Updating Statistics

I read a post recently saying that updated statistics could prove more important to performance than optimizing indexes.  Cool stuff, and possibly much lighter on your server load.  If I can find the article that I read, I’ll post a link and/or an excerpt.

Here’s a couple of scripts using the undocumented sp_msforeachtable stored procedure, provided by MS since time began or shortly thereafter.

First, view the date that statistics were updated for every table:

sp_msforeachtable @command1="SELECT name, STATS_DATE(object_id, stats_id) as StatsDate FROM sys.stats 
WHERE object_id = OBJECT_ID('?')"

The results are interesting, you have one result pane for every table with one row for every index.  If your table name is part of the index name, identifying what is what shouldn’t be difficult.  But what was really interesting was the groups of indexes, including PKs, that the StatsDate field was null even after running an Update Statistics.  It took a minute for me to realize that those tables currently had no records.  *facepalm*

That ‘?’ functions as a variable substitution for the table name within the SP.  Its companion undocumented SP, sp_msforeachdatabase, has a similar wildcard.  One problem with sp_msforeachtable is that @command1 only accepts 128 characters, this is probably a carryover of the days that the maximum length of a char/varchar data type was 256 characters, with nchar/nvarchar taking half that.  Now in more modern SQL, char/varchar has a max length of 4,000 characters, so it’s not quite the problem that it used to be.  And you can find numerous rewrites of these stored procedures that do all sorts of spiffy things, probably also including bypassing this 128 character limit.

Next, update EVERY table’s statistics.  This is fine and pretty fast if your system is pretty small — if your system is pretty big, I hope you know what you’re doing before doing something like this!

sp_msforeachtable @command1="UPDATE STATISTICS ?"

Should you update statistics for every table every night, or multiple times every day?  The answer to that is left to the reader to research and decide what is appropriate to their installation(s).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s