Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How to determine when column stats are updated? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, June 25, 2012 10:06 AM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, November 3, 2016 11:45 AM Points: 94, Visits: 531
 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.
Post #1320722
 Posted Monday, June 25, 2012 11:13 AM
 SSChampion Group: General Forum Members Last Login: Today @ 9:48 AM Points: 14,541, Visits: 38,381
 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 analysisSELECT 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.idWHERE P.index_id in (0,1) AND I.rowmodctr > 0) XWHERE [Rows Modified] > 1000ORDER 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!
Post #1320792
 Posted Monday, June 25, 2012 3:41 PM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 3:25 AM Points: 7,933, Visits: 14,355
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_idWHERE   (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

Post Attachments
 StatProperties.jpg (198 views, 27.71 KB)
 StatDate.jpg (199 views, 62.66 KB)
Post #1320917
 Posted Monday, June 25, 2012 3:46 PM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 3:25 AM Points: 7,933, Visits: 14,355
 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
Post #1320919
 Posted Monday, June 25, 2012 4:28 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 5:18 AM Points: 45,619, Visits: 44,147
 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 ShawMicrosoft 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 enterWe stand on the bridge and no one may pass
Post #1320931
 Posted Friday, June 29, 2012 9:06 AM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, November 3, 2016 11:45 AM Points: 94, Visits: 531
 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?
Post #1323124
 Posted Friday, June 29, 2012 9:09 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 5:18 AM Points: 45,619, Visits: 44,147
 If you use the maintenance plan task, it will update everything. Gail ShawMicrosoft 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 enterWe stand on the bridge and no one may pass
Post #1323125
 Posted Friday, June 29, 2012 9:29 AM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, November 3, 2016 11:45 AM Points: 94, Visits: 531
 Ok. A-scripting I shall go!
Post #1323136
 Posted Friday, June 29, 2012 10:31 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 3:25 AM Points: 7,933, Visits: 14,355
 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 HallengrenSetting 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
Post #1323174
 Posted Friday, June 29, 2012 11:25 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 5:18 AM Points: 45,619, Visits: 44,147
 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 ShawMicrosoft 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 enterWe stand on the bridge and no one may pass
Post #1323198

 Permissions