Stuck on Update Stats issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Stuck on Update Stats issue

Author

Topic

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2014-05-05 : 10:17:45

We just got this huge Database. (well I think it's big)
Now my weekend job doesn't finish.
Found out that it's stuck on updating stats on a large table. I found it's stuck on this type of statement:

SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [column1] AS [SC0] FROM [dbo].[table1] WITH (READUNCOMMITTED) ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL

But, there is no index on this column. My question is: why is SQL server checking every single column? i.e. I don't see why stats on columns that are unidexed could help the optimizer.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-05-05 : 14:00:55

SQL Server will create stats on any column that is used in a where clause. By default a database has that setting enabled - it can be disabled (but I would not recommend that unless you have specific guidance to disable it).

You can also manually create statistics on columns.

It looks like your weekend job is identifying all statistics and updating them with a full scan. That will require the statement to read all data from the table to generate the statistics. Whether or not that is what is needed...can't really say.

I use a script to update statistics, which will update any auto created statistics with the same default sampling rate - and index based statistics will be updated with a full scan.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2014-05-05 : 15:23:06

Yes that is correct, it is doing the whole server with Full.

We have low activity over the weekend so I had gotten away with that. I'm just thinking how I should change it...

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-05-06 : 14:06:58

What is happening is that auto created statistics - are then being updated with a full scan. The full scan has to read all of the data for the table and it is taking a very long time to complete.

I don't know how you have that coded, but if you are using the maintenance plan task you can set it up to do index statistics with full scan and column statistics with a sampling. If you start with this code:


 Select quotename(object_schema_name(ss.[object_id])) 
      + '.' + quotename(object_name(ss.[object_id])) As ObjectName
      , quotename(ss.name) As StatsName
      , ss.auto_created
      , sp.last_updated
      , sp.[rows]
      , sp.rows_sampled
   From sys.stats                       ss
  Inner Join sys.objects                so On so.[object_id] = ss.[object_id]
  Cross Apply sys.dm_db_stats_properties(ss.[object_id], ss.stats_id) sp
  Where so.[type] In ('U', 'IT')
    And sp.modification_counter > 0
    And substring(object_name(ss.[object_id]), 1, 1) <> '#'
    And sp.last_updated < cast(dateadd(day, -1, getdate()) As datetime2(7))
  Union --==== Include any stats that were auto updated with a sampling rate
 Select quotename(object_schema_name(ss.[object_id])) 
      + '.' + quotename(object_name(ss.[object_id])) As ObjectName
      , quotename(ss.name) As StatsName
      , ss.auto_created
      , sp.last_updated
      , sp.[rows]
      , sp.rows_sampled
   From sys.stats                       ss
  Inner Join sys.objects                so On so.[object_id] = ss.[object_id]
  Cross Apply sys.dm_db_stats_properties(ss.[object_id], ss.stats_id) sp
  Where so.[type] In ('U', 'IT')
    And ss.auto_created = 0 
    And sp.[rows] <> sp.rows_sampled
  Order By 
        last_updated
      , ObjectName
      , StatsName;

This will get you a list of statistics and the last time they were updated. It identifies auto created and any statistics that were auto updated with a sampling rate. Using this, you can then decide how to update each statistic.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources