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

of your data structures grew exponentially. We'll discuss a simulation technique to examine this issue in
the next section.
Simulated Stress Testing for Query Plans
When the T-SQL has been tuned to perfection, you should start looking at what happens to the query
plan as your data starts to scale out. The best way to do this is to generate data into the table, update
the statistics, and start running the query. This method gives you a chance to look at not only the effect
on the query plan, but you can also measure actual results. The downside is that many of your tables
contain RI that creates sub-loading tasks before you can get to the table you want to test. Not only does
this become another chore, but you also have to consider loading the data in a proper distribution pattern
to get an accurate stress test.
If you want to get a quick simulation of the effects more rows will have on your perfect query plan,
and you are in a testing environment, use the ROWCOUNT and PAGECOUNT options in the UPDATE
STATISTICS command to change the metadata for a table or index. Note that these are undocumented
options meant only for testing environments. Only use this technique when you can drop and add the
table after you test. The optimizer uses the metadata that these options alter to figure out that the table is
large instead of small or vice versa. This is beneficial to you for stress testing because it is difficult to get
the optimizer to waste time evaluating more complicated query plans if it determines that it only has a
few rows to return. By increasing the metadata row and page counts, the optimizer determines that there
is value in digging deeper into the optimization layers and builds plans as if the table had those actual
counts. Experiment with this by running the following script to build an Order and Detail table:
CREATE TABLE ORDER_HEADER(ORDER_HEADER_ID int, ORDER_NUMBER NVARCHAR(30))
CREATE TABLE ORDER_DETAIL
(ORDER_DETAIL_ID int, ORDER_HEADER_ID int, ORDER_LINE_AMOUNT MONEY)
GO
CREATE CLUSTERED INDEX [PK_ORDER_HEADER_ORDER_HEADER_ID]
ON [dbo].[ORDER_HEADER] ( [ORDER_HEADER_ID] ASC)
GO
CREATE CLUSTERED INDEX [PK_ORDER_DETAIL_ORDER_DETAIL_ID]
ON [dbo].[ORDER_DETAIL] ( [ORDER_DETAIL_ID] ASC )
Now run this query to get a benchmark plan using the current indexes. Note that although you did not
add any rows to either of these tables, you'll still be able to get costing estimates based on the default
optimizer costing models. The STATISTICS PROFILE option will allow you to review these plan estimates.
SET STATISTICS PROFILE ON
GO
--Summation Query
SELECT H.ORDER_HEADER_ID, SUM(ORDER_LINE_AMOUNT) AS TOTAL_ORDER_AMOUNT
FROM ORDER_HEADER H
INNER JOIN ORDER_DETAIL D
ON H.ORDER_HEADER_ID = D.ORDER_HEADER_ID
GROUP BY H.ORDER_HEADER_ID OPTION (RECOMPILE)
The plan estimates look good for this query. The subtree costs are low although you can see some
significant I/O estimates on the Sort step. The .01126126 estimate doesn't look too outrageous, so you
may feel like the indexing schemes are ready for prime time (see Figure 9-21).