THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Playing with Metadata for Statistics - Part 2

When I got back to class this morning, I showed the class this blog, and the solution I had written to the question from yesterday. It was only then that I found out the need was for SQL Server 2000.

Where SQL 2005 has the catalog view sys.stats with a very queryable column called no_recompute, in SQL 2000, the information on whether autostats is enabled for a table or index is embedded as a bit in the status field of sysindexes. By examining the definition of the sp_autostats procedure, I was able to determine that if the 25th bit was set, it indicated that autostats was turned off for the particular index. I borrowed a few lines of code from sp_autostats and created the following view, which will report the automatic statistics setting and the last statistics update date on all tables and indexes in a database:

CREATE VIEW Stats_Info AS
  SELECT 
    
quotename(object_name(si.id),'[') AS Table_Name,
     quotename(si.name, '[') AS Index_Name,
     CASE (si.status & 16777216)
             WHEN 16777216
                 THEN 'OFF'
                 ELSE 'ON'
             END AS Auto_Stats,
     stats_date(id,si.indid) As Last_Update_Date
  FROM sysindexes si
  WHERE id > 100 AND
        si.indid BETWEEN 1 AND 254

If you just want to see the indexes that have autostats turned off, you can query the view:

  SELECT * FROM Stats_Info
  WHERE Auto_Stats = 'OFF'

Have fun!

~Kalen

Published Wednesday, November 07, 2007 7:58 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement