Monday, January 14, 2008

Basics of Statistics in SQL Server 2005 - Load testing without any data

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

Basics of Statistics in SQL Server 2005

No comments: