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.

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.

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;GOSELECT * 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 tempdbGOSELECT testdb.$PARTITION.myRangePF(c1)as Partition_Number, count(*) as row_countFROM testdb.dbo.myRangeTableGROUP 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.

Comment Notification

Comments

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.