SQL Server Finding Statistic Update Dates For Entire Database

This morning I came into one of my DBA’s telling me we had an unexplained cpu spike of 90%. This was a bit concerning since we had a release the night before, and there was a concern some of the new code might be causing issues. After reviewing logs, traces and perfmon we were unable to determine the issue. After doing a deeper dive we were able to determine the issue stemmed from a 50 million row table updating its statistics. The positive was we found the issue, the negative was it took us almost an hour to get it. Following the resolution we decided that checking statistics update was to be a part of the normal troubleshooting procedures, for unexplained resource spikes. The problem is there’s no specific SP, function or DMV that walks the entire database to provide this information. Most statistic data is surfaced on a per stat or table basis. For that reason I wrote the following script to gather this information by leveraging the stats_date system function.




drop table #TableIndexHold
Drop table #StatsDate

--Holds indexes and table names
create table #TableIndexHold
(TBLID int, IdxID int, TblName varchar(100), IndexName varchar(100) )

insert into #TableIndexHold
select id,indid, o.name, i.name from sys.sysindexes i
inner join sys.objects o on i.id = o.object_id
where o.type = 'u'

Declare @Tbl int
Declare @Index int
Declare @TblName varchar(100)
Declare @IndexName Varchar(100)

--Create Result Table
Create table #StatsDate
(TableName Varchar(100),IndexName varchar(100), StatsDate datetime)



Declare Stats_Cursor Cursor for
Select TBLID, IdxID, TblName, IndexName from #TableIndexHold

Open Stats_Cursor
Fetch Next from Stats_Cursor
into @Tbl,@Index,@TblName,@IndexName

WHILE @@FETCH_STATUS = 0
BEGIN
Insert into #StatsDate
Select @TblName, @IndexName, Stats_Date(@Tbl, @Index)

Fetch Next from Stats_Cursor
into @Tbl,@Index,@TblName,@IndexName
End

Close Stats_Cursor
Deallocate Stats_Cursor


Select * from #StatsDate