January 9, 2011 at 10:04 pm
Hi All,
Good Mrng ! Can anyone tell me what is the common used dmv or sp in query analyzer to find out when was the last statistics updated.
____________________________________________________________________
Going in one more round when you don't think you can - that's what makes all the difference in your life --Rocky Balboa
Believe you can and you're halfway there. --Theodore Roosevelt
January 9, 2011 at 10:52 pm
STATS_DATE()
It's a function, use it in conjunction with sys.indexes or sys.statistics.
Or, if you just want the details for one stats set, DBCC SHOW_STATISTICS will also show last update.
Bear in mind that stats that haven't been updated for a while are not necessarily a problem. It's only a problem if there have been lots of data changes in the interim.
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
January 11, 2011 at 4:40 am
Thanks for the reply Gail.
____________________________________________________________________
Going in one more round when you don't think you can - that's what makes all the difference in your life --Rocky Balboa
Believe you can and you're halfway there. --Theodore Roosevelt
January 11, 2011 at 8:53 am
This query has worked for me in determining statistics that may nee to be updated.
USE YourDB;
SELECTsch.name + '.' + obj.name AS TableName,
ind.name AS IndexName,
STATS_DATE(ind.object_id, ind.index_id) AS LastStatsUpdate,
last_user_update, P.row_count,
DATEDIFF(MI,last_user_update, STATS_DATE(ind.object_id, ind.index_id)) AS TimeDiffMinutes
FROM sys.indexes ind
INNER JOIN sys.objects obj
ON obj.object_id = ind.object_id
INNER JOIN sys.schemas sch
ON sch.schema_id = obj.schema_id
INNER JOIN sys.dm_db_index_usage_stats dmv1
ON dmv1.object_id = ind.object_id
AND dmv1.index_id = ind.index_id
INNER JOIN sys.dm_db_partition_stats P
ON P.object_id = ind.object_id
AND P.index_id = ind.index_id
WHERE ind.name IS NOT NULL
AND STATS_DATE(ind.object_id, ind.index_id) IS NOT NULL
AND dmv1.user_updates > 0
AND dmv1.database_id = db_id(N'YourDB')
AND dmv1.last_user_update > STATS_DATE(ind.object_id, ind.index_id)
AND P.row_count > 0
ORDER BY STATS_DATE(ind.object_id, ind.index_id) DESC
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply