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

Figure 9-21
Before you test this theory, take other benchmark snapshots of the perfmon counters and key metrics
from the core DMVs. Save this data for comparison later. Test your indexing structures and T-SQL code
by tricking SQL Server into thinking that your tables have now grown exponentially larger. How do
you do this? By running the undocumented options on the UPDATE STATISTICS command to set the
row and page counts to 100K. This command tricks the optimizer into thinking that the table is now
bigger than it is during optimization. Use these queries to increase the statistics for the order header and
detail tables:
update statistics ORDER_HEADER with rowcount = 100000, pagecount = 100000
update statistics ORDER_DETAIL with rowcount = 100000, pagecount = 100000
Now rerun the summation query again and compare the results; see Figure 9-22. The difference is in the
row count estimates and subtree costs. The optimizer is behaving as if we actually had a large number
of rows in these tables. Note that the Sort step is now supplemented with an additional Merge Join
Operation and is showing an increase in I/O costs when joining on the ORDER_HEADER_ID in the
ORDER_DETAIL ([D]) table.
Figure 9-22
The issue here is that there is no index on the ORDER_HEADER_ID in the ORDER_DETAIL table. If
you added the ORDER_HEADER_ID to the clustered index on the ORDER_DETAIL table, you could
eliminate this cost as well as the first Clustered Index Scan operator. Run this query to drop and add this
new index on ORDER_DETAIL:
DROP INDEX [PK_ORDER_DETAIL_ORDER_DETAIL_ID] ON [dbo].[ORDER_DETAIL]
GO