3

We have a SQL Server 2012 instance, with auto-statitics set to ON for the DB:

enter image description here

But then i ran a query to check some statistics, and some haven't been updated in a while:

enter image description here

  1. Why is this the case? Is there a rule to whether SQL Server updates statistics that haven't been triggered by these indexes?
  2. Do i need to care? How do i know if i need to update them, or if they are causing performance issues for me?

Thanks!

RPM1984
  • 72,246
  • 58
  • 225
  • 350
  • are you sure there were changes on the tables? are all statistics wrong, or just for some tables, if just some tables, what do they have in common? – RealCheeseLord Aug 16 '17 at 06:15

1 Answers1

4

Even though you set Auto update statistics to true, they will update only when a threshold has been reached..this is different for different versions

Thresholds for SQL Server 2012 or older:

  1. The table size has gone from 0 to > 0 rows

  2. The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the statistics object has changed by more than 500 since then

  3. The table had more than 500 rows when the statistics were gathered, and the colmodctr of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered

For SQLServer 2016,there are few major changes and SQL Updates statistics with a new algorithm(read as more frequently than old versions)

Do i need to care? How do i know if i need to update them, or if they are causing performance issues for me?

Normally people schedule maintenance jobs during weekends and this includes index rebuild/stats update..

This should normally take care of most databases.In your case,if you are seeing performance issues due to invalid stats,you can update them manually.We do it once a week,but sites like StackOverflow does it more often

update stats tablename

Further reading/references:
Statistics Used by the Query Optimizer in Microsoft SQL Server 2008
Understanding When Statistics Will Automatically Update

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • 1
    Thanks..this makes sense. Some of these tables aren't as active, so it makes sense. Two more questions: 1) "_Normally people schedule maintenance jobs during weekends and this includes index rebuild/stats update.._". I understand the need for manual index rebuild, but if AUTO STATS are on, i shoudn't need to manually do the stats updates, right? 2) "if you are seeing performance issues due to invalid stats". How do i know if my performance issues are related to invalid stats? I seem to remember something about "estimated vs actual rows". – RPM1984 Aug 16 '17 at 23:47
  • Update stats causes a lot of IO,so scheduling during off days is one reason I could think off.second one is if sql finds stats are not up to date for a query when it is executing,query execution is stopped for a brief moment of time until stats are rebuilt – TheGameiswar Aug 17 '17 at 03:29
  • Thanks. For clarity - your first comment implies to turn _off_ AUTO STATS and do it on the off days manually, right? – RPM1984 Aug 17 '17 at 06:51
  • i didn't meant that,you can keep it on,it helps in creating stats for non indexed columns as well – TheGameiswar Aug 17 '17 at 07:07