Mindy Hreczuck (6/25/2012)
I have index statistics taken care of by a scripted maintenance plan but I'm going to be setting up a separate plan to take care of updating the column statistics. Is there a query for determining when the columns were last updated? I've determined what columns are not in any indexes to cover "yes, I need to update column stats" but they don't appear in sys.stats so it looks like that table just contains info for the index stats.
Using SSMS 2012 against a SQL 2008R2 instance if I right-click on a stat in SSMS and go to Properties...
I see the last update date:
Tracing what SSMS 2012 did I was able to poach this query to get a column stat update date:
DECLARE @statistic_name SYSNAME = N'_WA_Sys_00000002_7D78A4E7',
@table_name SYSNAME= N'activity_snapshot',
@schema_name SYSNAME = N'dbo';
SELECT STATS_DATE(st.object_id, st.stats_id) AS [LastUpdated]
FROM sys.tables AS tbl
INNER JOIN sys.stats st ON st.object_id = tbl.object_id
WHERE (st.name = @statistic_name)
AND (
(tbl.name = @table_name
AND SCHEMA_NAME(tbl.schema_id) = @schema_name )
);
I ran a query with a non-indexed column in the WHERE-clause to force SQL Server to create a new column stat and I was able to see the update date using the above query. As for how to differentiate the stat update date from the stat create date, I am not sure if we can do that.
I also ran this to update the column stat:
UPDATE STATISTICS dbo.activity_snapshot _WA_Sys_00000002_7D78A4E7;
and the query (and UI) showed a new update date.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato