none
SQL Server no longer updating statistics

    Pergunta

  • Running SQL Server 2012 RTM, but problem seems to have been carried forward from when the database was on SQL 2008 R2.

    I noticed a lot of slowdowns in the database this morning and upon investigation found that statistics were several months old on some tables (but some were up to date).  I re-ran stats with a full scan to catch them up and then opted to check for fragmentation.  DBCC SHOWCONTIG comes back immediately on some tables with very disturbing output:

    DBCC SHOWCONTIG scanning 'factMetricInts' table...
    Table: 'factMetricInts' (421576540); index ID: 1, database ID: 6
    TABLE level scan performed.
    - Pages Scanned................................: 0
    - Extents Scanned..............................: 0
    - Extent Switches..............................: 0
    - Avg. Pages per Extent........................: 0.0
    - Scan Density [Best Count:Actual Count].......: 100.00% [0:0]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 0.00%
    - Avg. Bytes Free per Page.....................: 0.0
    - Avg. Page Density (full).....................: 0.00%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    I even rebuilt the indexes on this table and then re-ran showcontig and I get the same output.

    This table is partitioned and page compressed.

    Has anyone seen this behavior before?

    sexta-feira, 30 de março de 2012 17:54

Respostas

  • As someone already mentioned, use sys.dm_db_index_physical_stats to see the index fragmentation.

    DBCC SHOW_STATISTICS for last stats update date.

    To answer your original question, stats are automatically updated (provided you have the corresponding db options set) when it hits a threshold of 20 % modifications for a given column...For tables having large rows this threshold may be too high hence the issue. As such, either you should manually update the stats with desired sampling else use TF 2371 (beware before you use this on prod) to override the 20% behavior..


    Prashant [MSFT] -- This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marcado como Resposta JustinRush segunda-feira, 2 de abril de 2012 11:58
    sexta-feira, 30 de março de 2012 21:55

Todas as Respostas

  • Hello

    Please run this script to confirm whether the stats are been updated or not.

    http://sql-javier-villegas.blogspot.com.ar/2012/01/check-statistics.html

    I think DBCC SHOWCONTIG has been deprecated


    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    sexta-feira, 30 de março de 2012 18:37
  • DBCC SHOWCONTIG displays correctly for some tables, so it must still work.

    I'm not sure what you are getting at with your script.  I verified that stats were not getting updated on some of the indexes and then I manually ran stats for them.

    sexta-feira, 30 de março de 2012 18:43
  • The script reports  the last time the stats were updates per table/index

    Have you checked "Auto Update Statistics" settings for your database?

    Regarding to DBCC SHOWCONTIG, looks like it is still working 


    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    sexta-feira, 30 de março de 2012 18:56
  • RE: showcontig, Ack, ok, that sucks.

    Auto update stats and Auto Update stats Async are both checked... some of the tables appear to be updating properly, just not all of them.

    The script shows everything updated in the last few hours because I ran stats manually.


    sexta-feira, 30 de março de 2012 19:08
  • What command are you using to update stats? The stats on tables are not up-to-date do they have any indexes on them?

    http://uk.linkedin.com/in/ramjaddu

    sexta-feira, 30 de março de 2012 19:50
  • What command are you using to update stats? The stats on tables are not up-to-date do they have any indexes on them?

    http://uk.linkedin.com/in/ramjaddu

    usually when upgrading a database from an older version to a newer version, it is recommand to execute

    DBCC UPDATEUSAGE (6)

    (6 is the database id of your database as you mentioned in your first posting)

    http://msdn.microsoft.com/en-us/library/ms188414(v=sql.110).aspx

    to avoid problems with updating row counts, stats etc.

    we've noticed this issue with several databases and executing DBCC UPDATEUSAGE fixed it.


    sexta-feira, 30 de março de 2012 20:09
  • Hi Justin,

    As others have already mentioned it , the correct way to look at fragmentation starting from SQL Server 2005 is physical index stats DMV. Most likely reason behind you are not seeing the correct data from showcontig is due to the table being partitioned or type of data type involved.There are number of restirctions why DBCC SHOWCONTIG might not give you the correct results which are documented here http://msdn.microsoft.com/en-us/library/ms175008.aspx under the restriction 

    DBCC SHOWCONTIG does not display data with ntext, text, and image data types. This is because text indexes that store text and image data no longer exist.
    Also, DBCC SHOWCONTIG does not support some new features. For example:
    If the specified table or index is partitioned, DBCC SHOWCONTIG only displays the first partition of the specified table or index.
    DBCC SHOWCONTIG does not display row-overflow storage information and other new off-row data types, such as nvarchar(max), varchar(max), varbinary(max), and xml.
    Spatial indexes are not supported by DBCC SHOWCONTIG.
    All new features are fully supported by the sys.dm_db_index_physical_stats (Transact-SQL) dynamic management view.


    Thanks, Leks

    sexta-feira, 30 de março de 2012 21:40
  • As someone already mentioned, use sys.dm_db_index_physical_stats to see the index fragmentation.

    DBCC SHOW_STATISTICS for last stats update date.

    To answer your original question, stats are automatically updated (provided you have the corresponding db options set) when it hits a threshold of 20 % modifications for a given column...For tables having large rows this threshold may be too high hence the issue. As such, either you should manually update the stats with desired sampling else use TF 2371 (beware before you use this on prod) to override the 20% behavior..


    Prashant [MSFT] -- This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marcado como Resposta JustinRush segunda-feira, 2 de abril de 2012 11:58
    sexta-feira, 30 de março de 2012 21:55
  • I will give update usage a shot and see what that does.

    20% is the threshold to update stats?  That seems excessively high.  These fact tables have 200,000,000 rows in them and I pretty much straight append into them and report off the newest partitions.  So if I add 1%, that's 2,000,000 rows.  If stats aren't updated then the optimizer thinks any queries issued against that new 1% of data will return no rows, and opts to not use parallelism.

    I just updated stats manually again.. I'll report back after a couple of days with update usage.

    domingo, 1 de abril de 2012 11:52
  • 20% is the threshold to update stats?  That seems excessively high.  

    ..But yeah :(

    http://connect.microsoft.com/SQLServer/feedback/details/714770/need-sp-configure-parameter-and-database-properties-setting-to-control-the-threshold-to-trigger-auto-update-stats


    Prashant [MSFT] -- This posting is provided "AS IS" with no warranties, and confers no rights.

    domingo, 1 de abril de 2012 19:24
  • That ticket seems to be hinting that the sp_configure parameter is added in some version of 2008 r2 and that there is a table called sys.dm_db_stats_properties.  I don't see this table in 2012 -- is this something that is still under development?

    I already see almost a million unsampled rows in one of the tables so I will likely end up going with the traceflag option in the next downtime window.

    segunda-feira, 2 de abril de 2012 11:58
  • That ticket seems to be hinting that the sp_configure parameter is added in some version of 2008 r2 and that there is a table called sys.dm_db_stats_properties.  I don't see this table in 2012 -- is this something that is still under development?

    I already see almost a million unsampled rows in one of the tables so I will likely end up going with the traceflag option in the next downtime window.

    Instead of a sp_configure parameter to control updating stats, MS provides a dmv sys.dm_db_stats_properties which may be helpful to decide your own strategy for updating stats. Looks like This dmv is included in SQL 2008 R2 SP2 which is yet to be released. As you say, SQL 2012 RTM doesnt have it, then my guess is that likely it would be included in a CU/SP of SQL 2012. 


    Prashant [MSFT] -- This posting is provided "AS IS" with no warranties, and confers no rights.

    terça-feira, 3 de abril de 2012 11:31