Monthly Archives: January 2008

Update Statistics for all tables

Statistics in Sql Server are awesome for improving performance of queries.  There’s a lot of voodoo behind the scenes, but the end result is fast, and that makes users smile.

From time to time, table and index statistics get stale.  Updating statistics regularly is a good use of a sql agent job or a db maintenance plan step.  In the cases where I want to do it manually, I frequently have to search for a way to extract table names from sys tables, construct the update statistics command, and execute.

The ‘a-ha’ moment came.  The answer is in.  It need not be this difficult.  I’ve been a fool for too long.  http://technet.microsoft.com/en-us/library/ms173804.aspx details a wonderfully simple approach:

exec sp_updatestats

run it for the target db (or in turn for each db), and it updates statistics for all objects in the db.  Now why didn’t I think of that?

Rob