THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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.

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

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

Comments

 

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

AS

SELECT

  testdb.$PARTITION.myRangePF(c1) Partition_Number,

  count(*) as row_count

FROM

  dbo.myRangeTable

GROUP BY

  testdb.$PARTITION.myRangePF(c1);

GO

Cheers,

AMB

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:

Interesting.  

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

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

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