Post reply

Update Statistics slow through Maintenance Plan whereas through T-SQL it completes pretty fast.

  • January 22, 2018 at 12:27 am

    #409978

    We have a Sharepoint instance hosting around 2.5 TB of Databases on it. When i try to perform the Update Statistics procedure through a Maintenance Plan, it executes for around 5-7 hrs time whereas when i try to perform the same using the below T-SQL it completes within 75 minutes. SQL Version is 2014 Enterprise Edition. I know that sp_msforeachtable is a undocumented stored procedure; i will try to modify the same but i would like to understand this strange behaviour.

    DECLARE @sql VARCHAR(1000)  
    DECLARE @DB sysname  

    DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR  
      SELECT  s.[name] AS dbName 
    FROM    master.sys.databases s            
        WHERE   s.state_desc = 'ONLINE'
    and s.[name] not in ('tempdb')
                AND s.is_read_only != 1  
    ORDER BY [name] 
         
    OPEN curDB  
    FETCH NEXT FROM curDB INTO @DB  
    WHILE @@FETCH_STATUS = 0  
       BEGIN  
           SELECT @sql = 'USE [' + @DB +']' + CHAR(13) + 'EXEC sp_MSForEachTable' + SPACE(1)+ ''''+SPACE(1)+'UPDATE STATISTICS ? WITH FULLSCAN'+SPACE(1)+''''+CHAR(13)  
           PRINT(@SQL)
           EXEC(@SQL)
           FETCH NEXT FROM curDB INTO @DB  
       END  
        
    CLOSE curDB  
    DEALLOCATE curDB

  • January 22, 2018 at 5:03 am

    #1976700

    Without seeing the wait statistics on the server, blocked process reports, etc., it's hard to say for certain why one is behaving differently than the other. It's pretty likely that the maintenance plan is running sp_updatestats, which is going to call things differently than the code you wrote. That could also explain the differences. If you really must nail down exactly why, I'd suggest capturing the queries, wait statistics and os behavior during each and comparing them.

    You might want to look to Ola Hollengren's scripts instead of writing your own. No need to reinvent the wheel.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 23, 2018 at 11:22 am

    #1976962

    Sequential instead of parallel?

  • January 23, 2018 at 11:58 am

    #1976972

    I'd probably start with an Extended Events session to capture exactly what statements are run by each, and get their execution characteristics.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply