Geek City: My Big Fat Partitioning Query

Kalen Delaney — Mon, 26 Jul 2010 15:45:00 GMT

When SQL Server first introduced table and index partitioning in SQL 2005, it was the same version that all the metadata changed. So I decided that I could learn a lot about metadata and a lot about the organization and storage of partitions if I tried...(read more)

Geek City: ALTERing a Partition Function

Kalen Delaney — Sun, 16 Aug 2009 20:31:00 GMT

Finally, a real geeky post. I received two questions in as many weeks about what really happens to existing partitions when you alter the underlying partition function. The big question being… does altering a partition function cause any data movement?...(read more)

Did You Know? What's a $ Worth?

Kalen Delaney — Sat, 13 Oct 2007 23:29:00 GMT

No, I'm not talking about US dollars, which are not worth much at all these days. I'm talking about the $ used in a particular construct in SQL Server 2005 to get information about a partition definition. There is a construct called $PARTITION which the BOL actually refers to as a function, but it doesn't act like any other function in the product. What's strange about it is that it gets concatenated to another function, which must be a previously defined partitioning function. My contacts at Microsoft have actually said we should think of $PARTITION more like a special namespace that exists in each database, and contains all of the partition functions we have defined.

I'm going to define a simple partition function with 3 boundary points (4 partitions) and a simple partition scheme that uses the same filegroup for all the partitions. I'll show you the usage of $PARTITION and also show you a pretty nasty bug that was just reported on the public newsgroups that can occur when using $PARTITION. I suggest using a test database for this example. Create one if you don't have one already.

-- CREATE DATABASE testdb
USE testdb;
GO
CREATE PARTITION FUNCTION myRangePF (int)
AS RANGE LEFT FOR VALUES (2500, 5000, 7500);
GO
CREATE PARTITION SCHEME myRangePS
AS PARTITION myRangePF
ALL TO ([PRIMARY]);
GO

As soon as you've created the partition function, you can use $PARTITION, as it doesn't require any actual data. It will tell you which partition any particular value would be in when using the specified function. So

SELECT $PARTITION.myRangePF(2500);

will tell you that 2500 will be in partition 1. In fact, the main reason I use $PARTITION is to verify my boundary values, and make sure they are in the partition that I expect them to be in.

However, you can also use $PARTITION on a populated table. Let me create a partitioned table and insert 10000 rows into it.

CREATE TABLE dbo.myRangeTable (c1 INT, c2 CHAR (10))
ON myRangePS (c1)
GO
SET NOCOUNT ON
GO
DECLARE @n INT;
SELECT @n = 1;
WHILE (@n < 10000)
BEGIN
    INSERT INTO dbo.myRangeTable
            VALUES (@n, REPLICATE ('a', 10));
    SELECT @n = @n + 1;
END;

I can now use $PARTITION to determine how many rows are in each partition.

SELECT $PARTITION.myRangePF(c1) Partition_Number, count(*) as row_count
FROM dbo.myRangeTable
GROUP BY $PARTITION.myRangePF(c1);

Here are my results:

Partition_Number row_count
---------------- -----------
3                2500
1                2500
4                2499
2                2500

Let's suppose I now want to turn this query into a view, so I don't have to type all that messy stuff all the time.

CREATE VIEW dbo.PartitionSizes
AS
SELECT $PARTITION.myRangePF(c1) Partition_Number,
count(*) as row_count
FROM dbo.myRangeTable
GROUP BY $PARTITION.myRangePF(c1);

This works fine. I can select from the view and get the same results I got from the standalone query.

Now, for the bug. If you're going to try this, make sure you're using Query Analyzer or SSMS from which you can easily cancel a query. If you try to SELECT from this view from another database, the query freezes.

USE tempdb;
GO
SELECT * FROM testdb.dbo.PartitionSizes;

You'll have to cancel the query to get control back. It's very interesting, because sysprocesses doesn't show the query is being blocked, but it is not making any progress. The cpu, memusage and physical_io values do not change while the query is frozen. The only interesting thing I can find is that process is holding a database lock on a resource called [PLAN GUIDE] and it's holding a couple of Metadata [MD] locks. But the locks have all been granted and no one is waiting for anything.

My first thought was that you couldn't access the $PARTITION namespace from another database, which is still no reason the connection should freeze. But in fact you CAN access $PARTITION from another database. I could execute the underlying SELECT statement directly:

USE tempdb
GO
SELECT testdb.$PARTITION.myRangePF(c1)as Partition_Number,
count(*) as row_count
FROM testdb.dbo.myRangeTable
GROUP BY testdb.$PARTITION.myRangePF(c1);

The above works just fine. Accessing the SELECT using $PARTITION through a view in another database doesn't work, and a bug has been filed on it.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=303784

There actually is a workaround you can use if you want a simpler way to get the rowcounts for every partition in a table. You can use the dynamic management view sys.dm_db_partition_stats to get the same information, without having to use $PARTITION. Although this metadata view has lots of columns, I am only interested in two of them right now.

USE testdb
GO
CREATE VIEW dbo.PartitionSizes2 AS
SELECT Partition_Number, row_count
FROM sys.dm_db_partition_stats
WHERE object_id = object_id('myRangeTable');

Now you can select from this view from any database, and get the results you want.

USE tempdb;
GO
SELECT * FROM testdb.dbo.PartitionSizes2;

Have fun!

~Kalen

Kalen Delaney : partition functions

Geek City: My Big Fat Partitioning Query

Geek City: ALTERing a Partition Function

Did You Know? What's a $ Worth?