1

We have several "reporting" tables in a SQL Server 2012 SP1 instance. Every hour, a job runs procedures that delete all the rows from these tables and then insert current data (involving lots of recursion and aggregations) into them. Each contains thousands of rows. Each such table is queried many times per day. The statistics on both the clustered and non-clustered indexes on these tables show outrageous things like 94,000% of rows updated, with the last statistics update being several days ago. Auto Update of statistics is set to true.

I would have thought that the statistics would get updated every time the table was queried for the first time after the contents were last replaced, but that apparently is not happening. Why?

  • 3
    Statistics for what object? Clustered index? Non-clustered index? Are you sure the query actually used the index you're checking? – Aaron Bertrand Mar 4 '15 at 21:37
  • are you using DBCC SHOW_STATISTICS(<tablename>,<statsname>) to look at the details, or are you using GUI? – Max Vernon Mar 5 '15 at 0:22
  • Max: I am using a query that joins dm_db_partition_stats, sys.objects, sys.stats, and sysindexes. DBCC SHOW_STATISTICS shows the same rowcount and updated date as my query. – Mark Freeman Mar 5 '15 at 15:21
  • Aaron: I edited my post to show that I'm referring to both clustered and nonclustered indexes. I am using a query that uses sys.dm_exec_cached_plans with a CROSS APPLY on sys.dm_exec_query_plan and query_plan.nodes to verify that the indexes are being used. I've even run some of the queries myself just to ensure they were run recently, but the stats don't update as a result. – Mark Freeman Mar 5 '15 at 15:24
  • Stats updates are based on the total number of changes. Your delete/insert may not be exceeding that threshold. – Robert L Davis Mar 12 '15 at 18:49
2

Unfortunately after searching SolarWinds' KB for about 10 minutes I cannot find what "Ineffective Statistics" measures. That information would be helpful. However, based on your question I'll infer that it means that the Statistics objects may be out of date or have taken quite a few modifications and Database Performance Analyzer has not detected an update of some sort. Without more information from the product vendor, it's hard to tell.

Having said that, there are a few things that you should know.

First, you can verify what's going on with your server right now (e.g. when you get the alert) and compare it against your monitoring tool. Here's a script that I use:

use [mydatabasename];
go
select 
    [schema_name] = sh.name, 
    [table_name] = t.name, 
    [stat_name] = s.name,
    [column_names] = stuff((
                              select ','+index_col(t.name,sc.stats_id,sc.stats_column_id)
                              from sys.stats_columns sc
                              where sc.object_id = s.object_id
                                and sc.stats_id = s.stats_id    
                               order by sc.stats_column_id        
                              for xml path('')), 1, 1, '' 
                            ) ,
    s.stats_id,     
    sp.last_updated, 
    sp.rows, 
    sp.rows_sampled, 
    sp.unfiltered_rows, 
    [sample_rate] = (100.*sp.rows_sampled)/sp.unfiltered_rows,
    modification_counter,
    [default_mod_threshold] = case when sp.unfiltered_rows < 500
                                                   then 500
                                                   else ( .20
                                                          * sp.unfiltered_rows
                                                          + 500 )
                                              end ,
    [2371_mod_threshold] = sqrt(1000 * sp.unfiltered_rows),
    [default_mod_hit] = case when sp.modification_counter > case
                                                              when sp.unfiltered_rows < 500
                                                              then 500
                                                              else ( .20
                                                              * sp.unfiltered_rows
                                                              + 500 )
                                                              end then 1
                                           else 0
                                      end ,
    [2371_mod_hit] = case when sp.modification_counter > sqrt(1000
                                                              * sp.unfiltered_rows)
                                          then 1
                    end

from sys.stats s 
join sys.tables t 
    on s.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
cross apply sys.dm_db_stats_properties(s.object_id,s.stats_id) sp
order by t.name, s.name;

Most of it is self-explanatory. However, I added the modification threshold for a statistics object at default and with TF 2371 included. The default threshold is an interesting calculation, while TF 2371 is the square root of 1000 * Table Cardinality. I included a calculation to include whether or not each of these thresholds had been hit yet. So run that inside the alerted database and see if what SolarWinds is seeing comports with what is occurring on the server right now. If it isn't then there's probably a timing issue in there somewhere such that the alert was generated before a stats update but after a massive amount of data change.

Having said that, it's important to know how AUTO_UPDATE of statistics actually works. There isn't a clock-like sweep that goes through and constantly updates statistics. Instead, the statistics are updated when the statistic object is loaded to compile a query plan. If the statistic is rarely/never used then it won't be updated unless you manually update it or if it happens to get caught up in an index rebuild as a dependent object for the index. However, if you have an index that's never getting used then that's a different situation altogether.

So in summary:

  1. Verify what the alert is telling you
  2. Know the factors that lead to a stats update

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.