locked
update statistics RRS feed

  • Question

  • hi,

    Im upgrading fro SQL 2008 R2 to SQL 2012, i ve quite  a few huge databases, i will be running dbcc checkdb and update statistics statements after upgrade is done. If i run update stats command with fullscan, i know its going to take long time, is there any script or any option in update stats to run update stats faster?

    Thursday, August 27, 2015 10:05 PM

Answers

All replies

  • What are you trying to achieve by updating stats. with out reorganizing indexes.

    As this is your migration from SQL 2008 to 2012 take time and schedule maintenance and rebuild indexes.

    Yes it will take time if your databases are big but it is worth doing.


    Thursday, August 27, 2015 10:10 PM
  • 1) change database compatibility to 110.

    2) rebuild and reorganize index depend on index fragmentation.

    3)update statics.

    DECLARE @SQL VARCHAR(1000)  
    DECLARE @DB sysname  

    DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR  
       SELECT 
    [name]  
       FROM master..sysdatabases 
       WHERE [name] NOT IN ('model''tempdb'
       
    ORDER BY [name] 
         
    OPEN curDB  
    FETCH NEXT FROM curDB INTO @DB  
    WHILE @@FETCH_STATUS 0  
       BEGIN  
           SELECT 
    @SQL 'USE [' @DB +']' CHAR(13) + 'EXEC sp_updatestats' CHAR(13)  
           
    PRINT @SQL  
           
    FETCH NEXT FROM curDB INTO @DB  
       
    END  
        
    CLOSE 
    curDB  
    DEALLOCATE 
    curDB

    you can change database name in script.


    Please click "Mark As Answer" if my post helped.

    Friday, August 28, 2015 1:46 AM
  • hi,

    Im upgrading fro SQL 2008 R2 to SQL 2012, i ve quite  a few huge databases, i will be running dbcc checkdb and update statistics statements after upgrade is done. If i run update stats command with fullscan, i know its going to take long time, is there any script or any option in update stats to run update stats faster?

    I would suggest you to try Ola Hallengren solution for index rebuild and update stats

    Ola solution for checkdb

    Its a widely accepted solution used worldwide


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Friday, August 28, 2015 11:20 AM