SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Stuck on Update Stats issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 05/05/2014 :  10:17:45  Show Profile  Reply with Quote
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

USA
797 Posts

Posted - 05/05/2014 :  14:00:55  Show Profile  Reply with Quote
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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 05/05/2014 :  15:23:06  Show Profile  Reply with Quote
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...
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
797 Posts

Posted - 05/06/2014 :  14:06:58  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000