How to Update Statistics
-
Posted on June 20, 2009 by Derek Dieter
-
1
There are two ways to update statistics. The first way is the easy way. It is one line of code that will update all the statistics in the database using the default sample size of 20,000 rows per table.
[cc lang=”sql”]
EXEC sp_updatestats
[/cc]
The other way, is to use the UPDATE STATISTICS command. This command gives much better granularity of control:
[cc lang=”sql”]
— Update all statistics on a table
UPDATE STATISTICS Sales.SalesOrderDetail
— Update a specific index on a table
UPDATE STATISTICS Sales.SalesOrderDetail IX_SalesOrderDetail
— Update one column on a table specifying sample size
UPDATE STATISTICS Production.Product(Products) WITH SAMPLE 50 PERCENT
[/cc]
Using update statistics can give you the granularity of control to only update the out of date statistics, thus having less impact on your production system.
The following script updates all out of date statistics. Set the @MaxDaysOld variable to the number of days you will allow the statistics to be out of date by. Setting the @SamplePercent variable to null will use the SQL Server default value of 20,000 rows. You can also change the sample type to specify rows or percent.
[cc lang=”sql”]
DECLARE @MaxDaysOld int
DECLARE @SamplePercent int
DECLARE @SampleType nvarchar(50)
SET @MaxDaysOld = 0
SET @SamplePercent = NULL –25
SET @SampleType = ‘PERCENT’ –‘ROWS’
BEGIN TRY
DROP TABLE #OldStats
END TRY
BEGIN CATCH SELECT 1 END CATCH
SELECT
RowNum = ROW_NUMBER() OVER (ORDER BY ISNULL(STATS_DATE(object_id, st.stats_id),1))
,TableName = OBJECT_SCHEMA_NAME(st.object_id) + ‘.’ + OBJECT_NAME(st.object_id)
,StatName = st.name
,StatDate = ISNULL(STATS_DATE(object_id, st.stats_id),1)
INTO #OldStats
FROM sys.stats st WITH (nolock)
WHERE DATEDIFF(day, ISNULL(STATS_DATE(object_id, st.stats_id),1), GETDATE()) > @MaxDaysOld
ORDER BY ROW_NUMBER() OVER (ORDER BY ISNULL(STATS_DATE(object_id, st.stats_id),1))
DECLARE @MaxRecord int
DECLARE @CurrentRecord int
DECLARE @TableName nvarchar(255)
DECLARE @StatName nvarchar(255)
DECLARE @SQL nvarchar(max)
DECLARE @SampleSize nvarchar(100)
SET @MaxRecord = (SELECT MAX(RowNum) FROM #OldStats)
SET @CurrentRecord = 1
SET @SQL = ”
SET @SampleSize = ISNULL(‘ WITH SAMPLE ‘ + CAST(@SamplePercent AS nvarchar(20)) + ‘ ‘ + @SampleType,N”)
WHILE @CurrentRecord <= @MaxRecord BEGIN SELECT @TableName = os.TableName ,@StatName = os.StatName FROM #OldStats os WHERE RowNum = @CurrentRecord SET @SQL = N'UPDATE STATISTICS ' + @TableName + ' ' + @StatName + @SampleSize PRINT @SQL EXEC sp_executesql @SQL SET @CurrentRecord = @CurrentRecord + 1 END [/cc] After updating the statistics, the execution plans that use these statistics may become invalid. Ideally SQL Server should then create a new execution plan. Personally, I prefer to help SQL Server out by flushing the cache. I would recommend you do the same. Note, this clears the entire procedure cache for the server, not just the database. [cc lang="sql"] -- Clears the procedure cache for the entire server DBCC FREEPROCCACHE [/cc] You should then also update the usage stats. Usage stats are the row counts stored for each index: [cc lang="sql"] -- Update all usage in the database DBCC UPDATEUSAGE (0); [/cc] If you are not already doing so, it is highly recommended to leave the default settings of "Auto Update Statistics" and "Auto Create Statistics" ON.
- Comments (RSS)
- Trackback
- Permalink
31 Matt Jones Jersey.Cheap 2014 New 75 Brandon Scherff Jersey,
MLB/NBA/NFL/NHL/NCAA Jerseys From China Free Shipping, NHL Jerseys, NBA Jerseys, 34 Shane Vereen Jersey From factory.
@Dinesh – if you delete a most of the data then you should also rebuild your indexes which will take care of updating statistics.
Can someone offer a better way?
if there are only a few records (20,000) scan all of them, beyond that a percent, beyond that a fixed number to prevent long running scans.
Also exclude system tables, and scan each table once (don’t specify the stat you want to update.
作業系統 winxp sp3 (在win 7 64bit中VM)錯誤提示 Error executing SQL.Error [26]: File opneed that is not a database file.”SELECT * FROM [UpFileHis]”: file is encrypted or is not a database.Access violation at address 00616755 in module ‘UDown.exe’. Read of address 00000008.無效的類別字串, ProgID: “transfer.Config”.
Thanks for the very nice post…I have a minor issue: can you do a cast on Null values that you did here: CAST(@SamplePercent AS nvarchar(20)), I am getting problem here.
Thank you for this!
Saved me some work. Also really nice explained 🙂
Thank you again.
The dynamic sql fails in certain circumstances… For instance, the DB I’m working with has a table named fim.Set. The statement generated is:
UPDATE STATISTICS fim.Set _WA_Sys_00000002_3B75D760
It needs to be:
UPDATE STATISTICS fim.[Set] _WA_Sys_00000002_3B75D760
Also, I had to add brackets around the index name, as well, as some of the indexes have a hyphen in them and SQL doesn’t like that either.
You are the man, thank you for saving me hours!
Simple and after 10 websites of crap this fixed my problem with one line of code and 20 seconds of run time.
Thank you for the code.
(a minor issue: there are some html escape codes in the SQL: less-than, greater-than things)