{"id":65,"date":"2010-10-11T17:10:41","date_gmt":"2010-10-12T00:10:41","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=65"},"modified":"2013-01-30T12:02:29","modified_gmt":"2013-01-30T19:02:29","slug":"fastest-way-to-update-stats-on-sql-server-with-fullscan","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2010\/10\/fastest-way-to-update-stats-on-sql-server-with-fullscan\/","title":{"rendered":"SQL Server Update Stats – The Fastest Way (with FULLSCAN)"},"content":{"rendered":"

The script below has three distinct advantages over the traditional method of just running sp_updatestats against the entire database:<\/p>\n

    \n
  1. It updates the stats starting with the smallest table first and finishing with the largest table. This provides the most bang for your buck when time is of the essence.<\/li>\n
  2. The messages tab is constantly being updated with the progress of the script (including a counter showing how many tables remain). This can be helpful when trying to determine if the script is close to done and should be allowed to keep running or if it should be cancelled to free up resources.<\/li>\n
  3. Provides the option to update statistics WITH FULLSCAN instead of just based on a sample of the data.<\/li>\n<\/ol>\n

    When you are faced with a serious performance issue one of the lowest risk solutions is to update statistics on your tables (even if you've set your DB to automatically update stats).\u00a0 Unfortunately, for various reasons statistics on your tables can be come inaccurate to the point that a simple \"UPDATE STATISTICS\" does not resolve the issue.\u00a0 When faced with an urgent performance issue updating statistics with the \"WITH FULLSCAN\" option can be exactly what SQL Server needs to find the optimal execution plan.\u00a0 The bad news is the system stored procedure sp_updatestats does not provide the ability to update your statistics with the FULLSCAN option. Execute the query below to update statistics with full scan on all tables in your database.\u00a0\u00a0 Check the SSMS messages window to see the progress of the proc while it is running. It will display realtime messages similar to the following:<\/p>\n

    Table 1 of 84: Running 'UPDATE STATISTICS [dbo].[ErrorLog]' (0 rows)\r\nTable 2 of 84: Running 'UPDATE STATISTICS [dbo].[kproduct_xml_doc]' (0 rows)\r\nTable 3 of 84: Running 'UPDATE STATISTICS [dbo].[test3]' (0 rows)\r\nTable 4 of 84: Running 'UPDATE STATISTICS [dbo].[ProspectiveCustomers]' (0 rows)\r\nTable 5 of 84: Running 'UPDATE STATISTICS [dbo].[AWBuildVersion]' (1 rows)\r\n...\r\nTable 80 of 84: Running 'UPDATE STATISTICS [Production].[WorkOrderRouting]' (67131 rows)\r\nTable 81 of 84: Running 'UPDATE STATISTICS [Production].[WorkOrder]' (72591 rows)\r\nTable 82 of 84: Running 'UPDATE STATISTICS [Production].[TransactionHistoryArchive]' (89253 rows)\r\nTable 83 of 84: Running 'UPDATE STATISTICS [Production].[TransactionHistory]' (113443 rows)\r\nTable 84 of 84: Running 'UPDATE STATISTICS [Sales].[SalesOrderDetail]' (121317 rows)<\/pre>\n
    Look good? The script below is what you're looking for. By default it runs with FULLSCAN off. Set @WithFullscan = 1 to have every table run with FULLSCAN on.<\/p>\n
        SET NOCOUNT ON\r\n    GO\r\n\r\n    --Determine if you want to execute the script with FULLSCAN\r\n    DECLARE @WithFullscan BIT\r\n    SELECT  @WithFullscan = 0\r\n\r\n    -------------------\r\n    --Begin script\r\n    -------------------\r\n\r\n    DECLARE @StartTime DATETIME\r\n\r\n    SELECT  @StartTime = GETDATE()\r\n\r\n    IF OBJECT_ID('tempdb..#TablesToUpdateStats') IS NOT NULL \r\n        BEGIN\r\n            DROP TABLE #TablesToUpdateStats\r\n        END\r\n\r\n    DECLARE @NumTables VARCHAR(20)\r\n\r\n    SELECT  s.[Name] AS SchemaName\r\n          , t.[name] AS TableName\r\n          , SUM(p.rows) AS RowsInTable\r\n    INTO    #TablesToUpdateStats\r\n    FROM    sys.schemas s\r\n            LEFT JOIN sys.tables t\r\n                ON s.schema_id = t.schema_id\r\n            LEFT JOIN sys.partitions p\r\n                ON t.object_id = p.object_id\r\n            LEFT JOIN sys.allocation_units a\r\n                ON p.partition_id = a.container_id\r\n    WHERE   p.index_id IN ( 0, 1 ) -- 0 heap table , 1 table with clustered index\r\n            AND p.rows IS NOT NULL\r\n            AND a.type = 1  -- row-data only , not LOB\r\n    GROUP BY s.[Name]\r\n          , t.[name]\r\n    SELECT  @NumTables = @@ROWCOUNT\r\n\r\n    DECLARE updatestats CURSOR\r\n    FOR\r\n        SELECT  ROW_NUMBER() OVER ( ORDER BY ttus.RowsInTable )\r\n              , ttus.SchemaName\r\n              , ttus.TableName\r\n              , ttus.RowsInTable\r\n        FROM    #TablesToUpdateStats AS ttus\r\n        ORDER BY ttus.RowsInTable\r\n    OPEN updatestats\r\n\r\n    DECLARE @TableNumber VARCHAR(20)\r\n    DECLARE @SchemaName NVARCHAR(128)\r\n    DECLARE @tableName NVARCHAR(128)\r\n    DECLARE @RowsInTable VARCHAR(20)\r\n    DECLARE @Statement NVARCHAR(300)\r\n    DECLARE @Status NVARCHAR(300)\r\n    DECLARE @FullScanSQL VARCHAR(20)\r\n\r\n    IF @WithFullscan = 1 \r\n        BEGIN\r\n            SELECT  @FullScanSQL = ' WITH FULLSCAN'\r\n        END\r\n    ELSE \r\n        BEGIN --If @WithFullscan<>1 then set @FullScanSQL to empty string\r\n            SELECT  @FullScanSQL = ''\r\n        END\r\n\r\n    FETCH NEXT FROM updatestats INTO @TableNumber, @SchemaName, @tablename,\r\n        @RowsInTable\r\n    WHILE ( @@FETCH_STATUS = 0 ) \r\n        BEGIN\r\n\r\n            SET @Statement = 'UPDATE STATISTICS [' + @SchemaName + '].['\r\n                + @tablename + ']' + @FullScanSQL\r\n\r\n            SET @Status = 'Table ' + @TableNumber + ' of ' + @NumTables\r\n                + ': Running ''' + @Statement + ''' (' + @RowsInTable\r\n                + ' rows)'\r\n            RAISERROR (@Status, 0, 1) WITH NOWAIT  --RAISERROR used to immediately output status\r\n\r\n            EXEC sp_executesql @Statement\r\n\r\n            FETCH NEXT FROM updatestats INTO @TableNumber, @SchemaName,\r\n                @tablename, @RowsInTable\r\n        END\r\n\r\n    CLOSE updatestats\r\n    DEALLOCATE updatestats\r\n\r\n    DROP TABLE #TablesToUpdateStats\r\n\r\n    PRINT 'Total Elapsed Time: ' + CONVERT(VARCHAR(100), DATEDIFF(minute,\r\n                                                              @StartTime,\r\n                                                              GETDATE()))\r\n        + ' minutes'\r\n\r\n    GO<\/pre>\n
    \u00a0<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"
    The following script is the fastest way to update stats on all your tables with the FULLSCAN option.  Developed and tested by SQL Server Consultants.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,3],"tags":[],"class_list":["post-65","post","type-post","status-publish","format-standard","hentry","category-performance-tuning","category-scripts"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/65","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=65"}],"version-history":[{"count":5,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/65\/revisions"}],"predecessor-version":[{"id":67,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/65\/revisions\/67"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=65"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=65"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=65"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}