17.5. Best practice considerations: performance tuning

Performance tuning is a specialist skill. In this chapter, we've briefly covered a number of common problems in addition to looking at the waits and queues methodology for a targeted tuning approach. I encourage you to visit http://www.sqlCrunch.com/performance for links that provide broader and deeper coverage on this most important area.

• A good performance-tuning strategy considers input from multiple sources before drawing any conclusions. In this chapter, we've consider input from waits, queues, and DMVs in assisting in the diagnosis of common performance problems.

• The waits and queues methodology permits a targeted approach to performance tuning by narrowing the target to the biggest pain points identified using the sys.dm_os_wait_stats DMV.

  Figure 17.13. Performance-tuning information sources by resource category
   

  

• A good baseline and regular baseline analysis should form part of an overall performance-tuning exercise. The values for many of the counters discussed throughout this chapter are meaningless unless seen in the context of a known value recorded during times of acceptable performance. Knowing these values is key in detecting emerging trends that can be arrested before the problem becomes widespread.

• Wherever possible, use stored procedures (or parameterized sp_executesql) instead of dynamically executed SQL using exec. Parameterizing SQL avoids the common procedure bloat issue whereby valuable memory (and CPU resources) is wasted on single-use ad hoc SQL; further, dynamic SQL opens up the possibilities of SQL injection attacks that we covered in chapter 6.

• In situations where direct control over application code is not possible, consider using the Forced Parameterization or the Optimize for Ad Hoc Workload option. As with all other configuration recommendations throughout this book, such changes should be made after observation in a testing environment with an appropriate workload simulation.

• In cases where a small number of queries are causing compilation issues, and the queries themselves cannot be changed, such as in a vendor-supplied application, consider using plan guides (not covered in this book) in place of Forced Parameterization.

• The excellent Microsoft whitepaper titled Batch Compilation, Recompilation and Plan Caching Issues is a must read in understanding the (many) issues for consideration in increasing plan usage. One such recommendation is ensuring objects are fully qualified, for example, select * from dbo.table rather than select * from table.

• For stored procedures that take a wide variety of parameter values, consider creating the procedure with the WITH RECOMPILE option to avoid parameter-sniffing issues whereby ongoing performance is dictated by the parameters used in the first execution. While ongoing compilation will be higher, the resultant plans are typically more accurate. In cases where the additional compilation overhead is accepted in return for improved (and consistent) performance, such an option is certainly worth considering.

Additional links and information on the best practices covered in this chapter can be found online at http://www.sqlcrunch.com/performance.