SQLDIY: Tracking Space Usage At the Index Level

I am a fan of information, the more the merrier. Anytime I’m tasked with tracking a production server over the long haul I try and gather as many data points as I can. You can capture database sizes but when your database blows up in size its nice to be able to drill down and find out if it was a new index or correlates with a change in code at a point in time. In this script I’m falling back on sp_spaceused to gather that information. I could query several DMV’s to get the same or more information. I could also put in DDL triggers for tracking things like schema changes if I need to the minute and alerting to go along with it. This particular view is more of a “close enough” look at your database in some detail. As we get into more detail index tracking you will see some more specific numbers on row counts, index size and other index specific details. You may see a pattern emerging here. My goal is to gather data in a timely and contestant fashion. We will also gather more detailed meta data on schema later as well.

Series to Date SQLDIY: Manage and Monitor SQL Server Yourself

Link to the script Gather Table Statistics

I’m hosting all my scripts from this series on GitHub as I do with all my open source projects.

As always, if you find any bugs please let me know and I will correct them!

UPDATE:

Aaron Bertrand sugested that I take a look at sys.dm_db_partition_stats instead of sp_spaceused and as usual he was spot on. By moving to sys.dm_db_partition_stats I was able to cut the table loop out completely and sped up the performance quite a bit. New version is up.

Posted on June 29, 2011, in SQLDIY, SQLServerPedia Syndication, Syndicated. Bookmark the permalink. 4 Comments.

  1. Wes, it seems to be it would be easier to query sys.dm_db_partition_stats once to get all of this information, instead of looping through every table and calling sp_spaceused (is there anything sp_spaceused really provides that this one DMV does not?). Performance isn’t the biggest benefit here; I imagine the code would be much more maintainable as well.

  2. Ok, I see. I’ll do a re-write tonight. Most of these scripts I’ve had knocking around for the better part of a decade. I did a pass several years ago to replace the old sys table calls with DMV’s without fully exploring all the new DMV’s.

    Thanks again Aaron, it’s nice to see someone else besides me actually looking at the code :)

  1. Pingback: SQLDIY: Manage and Monitor SQL Server Yourself | SQL Server Input/Output

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: