none
SQL 2012 Update Statistics Maintenance Plan

    Question

  •  Hi All

    Is anyone aware of any issues on SQL 2012 regarding maintenance plans for update statistics.

    I have migrated a system to SQL 2012 from SQL 2008, I created a maintenance plan to update all stats with a sampling rate of 15%. The job ran for over 3 days.

    On SQL 2008, the job runs for 2 hours.

    Nothing has changed regarding the workload on the system and the absolute only change was the upgrade to SQL 2012.

    Obviously there is something very wrong with the release.

    Any ideas?

    Thanks

    Tuesday, August 20, 2013 7:14 AM

All replies

  • Hi

    I've seen this before...  it seems centered around system views.  Does your job update stats for both views and tables?  If so then remove the views from the maintenance plan (leaving it as "Tables Only") and see if it runs any faster.  In my environment we got a maintenance plan down from 30 mins to 5 mins just by removing the Views option.

    Thanks,

    Kevin

    Tuesday, August 20, 2013 7:34 AM
  • It's just tables at 15% sampling. I have a separate MP for Views
    Tuesday, August 20, 2013 8:22 AM
  • Just to be absolutely certain, I would compare number of rows in sys.states for both servers. Also, just FYI I blieve that update statistics on EE and run in parallell if you do fullscan, but uses only 1 CPU if sample data (doesn't explain difference, but thought I'd mention...

    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, August 20, 2013 4:52 PM
    Moderator
  • There is definitely something wrong with the stats maintenance plans in SQL 2012.

    Even if there was database growth, to go from 2 hours to 3+days is not normal.

    Wednesday, August 21, 2013 6:58 AM
  • Hi ,

    Since the job has run so much time,  we suggest you to check the maintenance plan history log,have a general scan on start time ,end time and which steps cost so much time. Please refer below article named "How to troubleshoot problems that occur when you use a Database Maintenance plan in SQL Server".

    We also suggest you updating statistics manually and check the execute time to see whether the time was also so long. Please refer below codes:

    SET STATISTICS IO ON
    EXEC sp_updatestats; 
    SET STATISTICS TIME ON

    If you have any concerns, please let me know.
    How to troubleshoot problems that occur when you use a Database Maintenance plan in SQL Server
    http://support.microsoft.com/kb/288577
    Thanks,
    Candy Zhou

    Wednesday, August 21, 2013 9:33 AM
  • Hi ,

    Since the job has run so much time,  we suggest you to check the maintenance plan history log,have a general scan on start time ,end time and which steps cost so much time. Please refer below article named "How to troubleshoot problems that occur when you use a Database Maintenance plan in SQL Server".

    We also suggest you updating statistics manually and check the execute time to see whether the time was also so long. Please refer below codes:

    SET STATISTICS IO ON
    EXEC sp_updatestats; 
    SET STATISTICS TIME ON

    If you have any concerns, please let me know.
    How to troubleshoot problems that occur when you use a Database Maintenance plan in SQL Server
    http://support.microsoft.com/kb/288577
    Thanks,
    Candy Zhou

    I've run that code and it completes in under 2 hours.

    I've been advised that I should check wait statistics, CPU usage, Server load etc etc. All of this is not what the problem is.

    I've created a custom cursor which loops through all the tables in the database and updates the statistics, this also completes in under 2 hours.

    Why is it that when I use a maintenance plan, it runs for over 3 days. Something is clearly wrong with the maintenance plan itself.

    This is what I noticed:

    When you create and execute a maintenance plan, before it actually starts doing the job, it runs a metadata query which usually runs in a few seconds, at that point it starts with the actual job (updating stats).

    The problem is that the metadata query never stops and hence, never gets to the part of updating stats.

    Wednesday, August 21, 2013 11:34 AM
  • SQL24,

    If possible can you share the script-out of this task by clicking "View T-SQL" and log file for this maintenance plan.

    Also, as a test just create a new maintenence plan with update statistics task with just 1 table and help with your observation.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.



    • Edited by anuragsh Wednesday, August 21, 2013 12:05 PM
    Wednesday, August 21, 2013 12:00 PM
  • Unfortunately, I can't script it out - There are too many names to obfuscate.

    I can tell you that the entire script is this:

    use [Database_Name]
    GO
    UPDATE STATISTICS [schema].[table_name]
    WITH SAMPLE 15 PERCENT
    GO

    This is repeated for every table, obviously

    Wednesday, August 21, 2013 12:06 PM
  • As asked above, please share the log file for this maintenance plan and you observation on a test of just create a new maintenence plan with update statistics task with just 1 table and help with your observation.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.

    Wednesday, August 21, 2013 12:08 PM
  • No log was generated for the maintenance plan because I cancelled it after 3 days

    I created a maintenance plan for just 1 table, that ran instantly and with no issues.

    Wednesday, August 21, 2013 1:10 PM
  • There seems to be bug.Try updating the sql server 2012 version tp sp1 cu 4 wherein this being addressed.

    1254696 2829845 FIX: "The provided statistics stream is corrupt" when you run the "UPDATE STATISTICS" statement against a table in SQL Server 2012

    Refer below link:

    http://support.microsoft.com/kb/2833645/en-us

    Hope it may help!!


    Regards, Vishal Srivastava

    Wednesday, August 21, 2013 1:25 PM
  • There seems to be bug.Try updating the sql server 2012 version tp sp1 cu 4 wherein this being addressed.

    1254696 2829845 FIX: "The provided statistics stream is corrupt" when you run the "UPDATE STATISTICS" statement against a table in SQL Server 2012

    Refer below link:

    http://support.microsoft.com/kb/2833645/en-us

    Hope it may help!!


    Regards, Vishal Srivastava


    I am running Sp1 CU4 (3368)
    Wednesday, August 21, 2013 1:41 PM
  • Hi,
    First, please try to install the “Cumulative update package 7 for SQL Server 2012” and check whether the issue is fixed.

    Second, let’s create a job with the update statistics codes, and check whether the job can finish updating statistics quicker, if so, we can schedule the job to update statistics.Tips: About how to get update statistics codes, please open the subplan of the maintenance plan and click “View T-SQL” and paste the codes into the custom job step part.

    Please refer below KB to download the source.

    Cumulative update package 7 for SQL Server 2012
    http://support.microsoft.com/kb/2823247

    Thanks,
    Candy Zhou

    Thursday, August 22, 2013 8:13 AM
  • ...

    When you create and execute a maintenance plan, before it actually starts doing the job, it runs a metadata query which usually runs in a few seconds, at that point it starts with the actual job (updating stats).

    The problem is that the metadata query never stops and hence, never gets to the part of updating stats.

    Did you check for blocking occuring?

    I have seen that at systems.

    You can do this even via Activity Monitor..


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Saturday, August 24, 2013 7:15 PM
  • ...

    When you create and execute a maintenance plan, before it actually starts doing the job, it runs a metadata query which usually runs in a few seconds, at that point it starts with the actual job (updating stats).

    The problem is that the metadata query never stops and hence, never gets to the part of updating stats.

    Did you check for blocking occuring?

    I have seen that at systems.

    You can do this even via Activity Monitor..


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com


    No blocking and no waits at all
    Monday, August 26, 2013 6:47 AM
  • ...

    The problem is that the metadata query never stops and hence, never gets to the part of updating stats.

    Did you check for blocking occuring?

    I have seen that at systems.

    You can do this even via Activity Monitor..


    ..


    No blocking and no waits at all

    Strange

    But if you are there: can you see the command, that the session is currently running?

    And if you check again later 2 times, (5-10 minutes apart), does it run a different command by then and which?


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Monday, August 26, 2013 11:12 AM
  • ...

    The problem is that the metadata query never stops and hence, never gets to the part of updating stats.

    Did you check for blocking occuring?

    I have seen that at systems.

    You can do this even via Activity Monitor..


    ..


    No blocking and no waits at all

    Strange

    But if you are there: can you see the command, that the session is currently running?

    And if you check again later 2 times, (5-10 minutes apart), does it run a different command by then and which?


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Very strange, what I've been saying all along. Something is definitely wrong with stats maintenance plans
    Same command - it's a command that queries the metadata.
    Monday, August 26, 2013 11:59 AM
  • ...

    Very strange, what I've been saying all along. Something is definitely wrong with stats maintenance plans
    Same command - it's a command that queries the metadata.

    Honestly I'd take a different Approach then using main- plans..

    Can you share the command?


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Monday, August 26, 2013 12:07 PM
  • ...

    Very strange, what I've been saying all along. Something is definitely wrong with stats maintenance plans
    Same command - it's a command that queries the metadata.

    Honestly I'd take a different Approach then using main- plans..

    Can you share the command?


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com


    I have already taken a different approach. Maintenance plans are proving to be too unpredictable.
    Monday, August 26, 2013 12:34 PM