Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to determine when column stats are updated? Expand / Collapse
Author
Message
Posted Monday, June 25, 2012 10:06 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:11 AM
Points: 83, Visits: 502
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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1320792
Posted Monday, June 25, 2012 3:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:40 PM
Points: 7,084, Visits: 12,576
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


  Post Attachments 
StatProperties.jpg (183 views, 27.71 KB)
StatDate.jpg (184 views, 62.66 KB)
Post #1320917
Posted Monday, June 25, 2012 3:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:40 PM
Points: 7,084, Visits: 12,576
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

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:17 AM
Points: 42,466, Visits: 35,533
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1320931
Posted Friday, June 29, 2012 9:06 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:11 AM
Points: 83, Visits: 502
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

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:17 AM
Points: 42,466, Visits: 35,533
If you use the maintenance plan task, it will update everything.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1323125
Posted Friday, June 29, 2012 9:29 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:11 AM
Points: 83, Visits: 502
Ok. A-scripting I shall go!
Post #1323136
Posted Friday, June 29, 2012 10:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:40 PM
Points: 7,084, Visits: 12,576
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
Post #1323174
Posted Friday, June 29, 2012 11:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:17 AM
Points: 42,466, Visits: 35,533
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1323198
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse