January 23, 2013 at 9:04 am
I am looking for a script where I can update the statistics of the objects in a database if only the last stat updates date was before any specific date. Any help would be greatly appreciated.
Thank you.
January 23, 2013 at 9:30 am
SET NOCOUNT ON
DECLARE @StatsToUpdate TABLE ( StatName NVARCHAR(MAX) )
DECLARE @StatUpdating NVARCHAR(MAX)
INSERT INTO @StatsToUpdate
( StatName
)
SELECT 'UPDATE STATISTICS dbo.' + OBJECT_NAME + ' ' + index_name
FROM ( SELECT TOP 1
OBJECT_NAME(A.object_id) AS Object_Name ,
A.name AS index_name ,
STATS_DATE(A.OBJECT_ID, index_id) AS StatsUpdated ,
DATEDIFF(d, STATS_DATE(A.OBJECT_ID, index_id),
GETDATE()) DaysOld
FROM sys.indexes A
INNER JOIN sys.tables B ON A.object_id = B.object_id
WHERE A.name IS NOT NULL
AND DATEDIFF(d, STATS_DATE(A.OBJECT_ID, index_id),
GETDATE()) >= 0
) StatsThatAreTooOld
WHILE ( SELECT COUNT(*)
FROM @StatsToUpdate
) > 0
BEGIN
SET @StatUpdating = ( SELECT TOP 1
statname
FROM @StatsToUpdate
)
EXECUTE sp_executesql @StatUpdating
PRINT 'Done ' + @StatUpdating
DELETE FROM @StatsToUpdate
WHERE StatName = @StatUpdating
END
Not the most elegant. Simply looks at the stats and in this case returns a list of stats more than 0 days old and then carrys out an update stats statement
Would need a little tweaking to fully meet your needs probably and there are probably better ways to do this.
January 25, 2013 at 8:01 am
If you're running SQL Server 2008R2 SP2, or SQL Server 2012 SP1, you can use the new statistics DMF sys.dm_db_stats_properties. I have a query for it on my blog: http://www.sqlskills.com/blogs/erin/new-statistics-dmf-in-sql-server-2008r2-sp2/
Otherwise, you'll have to roll through DBCC SHOW_STATISTICS or use STATS_DATE as the previous poster mentioned.
Hope that helps!
Erin
January 25, 2013 at 8:08 am
This is what I use to do that same thing. It is used in a SSIS package and the results are held in an object variable then I loop through that variable executing an update stats command.
select o.object_id,o.name,s.stats_id,s.name
from sys.objects o
inner join sys.stats s on o.object_id = s.object_id
where o.type = 'U'
and stats_date(o.object_id,s.stats_id) <= getdate()-20
order by o.object_id,s.stats_id
January 25, 2013 at 11:15 am
Execute following ---
use dbname
go
exec sp_updatestats
(this sp will detect and automatically update only require)
June 21, 2018 at 4:38 am
bangsql - Friday, January 25, 2013 11:15 AMExecute following ---use dbnamegoexec sp_updatestats(this sp will detect and automatically update only require)
Too good to be true
https://sqlperformance.com/2013/07/sql-statistics/statistics-updates
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply