Posts
1150
Comments
891
Trackbacks
1
T-SQL: Basics of Statistics in SQL Server 2005

http://www.developer.com/db/article.php/3622881

Includes this cool trick:

The STATS_STREAM option, so far as I know, remains undocumented. But we now know what the other two options do, thanks to the new blogging spirit of openness and a blog posting from the SQL Server Query Optimization Team. It turns out that ROWCOUNT and PAGECOUNT can be used to trick the query optimizer by letting you fake the internal metadata about a particular table - specifically the count of rows and count of pages that the table occupies.

If you're going to experiment with this, please do not do so in a production database!

For example, you can vastly increase the apparent rowcount and pagecount of the Sales.SalesOrderDetail table by issuing this command:


UPDATE STATISTICS Sales.SalesOrderDetail 
WITH ROWCOUNT=5000000, PAGECOUNT = 5000000

Why would you want to do this? SQL Server's query optimizer does things differently on small tables and on large tables. During development, you likely don't have a whole lot of data in your tables. Rather than spend time generating huge amounts of sample data, you can use the undocumented options of UPDATE STATISTICS to see how the query optimizer will treat your tables after they've been in production for a while. This can be a quick and dirty method to uncover potential performance issues before they arise, and perhaps point out the need for additional indexes or other schema work. I'd recommend confirming any such changes by retesting with realistic amounts of sample data, though.”

posted on Thursday, March 01, 2007 12:49 PM Print
Comments
No comments posted yet.

Post Comment

Title *
Name *
Email
Url
Comment *  
Please add 3 and 5 and type the answer here: