Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Statistics Expand / Collapse
Author
Message
Posted Sunday, January 9, 2011 10:04 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 4:54 AM
Points: 248, Visits: 676
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
Post #1045109
Posted Sunday, January 9, 2011 10:52 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1045115
Posted Tuesday, January 11, 2011 4:40 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 4:54 AM
Points: 248, Visits: 676
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
Post #1045801
Posted Tuesday, January 11, 2011 8:53 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:31 AM
Points: 198, Visits: 1,127
This query has worked for me in determining statistics that may nee to be updated.

USE YourDB;

SELECT sch.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
Post #1045956
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse