Statistics

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Statistics

Statistics

Rate Topic

Display Mode

Topic Options

Author

Message

Vinodh247

Posted Sunday, January 09, 2011 10:04 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:55 AM
Points: 248, Visits: 631

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

GilaMonster

Posted Sunday, January 09, 2011 10:52 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

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

Vinodh247

Posted Tuesday, January 11, 2011 4:40 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:55 AM
Points: 248, Visits: 631

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

Chuck Hottle

Posted Tuesday, January 11, 2011 8:53 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 2:39 PM
Points: 197, Visits: 1,065

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 »

Permissions