How to determine when column stats are updated?
-
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.
-
I don't think when a statsitic has been updated/created is exposed;
however, you can use a formulat to determine if any existing statistics ned to be updated ro not;
I slapped this example together a long time ago (it's using sysindexes instead of sys.indexes Sorry Gail!)
the idea behind it is any table with 1000 rows having been modified is worth of a statistics update, and depedning on hte size fo the table, more often than the default of 20% of the rows; big tables often need statsitics updated more often than that.
see if this helps?
--The Analysis: my crappy assumptions:
--UPDATE STATISTICS dbo.GMPVDET
--tables under 1000 rows, I'll leave then at 20%
--tables with more rows than that, I'll use an arbitrary sliding scale formula.
--formula to be modified based on analysis
SELECT X.*,
ISNULL(CASE
WHEN X.[Total Rows]<=1000
THEN
CASE
WHEN [Percent Modified] >=20.0
THEN 'UPDATE STATISTICS ' + [Schema Name] + '.' + [Table Name] + ' WITH ALL, FULLSCAN --20% Small Table Rule'
END
WHEN [Percent Modified] = 100.00
THEN 'UPDATE STATISTICS ' + [Schema Name] + '.' + [Table Name] + ' WITH ALL, FULLSCAN --100% No real Stats Rule'
--WHEN X.[Rows Modified] > 1000
--THEN 'UPDATE STATISTICS ' + [Schema Name] + '.' + [Table Name] + ' WITH ALL, FULLSCAN --1000 Rows Modified Rule'
ELSE
CASE
WHEN X.[Total Rows] > 1000000000 --billion rows
THEN CASE
WHEN [Percent Modified] > 0.1
THEN 'UPDATE STATISTICS ' + [Schema Name] + '.' + [Table Name] + ' WITH ALL, FULLSCAN -- 1B Big Table Rule'
END
WHEN X.[Total Rows] > 100000000 --hundred million rows
THEN CASE
WHEN [Percent Modified] > 1.0
THEN 'UPDATE STATISTICS ' + [Schema Name] + '.' + [Table Name] + ' WITH ALL, FULLSCAN -- 100M Big Table Rule'
END
WHEN X.[Total Rows] > 10000000 --ten million rows
THEN CASE
WHEN [Percent Modified] > 2.0
THEN 'UPDATE STATISTICS ' + [Schema Name] + '.' + [Table Name] + ' WITH ALL, FULLSCAN -- 10M Big Table Rule'
END
WHEN X.[Total Rows] > 1000000 --million rows
THEN CASE
WHEN [Percent Modified] > 5.0
THEN 'UPDATE STATISTICS ' + [Schema Name] + '.' + [Table Name] + ' WITH ALL, FULLSCAN -- 1M Big Table Rule'
END
WHEN X.[Total Rows] > 100000 --hundred thousand rows
THEN CASE
WHEN [Percent Modified] > 10.0
THEN 'UPDATE STATISTICS ' + [Schema Name] + '.' + [Table Name] + ' WITH ALL, FULLSCAN -- 100K Big Table Rule'
END
WHEN X.[Total Rows] > 10000 --ten thousand rows
THEN CASE
WHEN [Percent Modified] > 20.0
THEN 'UPDATE STATISTICS ' + [Schema Name] + '.' + [Table Name] + ' WITH ALL, FULLSCAN -- 10K Big Table Rule'
END
END
END,'') AS [Statistics SQL]
FROM (
SELECT DISTINCT
DB_NAME() AS [Database],
S.name AS [Schema Name],
T.name AS [Table Name],
I.rowmodctr AS [Rows Modified],
P.rows AS [Total Rows],
CASE
WHEN I.rowmodctr > P.rows
THEN 100
ELSE CONVERT(decimal(8,2),((I.rowmodctr * 1.0) / P.rows * 1.) * 100.0)
END AS [Percent Modified]
FROM
sys.partitions P
INNER JOIN sys.tables T ON P.object_Id = T.object_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
INNER JOIN sysindexes I ON P.object_id = I.id
WHERE P.index_id in (0,1)
AND I.rowmodctr > 0
) X
WHERE [Rows Modified] > 1000
ORDER BY [Rows Modified] DESC
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
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 -
I thought I was in a SQL 2008 Forum when I posted, so I tested on SQL 2008. I noticed I was in a SQL 2005 Forum after I posted. I just tested the query against a SQL 2005 database and it behaved the same.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Just one point... Column statistics do indeed appear in sys.stats, all statistics are in that view both column and index. Index stats get updated when the index is rebuilt, column stats only get updated when update stats is run or an auto update kicks in.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks guys, yes I see the numbers in the properties of my SQL 2005 so that's good info.
And Gail, reading this I came to realize that as well. The query I had was tweaked so among the table joins it was only returning the stats for the indexes.
So here's my next question, is it a safe assumption that if I use the built-in update statistics task that it will do all columns, not just outdated ones? I updated the stats for columns on a copy of a db last night via that task and it ran 14 hrs... and most of the time was just on one (the largest) table. So if I want to break it down so it will finish within maintenance windows, I'm assuming I'll need to script it out. Yes?
-
If you use the maintenance plan task, it will update everything.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Ok. A-scripting I shall go!
-
You may have something very granular or customizable planned in terms of your scripting effort so this may not be applicable, or these options may not satisfy your requirements, but I figured I would throw them out there since they were not mentioned and they may save you some time:
1. sp_updatestats (SQL Server 2005).
In SQL Server 2005, sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items.
2. SQL Server Index and Statistics Maintenance by Ola Hallengren
Setting the UpdateStatistics parameter to COLUMNS, the OnlyModifiedStatistics parameter to YES and the StatisticsSample to FULLSCAN or SAMPLE you can have it only update stats that have changed since the last stats update, and with a sample size of your choice.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (6/29/2012)
You may have something very granular or customizable planned in terms of your scripting effort so this may not be applicable, or these options may not satisfy your requirements, but I figured I would throw them out there since they were not mentioned and they may save you some time:1. sp_updatestats (SQL Server 2005).
In SQL Server 2005, sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items.
Well, BoL's not entirely accurate there. The 'threshold' that sp_updatestats uses is 1 row.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (6/29/2012)
opc.three (6/29/2012)
You may have something very granular or customizable planned in terms of your scripting effort so this may not be applicable, or these options may not satisfy your requirements, but I figured I would throw them out there since they were not mentioned and they may save you some time:1. sp_updatestats (SQL Server 2005).
In SQL Server 2005, sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items.
Well, BoL's not entirely accurate there. The 'threshold' that sp_updatestats uses is 1 row.
Parsing a bit, but unchanged seems to be an accurate characterization. Misleading may be a fair assessment of that slice of the doc though. Ola's code does the same, checking this when @OnlyModifiedStatistics = 'Y':
sysindexes.[rowmodctr] <> 0
That's why I hedged and said something very granular or customizable planned in terms of your scripting effort. If one wanted to check rowmodctr manually and compare that to the number of rows in the stat it could help get closer to smart stats updates. I thought rowmodctr might become skewed since the column tracks updates but the engine seems to be aware when the same index entry is updated, meaning rowmodctr could be of service. At any rate, I read that since SQL 2005 the real story (the one used by auto update stats algorithms) is not available in any catalog view, only system tables that require we use DAC to see.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
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.The function STATS_DATE() exposes the date\time of the last stats updates.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply