Statistics in SQL Server

SQL Server stores all sorts of statistics about how data is distributed within your database. Statistics are used by the query optimizer to choose the most efficient way to answer your questions.

Sometimes when you have a query that performs badly, the cause can be that your statistics are out of date or in some other way munged. Mike Gunderloy wrote an article on the subject.

Especially cool here is the discussion of an undocumented option that lets you increase the apparent number of rows and pages in your tables. Why might you wnat to do this? SQL Server's query analyzer works differently on small tables and large tables. Tricking SQL Server into thinking you have a huge table can allow you to see how SQL Server will handle a particular query.

It is interesting to see how SQL Server does things. For example, years ago, I had a query that had worked correctly for ages and it suddenly failed, returning data in an incorrect order. It turns out that my query had no explicit ORDER BY clause, and I was depending upon the way that SQL Server returns rows by default (the Primary Key was being used, even though I had not specifically requested it). When the table reached a certain size, it no longer returned rows in that order, and my application broke. This sort of failure is particularly annoying ("I did not change anything, and now my application is broken. Did anyone install any SQL Server updates?"). Of course, once I realized the problem, the solution was trivial, doing what I should have done in the first place!

Posted by Douglas Reilly at 10:18 AM  Permalink