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

In class today, we were talking about automatic updating of statistics, and that you could use sp_autostats to turn off the automatic updating for just a single table, which is vastly preferable to turning it off for the entire database.

One of the students asked how she could determine which tables had the the automatic statistics feature turned off. She said she had an SAP database with tens of thousands of tables, and they had followed SAP's instructions to set no_recompute on some of the tables, but now she couldn't remember which ones.

So I was all set to have some fun analyzing the code in sp_autostats to see how it set the option on, and use that as a basis for writing a script that I was imagining would have to join at least 3 different metadata objects to get the necessary information.

But I was very disappointed to discover that everything I need is in sys.stats. A very simple query retrieved the table names and index names from this view, and a CASE expression turned the 0 or 1 in the no_recompute column into a YES or a NO, indicating whether the index had its statistics automatically updated or not.

To make the script a little fancier I put the original SELECT into a FROM clause to make it a derived table, and then selected just the indexes that didn't have automatic statistics updates enabled.

SELECT Table_Name, Index_Name
FROM
(
   SELECT object_name(object_id) as Table_Name,
           name as Index_Name, 
           CASE no_recompute
             WHEN 0 THEN 'Yes'
             ELSE 'No' 
           END as Auto_Update
   FROM sys.stats 
   WHERE object_id > 100
     AND object_name(object_id) not like 'queue%'
     AND auto_created = 0) AS autostats
WHERE Auto_Update = 'No'

 

If you want to see the list of all the indexes and whether or not statistics are auto updated, you can just run the SELECT inside the derived table definition:

SELECT object_name(object_id) as Table_Name,
           name as Index_Name, 
           CASE no_recompute
             WHEN 0 THEN 'Yes'
             ELSE 'No' 
           END as Auto_Update
  FROM sys.stats 
  WHERE object_id > 100
     AND object_name(object_id) not like 'queue%'
     AND auto_created = 0

Have fun...

~Kalen

Published Tuesday, November 06, 2007 7:38 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

 

Kalen Delaney said:

When I got back to class this morning, I showed the class this blog, and the solution I had written to

November 7, 2007 9:58 PM

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