Professional Microsoft® SQL Server® 2008 Administration

14.3. Summary

In this chapter you learned how query parsing, compiling, and optimization are done in SQL Server 2005. You also learned how to read the query plan. Keep in mind that when you read the query plan (using STATISTICS PROFILE, for example) the most important columns you want to look at are Rows, Executes, and EstimatedRows. If you see a big discrepancy between Rows (the actual row count) and EstimatedRows, remove that small query from your main query and start your analysis there. Note that we are not suggesting that every performance problem with a query stems from bad statistics or cardinality estimations. In the real world, where users are less experienced, most performance problems result from user errors (lack of indexes and such). Check the statistics on the columns in the JOIN and WHERE clauses.

You also learned about the different index access methods and join algorithms. Normally, I/O is the slowest process, so your goal in query tuning is to reduce the number of I/Os and balance the data modification operation (in an OLTP system), and for that knowledge of index access methods and join algorithms is vital. Tuning is not easy, but with patience and attention to details, we are sure that you will get to the root of the problem. Of course, make sure that your server and disk configuration are done properly. You have also learned in this chapter about new features on SQL Server 2008, such as the MERGE and the query-processing enhancements on partitioned tables ...