williamweber.net

We use partitioning quite heavily at my current job. And in order to take advantage of switching data from a partitioned table into a staging table all indexes must be aligned on the partition scheme. So, I was frustrated to find out that when you attempt to script out the create statement for an index in SQL Sever Managment Studio (SSMS) 2008 it does not include the clause to create the index on the partition scheme that the index was created on.

Thankfully, Microsoft wasn’t so dense as to remove this funtionality entirely, they simply turned it off by default (which I would argue is ridiculous, but they aren’t really going to listen to little old me). So, here’s the fix.

Open up Tools -> Options then change the setting at: SQL Server Object Explorer -> Scripting -> Script Partition Schemes to TRUE. (it’s at the very bottom of the list)

I wanted a more granular level of detail when looking at disk space usage of our databases. I wanted to see the disk usage of each table. Since we use partitioning pretty heavily I also wanted to see how that usage was broken out by partition and filegroup. In our implementation we have a separate filegroup for each table partition but in a configuration that has multiple parititons on a partition this script can tell you usage for either metric.

Normally I’ll take this query and take the results over to excel and pivot the data so that I can filter and sum data in whatever way I want. I also have this running in a SQL Agent job and inserting into a table so that I connect to that table and do growth trend analysis.

Read more…