This post will not change your life, but it will help me remember something.
When you decide to partition a table to take advantage of data management features, because IT IS NOT A PERFORMANCE FEATURE, or you have an existing partitioned table that takes advantage of data management features, because IT IS NOT A PERFORMANCE FEATURE, you may have or end up with nonclustered indexes that aren’t aligned with the partitioning scheme.
This is a bad thing because it will break those precious data management features. Of course, there are edge cases where you may want a nonaligned index, but at that point you’re probably just living with legacy partitioning that someone thought they’d use for performance even though IT IS NOT A PERFORMANCE FEATURE.
Options To Fix Things
The heading is a lie. There’s only one way to fix things, but I always forget it.
The tricky part is that you can’t use
ALTER INDEX REBUILD... to do it. Your only hope is to use
DROP EXISTING, or just create a new index and drop the old one.
Here’s an example. Let’s set up a table.
CREATE PARTITION FUNCTION pfunc ( INT )
AS RANGE RIGHT FOR VALUES ( 0, 1, 10, 100, 1000, 10000, 100000, 1000000, 10000000 );
CREATE PARTITION SCHEME pscheme
AS PARTITION pfunc
ALL TO ( [PRIMARY] );
CREATE TABLE dbo.ptable
id INT PRIMARY KEY CLUSTERED ON pscheme(id),
INSERT dbo.ptable WITH(TABLOCK)
( id, filler_bunny )
SELECT x.n, 'DETH'
SELECT TOP (1000 * 1000) ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2
The good news is that if you create an index with no direction, it will default to aligning with the partitioning scheme.
CREATE INDEX ix_fun_boy_one ON dbo.ptable(id);
CREATE INDEX ix_fun_bunny_one ON dbo.ptable(filler_bunny);
How do I know it’s so? Because sp_BlitzIndex tells me!
EXEC dbo.sp_BlitzIndex @DatabaseName = N'Crap',
@SchemaName = N'dbo',
@TableName = N'ptable';
But if you’re a silly goose — you know the type — the same kind of person who runs around creating nonclustered primary keys and then wondering why we call their tables heaps? You might create an index like this, or an index like this may be hanging around from before the table was partitioned.
CREATE INDEX ix_fun_boy_two ON dbo.ptable(id) ON [PRIMARY];
CREATE INDEX ix_fun_bunny_two ON dbo.ptable(filler_bunny) ON [PRIMARY];
Our new indexes are not partitioned! No good can come of this.
So Here’s The Part I Always Bungle
Index rebuilds are great for changing things about an index other than fragmentation. There are all sorts of lovely options.
Unfortunately, rebuilding on a partitioning scheme isn’t one of them.
Fun. Big fun.
CREATE INDEX ix_fun_boy_two ON dbo.ptable(id) WITH (DROP_EXISTING = ON) ON pscheme(id);
CREATE INDEX ix_fun_bunny_two ON dbo.ptable(filler_bunny) WITH (DROP_EXISTING = ON) ON pscheme(id);
Your other option would be to create indexes already aligned with the full syntax.
CREATE INDEX ix_fun_boy_three ON dbo.ptable(id) ON pscheme(id);
CREATE INDEX ix_fun_bunny_three ON dbo.ptable(filler_bunny) ON pscheme(id);
All Lined Up
I forget this a lot because I so rarely see clients using partitioning, at least in a meaningful way.
The conversation always goes something like:
- “Did you know this table is partitioned?”
- “No, wait, yeah, someone set that up years ago to fix [some problem].”
- “So you’re not swapping partitions in and out?”
- “Do you want to?”
- “Cool, moving along!”
In those cases, it’s not worth going through the trouble to align those indexes, especially is one of them is the clustered index. You may be looking at a maintenance window. If your database is part of log shipping, mirroring, or an availability group, processing large index changes can really raise hell.
Thanks for reading!