Tag Archives: Katmai

A Lovely Statistics Query

Most of you know that you can set SQL to auto update stats.  And I’m sure that most of you know that it invalidates stats at about 20%.  What that means is that when about 20% of the data in the table has changed, the stats are invalidated.  It’s actually 500 + 20% if you wanna be specific.  And notice I say they’re invalidated and not updated.  Stats aren’t updated when that 20% mark is reached, they’re only invalidated.  They’re not rebuilt until they’re needed.  Well, for really large tables that can result in stats being oudated longer than they should because the amount of changes needed to invalidate them is much higher.  The same issue can occur with smaller tables where the changes are varied enough to throw the distribution out of whack.  Some tables are just more sensitive to change than others and that 20% may be too much.  You may find that you need to update stats more often… say at 10% or in extreme cases even 5%.

Here’s a sexy little query I put together that will give you the percentage of change an index has had.  You can use it to not only run a job to update stats on a different percentage, but you can also just query it and see what percentage your tables are at and how fast they change.  It’s an excellent tool for troubleshooting possible stats problems.  All the same, it’s pretty handy. 

–Get stats percentage of change for tables.

SELECT
so.SCHEMA_ID,
ss.NAME AS [Schema],
OBJECT_NAME(id) AS TableName, si.NAME AS IndexName,
CAST((CAST(rowmodctr AS float)/CAST(rowcnt AS float))*100 AS int)  AS Pct,
rowcnt,
STATS_DATE([id], indid) AS [StatsDate]
FROM sys.sysindexes si
INNER JOIN sys.objects so
ON si.id = so.object_id
INNER JOIN sys.schemas ss
ON so.schema_id = ss.schema_id
WHERE rowcnt >= 1000000--500
AND rowmodctr > 0
--AND OBJECT_NAME(id) = 'TableName'
ORDER BY Pct DESC, rowcnt DESC, rowmodctr

Here are a couple notes on the query:

1.  Notice there’s a way to query for a specific table.

2.  The StatsDate col shows you the last time the stats were updated.

3.  I’m also limiting the rowcount to indexes with more than 1mill rows.  Feel free to lower that if you like.

4.  rowmodctr > 0 is something I threw in there to keep the divide by zero error out of there.  This also filters out the system-created stats.

OK, I hope you guys like this one.  I personally love it.  It’s allowed me to build a process to update stats on some of my tables more aggressively than 20% and keep my server running at its peak.

Powershell Webcast by O’Reilly

Hey guys… I’m starting a webcast series next week on Beginning Powershell for SQL Server DBAs.

This is an excellent chance for those of you who have always wanted to get started with powershell to do so.

It’ll be a ground zero course so if you don’t know anything at all about powershell I’ll attempt to bring you up.

I hope to see you all there.

Here’s a link to the webcast and I hope all of you can join me.

Signup and tell all your friends.  If we get enough people signup we’ll be able to keep this series alive.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground – http://www.infoworld.com/blogs/sean-mccown

Follow my Twitter:

http://twitter.com/MidnightDBA

The Runaway Instance

I recently installed the latest Katmai CTP on my workstation at home. I installed the entire package. I connected to it just fine and worked with it for several days. I then went to hit it with an external client so I could load some data, but it wouldn’t connect. I checked and it was set for remote connections, and it was listening on 1433 so that wasn’t the problem. I didn’t know if there might not be some kind of glitch so I burped the service from the Katmai Config Mgr. The service failed to restart. The message I got was that 1433 was already in use. OK, at least I have a message to troubleshoot now. Well, I’ve got Pinnacle running on that box and it uses SQL Express. So since I installed Katmai as a default instance as well, it was clear that Pinnacle was stepping on it… even though Yukon was on the box before and it never had issues with Pinnacle.

Anyway though… I stopped the express service and I still couldn’t connect. So to make a long story shorter, because I spent a very long time on this, it turns out that the Katmai Control Mgr was falsely reporting the express instance as stopped when it was really still on. When I went into Windows SCM it was still running. So I stopped it from Windows and all’s well.

At least I got some good troubleshooting practice in, huh?