Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 318

CREATE CLUSTERED INDEX [PK_ORDER_DETAIL_ORDER_HEADER_ID] ON [dbo]
.[ORDER_DETAIL] ([ORDER_DETAIL_ID] ASC, [ORDER_HEADER_ID] ASC)
GO
Now when you run the summation query again (see Figure 9-23), you'll see that the estimates go back to
a plan with low estimated row counts, I/O, and CPU costs, and the extra Merge Join Operator has been
eliminated.
Figure 9-23
Using the ROWCOUNT and PAGECOUNT options in the UPDATE STATISTICS command doesn't
compete with having real data with real statistics, but it does give you a quick way to evaluate how well
your SQL query is written and how well the index schemes will perform under increasing conditions.
Once you feel that your plan is well formed and the index schemes hold up well under stress testing you
can drop the table and re-script it to remove these false row and page counts.
Summary
This chapter has covered a lot of ground. You may not be at checkmate with the optimizer, but you
should be able to hold your own with the information that we've covered. You started with an in-depth
dive into the details of the optimizer and the operators that it uses to execute T-SQL statements. This
enabled you to read the execution plans and use this information to troubleshoot what may be going
wrong with the way the optimizer is handling your T-SQL requests.
In the second half of this chapter, you looked at what you could do tactically to performance-tune T-SQL
statements by examining the data provided to you in the execution plans and the profiler. You looked
at approaches of rewriting and reorganizing T-SQL to get the most performance gains. You developed a
troubleshooting protocol that looked at tuning T-SQL predicates and the effect of indexing on common
query tasks. For the problems that you'll see daily, you examined some common T-SQL pattern and pro-
gramming idioms to see best practices examples from a performance perspective. For the odd scenario,
you followed an example of how to use statement-level hints to improve a bad plan-caching issue. There
are no easy ways to succeed at chess; you have to simply play the game and learn from experience. The
same rules apply when performance tuning T-SQL statement in SQL Server 2005. The best decisions are
made with the most accurate data.