[statistics] Get the last 4 stat update time for every index on every table in a database.

Quickie, based off an earlier post. (http://thebakingdba.blogspot.com/2012/02/tuning-statistics-when-were-they.html)


Get the last 4 stat updates for every statistic based on an index. The filter is on the auto_created; flip that to get all the system-created (aka _WA_Sys_00000003_53D770D6) stats, or index-based.

[Statistics] Let's find the last 4 update times for each statistic in a database.

Needed to stretch my prior post's code.

[statistics] Find last update time for all statistics for a table

(tbd 2015/09/09 - added truncate table to the loop.  Doh!)

Statistics matter. So you want to find when they were last updated.  You usually see this one:


SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('pad_tx')

Which works - for all statistics tied to an index.  But SQL Server has more than just those: there are also the _WA_Sys stats, which begin with that phrase and are created by the engine.  (IIRC, you can also see DTA stats, built by the Database Tuning Advisor) 

In theory you shouldn't have many of them, because they're only generated if you're querying a field that isn't part of an index.  Theoretically you should clean them out (that may be another post), since a stat could be in there twice - once for an index and once because you queried the table (creating the stat) before you created the index.

This should list all of them.  

DECLARE @sql NVARCHAR (1000), @MIN INT, @MAX INT, @statname NVARCHAR(128)

declare @listofstats TABLE (id int identity, NAME sysname)

INSERT INTO @listofstats (name) 

SELECT name FROM sys.stats WHERE object_id = object_id('yourtablenamehere')

if object_id('tempdb..#stats_info') is not null

    DROP TABLE #stats_info

CREATE TABLE #stats_info (updated DATETIME, [Table cardinality] BIGINT, [snapshot ctr] BIGINT, steps INT, density DECIMAL(19,16), [rows above] INT, [rows below] INT, [squared variance error] DECIMAL (19,16), [inserts since last update] MONEY, [deletes since last update] MONEY, [leading column type] VARCHAR(50))

if object_id('tempdb..#stats_info2') is not null

    DROP TABLE #stats_info2

CREATE TABLE #stats_info2 (stat_name VARCHAR(128), updated DATETIME, [Table cardinality] BIGINT, [snapshot ctr] BIGINT, steps INT, density DECIMAL(19,16), [rows above] INT, [rows below] INT, [squared variance error] DECIMAL (19,16), [inserts since last update] MONEY, [deletes since last update] MONEY, [leading column type] VARCHAR(50))

SELECT @MIN = MIN(ID), @max = MAX(id) FROM @LISTOFSTATS

DBCC TRACEON (2388)

WHILE @min <= @max

begin

TRUNCATE TABLE #stats_info
 SELECT @sql = NULL, @statname = null

SELECT @statname = NAME FROM @listofstats WHERE id = @min

SELECT @sql = N'DBCC SHOW_STATISTICS (''yourdatabasenamehere..yourtablenamehere'','''+ @statname +''')'

INSERT INTO #stats_info 

EXEC master..sp_executesql @sql

INSERT INTO #stats_info2 SELECT @statname, * FROM #stats_info

SET @min = @min + 1

end

DBCC TRACEOFF (2388)

SELECT #stats_info2.stat_name, #stats_info2.updated FROM #stats_info2 INNER JOIN (SELECT stat_name, MAX(updated) AS max_date FROM #stats_info2 GROUP BY stat_name)a 

ON #stats_info2.stat_name = a.stat_name AND #stats_info2.[updated] = a.max_date

ORDER BY #stats_info2.stat_name

[tuning] Statistics - when were they created... OR RECENTLY UPDATED?

(update 2013/08/07 - since it shows recent times...)

Courtesy of Nayan Raval & #SQLHelp, which led me to another article (http://blogs.solidq.com/fabianosqlserver/post.aspx?id=52&title=undocumented+option(querytraceon+%3Ctracenumber%3E)+and+trace+flags+2388%2C+2389%2C+2390) which documents 2 more undocumented trace flags.

How do you find out when statistics were created?  If it's on an indexed field, when the index was created (crdate in sys.indexes).  But statistics on non-indexed fields?  Using Trace Flag 2388 changes the information that SHOW_STATISTICS returns.


DBCC TRACEON (2388)
DBCC SHOW_STATISTICS ('yourtablename','_WA_Sys_x')
DBCC TRACEOFF (2388)

Look for the row with the oldest Updated. The oldest update, depending on how often it's been updated, MAY show you when it was created.

And there's another use for this trace flag... when, aside from the most-recent date, was a statistic updated?   STATS_DATE will show you the LAST time it was updated, but I recently had a problem where I knew the problem existed and quickly updated stats, then realized I wanted to know when BEFORE then it had been updated.  One search to my blog later, code found, and there we go.

[Maint] Quick and Dirty maintenance

Is this suitable most places? No. Is it fast and easy? Yes.

Reindex all tables in a database (2000 & 2005)

exec sp_MSforeachtable "DBCC DBREINDEX ('?')"

or

EXEC sp_MSforeachtable "print '?' DBCC DBREINDEX ('?', ' ', 85)"

Update statistics in a database
For 2000 (since sp_updatestats can break certain things)

EXEC sp_MSforeachtable "update STATISTICS ?"

For 2005:

sp_updatestats