Rather then update index statistics for all indexes in a database I want to create a script that only updates those indexes where the statistics are no longer accurate.  The date shows when a statistic was last updated, but that doesn't necessarily mean the statistics are not accurate.  How do I determine when statistics should be updated?

Thanks,   Dave

 

I have no script for that. I use to update statistics tree days a week for heavy tables only, and a full update statistics on a week end. Also I run one script (again on weekends) to defrag only those indexes with high fragmentation. From time to time (not a job) I run one script to find out in any statistics are NULL. In this case I deleted those statistics.

Thanks.  We are asking the vendor to explain why we cannot have AUTO_UPDATE_STATISTICS and AUTO_CREATE_STATISTICS enabled.  Their technical documentation appears to be outdated and may not be taking into consideration the improvementd made in 2005 with index statistics.

But I believe AUTO_UPDATE_STATISTICS and AUTO_CREATE_STATISTICS enabling comes with some extra overhead. Sometime if statistics are outdated,Database Engine will automatically update any statistics that are manually created. This may happen in between query processing. Hence, it is advisable to update the statistics manually during maintenance windows rather than using AUTO function.

This is my suggestion. Moderator can add their views.

 

If you're using SQL 2005, you can Auto Update Statistics Asynchronously, which will reduce some of the overhead.

IMHO, unless we see the statistics and analyze them manually we may not be able to do it through script.. But the options that could be tried are

* Select stats_date() for each table and statstics if it returns null then stats for that are never updated.

* if Rowmodctr > 0 then stats may not be updated. Every time stats kick in it changes rowmodctr = 0. So, in script if we can see difference between rowcnt - rowmodctr then that delta of rows are added after last statistics update.

But in SQL Server 2005 as far as I understand it is no more row modifications that are counted but column modifications so not sure if this logic works there or not.

As regards to disabling auto stats I dont think that it may be a good option(as far as my experience goes).

In SQL2k5 we have all the more reasons to turn it on

* Statement level remcompilation vs Batch Level compilation in SQL server 2000

* Asynchronous stats updates vs Synchronous stats update. 

But there should be a script that update stats at regular intervals Reindexing does not update statistics create due to auto create statistics. Also update statistics are only kicked in due to some thresholds like % of rows modified which in large tables would be very infrequent(if there is not job to reindex). So stats should be updated at regular intervals.

review: http://support.microsoft.com/kb/195565  

for the basic algorithm for updating statistics

Hi Dave,

This is a huge subject but to summarize: you need to either have Autostats enabled (possibly with the Asynch option mentioned previously) for your tables or "manually" the statitics. I grabbed the below from an entry I made in another forum. The code you are asking for is at the end but browse throught the rest because if you don't maintain your statistics well, things can go horribly wrong :P

There are three possible issues you can run into with statistics

1. The statistics is out dated

This could be because the sysindexes.rowmodctr compared to sysindexes.rows is not big enough (http://support.microsoft.com/kb/195565/EN-US/ contains a chart) or the table has not been touch by a query lately. This can become an issue with large tables ("too many" updates are required to trigger Autostatistics).

2. High change frequency that makes Autostatistics and accompanying recompiles occur at disturbingly high rates

Not an issue for you since you have turned it off.

3. The sample rate used to build the statistics is not high enough

       b. the underlying data is of "skewed" nature (such as LastName where you can expect some "buckets" to have many entries, such as "Smith", and some very few, such as "Doggy Dog"). See Bart Duncans blog for a crystal clear example: http://blogs.msdn.com/bartd/archive/2006/07/25/limited-statistics-granularity.aspx

1. Outdated Statistics
==================
Add a job that on a regular basis updates your statistics. If you do index rebuilds remember that this will in fact rebuild your statistics (so you wouldn't do them during the same night or whatever interval you are using).  INDEX defrag on the other hand does not rebuild statistics.

2. Autostatistics and Recompile
============================
Not an issue for you but if it becomes one :) : http://support.microsoft.com/kb/243586 "Troubleshooting stored procedure recompilation".

3. Sample Rate Issues
==================
What you need to do is to:

A. Find a sample rate that is sufficiently high to provide you with good statstistics (this might be as low as 10% for ~5 million rows tables) but at the same time it should be low enough to be able to finish within your maintenance window (stating the obvious; it shouldn't obstruct other database activity either)

Maybe you end up running with 60% sample rate nightly on some tables but with 10% sample rate 3 times a day for other tables.

B. Turn OFF Autostatistics for the tables when you have set up your scheduled UPDATE STATISTICS .... WITH  SAMPLE XX PERCENT


To find your large tables and their row modifications you can do something like this:

        SELECT         os.name AS TableName,
                si.rowmodctr AS RowsModified,
                si.rowcnt AS RowCount
        FROM         SYSOBJECTS o JOIN SYSINDEXES si ON o.id = si.id
        WHERE         si.rowmodctr > -- some number
                and si.rowcnt > -- some number
                and so.xtype = 'U'
        order by RowCount

(Mind you also non-indexed columns can have statistics, Autocreate statistics feature. You will find them by looking at entries in sysindexes.name that begins with _WA.)

.. and to get a look at the sample rate that was used on the current statistics (ping me if you want the entire sproc that loops through everything):

DBCC SHOW_STATISTICS (@tablename, @index) WITH STAT_HEADER


Lubor Kollar has written an MSDN article on the subject: "Statistics Used by the Query Optimizer in Microsoft SQL Server 2000", http://msdn.microsoft.com/en-us/library/aa902688(SQL.80).aspx and there is a shorter version at http://support.microsoft.com/kb/195565/EN-US/ "Statistical maintenance functionality (autostats) in SQL Server".


HTH!

/Elisabeth

You can run sp_updatestats against the database, it will update the statistics on those tables which were modified recently.

 

Thanks,

Reddy