THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Did You Know? What's a $ Worth?


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.

USE testdb;
AS RANGE LEFT FOR VALUES (2500, 5000, 7500);

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


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)
SELECT @n = 1;
WHILE (@n < 10000)
    INSERT INTO dbo.myRangeTable
            VALUES (@n, REPLICATE ('a', 10));
    SELECT @n = @n + 1;

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

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
  SELECT $PARTITION.myRangePF(c1) Partition_Number,
          count(*) as row_count
  FROM dbo.myRangeTable

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;

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
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.

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
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;

SELECT * FROM testdb.dbo.PartitionSizes2;

Have fun!


Published Saturday, October 13, 2007 5:29 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Alejandro Mesa said:

Hi Kalen,

Very interesting indeed. I tested it and noticed an interesting lock that comes and goes during the time the "select" is being executing.

ObjId:  object id of the view

Type: TAB

Resource: [COMPILE]

Mode: X

Something is happening that SS is constantly adquiring an exclusive lock (X) on the view and the resource is [COMPILE]. You have to execute sp_lock multiple times to be able to see it. I am confused here, because [COMPILE] should be for compilation of a stored procedure, right?. Anyway, I decided to change the view definition and qualify the function with the database name, in case something wierd could be happening while referencing the view from another database. Guess what, now the "select" statement behaves as expected.

CREATE VIEW dbo.PartitionSizes



  testdb.$PARTITION.myRangePF(c1) Partition_Number,

  count(*) as row_count








October 14, 2007 4:17 PM

Kalen Delaney said:

Finally, a real geeky post. I received two questions in as many weeks about what really happens to existing

August 16, 2009 4:31 PM

Kalen Delaney said:

Finally, a real geeky post. I received two questions in as many weeks about what really happens to existing

August 16, 2009 4:33 PM

mbourgon said:


On our server (2008R2 SP1), using the same syntax for our Database/PF but simply trying to get it back for particular day (using '20120601' instead of C1), we got back:

Msg 164, Level 15, State 1, Line 1

Each GROUP BY expression must contain at least one column that is not an outer reference.

If we used your query, narrowing down via a subquery selecting just from

the partition we care about, we got the partition_number.

June 15, 2012 3:41 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement