Professional SQL Server® 2005 Performance Tuning

9.10. 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 ...