Professional SQL Server® 2005 Performance Tuning

9.8. Tuning the T-SQL Statement with Hints

Sometimes you might run into some issues that can't be resolved. Your data or situation just runs counter to how the optimizer approaches the solution, and you can only get the performance you need by tuning the T-SQL statement to get the plans that you've been able to simulate. This section will cover a common issue of plan caching, how to identify the issue, and how to resolve it.

9.8.1. Query Plan Caching Issues

Query plan caching is a wonderful thing. The idea is that a parameterized statement can be processed by SQL Server and only incur the costs of planning the best way to execute it once. This works wonderfully as long as the parameters represent evenly distributed data or you don't get too fancy and write multi-use, multi-parameter stored procedures. Performance issues occur when the first plan encountered by the optimizer isn't representative of the high-percentage usage of the procedure. The issue is that the low-percentage usage of the procedure is now cached and the high-percentage usage will have to use whatever plan was cached first. This is not optimal when the cached version is optimized for only a few rows of retrieval and the high-percentage usage is for a large row set. Rows will then be retrieved with nested loop operators that could more efficiently be retrieved with merge join operators. The opposite could also occur, but may be more difficult to detect.

To see an example of this in action, you need to produce ...