Improving query performance with OPTION (RECOMPILE), Constant Folding and avoiding Parameter Sniffing issues

OPTION (RECOMPILE) when used in the correct manner can give a performance boost though Constant Folding and also help deal with parameter sniffing issues. However, before going into OPTION (RECOMPILE) and how Constant Folding works first a bit of background. Plan Caching Issues The first time a stored procedure is run it will compile into…

4

Index Maintenance – Fragmentation should not be your only concern

Index fragmentation is not the only consideration when constructing an index maintenance plan. This article will look at other considerations that should be taken into account such as: Page fullness FILLFACTOR and PAD_INDEX The impact of Snapshot Isolation The less fragmented an index is, the more performant it will be. Inserting records inside an index…

2