How to determine when column stats are updated?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2005 » Administering » How to determine when column stats are...

Add to briefcase

12 posts, Page 2 of 2

««1 2

How to determine when column stats are updated?

Expand / Collapse

Author

Message

Posted Friday, June 29, 2012 12:32 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 6,711, Visits: 11,744

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1323225

Posted Sunday, July 01, 2012 2:33 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:36 AM
Points: 5,201, Visits: 11,153

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" Wink

Wink

Post #1323538

« Prev Topic | Next Topic »

Add to briefcase

12 posts, Page 2 of 2

««1 2

Permissions

Expand / Collapse