3

I have a couple of questions about the phyiscal layout of tables when they are partitioned. I've been researching this but am still a little unsure.

Say I have an existing table:-

    CREATE TABLE dbo.[ExampleTable]
     (ID INT IDENTITY(1,1),
      Col1 SYSNAME,
      Col2 SYSNAME,
      CreatedDATE DATE) ON [DATA];

     ALTER TABLE dbo.[ExampleData] ADD CONSTRAINT [PK_ExampleTable] PRIMARY KEY CLUSTERED
     ( [ID] ASC )
     GO

I want to partition this table on the CreatedDate column (all partitions in the same filegroup for this example) but I cannot have the column as a Primary Key on its own. So I add the CreatedDate column to the Primary Key:-

    ALTER TABLE dbo.[ExampleTable] DROP CONSTRAINT PRIMARY KEY

    ALTER TABLE dbo.[ExampleTable] ADD CONSTRAINT [PK_ExampleTable] PRIMARY KEY CLUSTERED
    ( [ID] ASC, [CreatedDate] ASC ) ON PartitionScheme(CreatedDate)
    GO

My question is how will data will be sorted? Will the data be physically split into partitions by the CreatedDate column and then ordered by the ID column? Or are the partitions logical and the data remains ordered by the ID column?

Also, what would happen if the ID column was a GUID? Would the data be in partitions then then horribly fragmented within those partitions?

Any advice would be most appreciated, thank you.

Andrew

EDIT:- Adding in the Partition Scheme and function:-

    DECLARE @CurrentDate DATETIME;

    CREATE PARTITION FUNCTION PF_Example (DATETIME)
    AS RANGE RIGHT 
    FOR VALUES (@CurrentDate+7,@CurrentDate+6,@CurrentDate+5,@CurrentDate+4,
                @CurrentDate+3,@CurrentDate+2,@CurrentDate+1,@CurrentDate,
                @CurrentDate-1,@CurrentDate-2,@CurrentDate-3,@CurrentDate-4,
                @CurrentDate-5,@CurrentDate-6,@CurrentDate-7,@CurrentDate-8);

   CREATE PARTITION SCHEME PS_Example
   AS PARTITION PF_Example
   ALL TO (Data);
7

Ok, so here is a quick example demonstrating why - in the case where most of your operations (reporting queries, archive operations, partition switches etc.) will identify ranges of rows by date - you're better off clustering on the partitioning column. Let's have a simple date-based partition scheme and function:

CREATE PARTITION FUNCTION DateRange (DATE)
AS RANGE RIGHT FOR VALUES ('20150101');
GO

CREATE PARTITION SCHEME DateRangeScheme
AS PARTITION DateRange ALL TO ([PRIMARY]);
GO

Then two tables - one with a clustered PK on ID, Date and a non-clustered index on Date, and another with a non-clustered PK on ID, Date and a clustered index on Date.

CREATE TABLE dbo.PKClustered
(
  ID INT, 
  dt DATE, 
  filler CHAR(4000)
   CONSTRAINT df_filler_c DEFAULT '' NOT NULL, 
  CONSTRAINT pk_clust PRIMARY KEY CLUSTERED (ID,dt)
);
CREATE INDEX dt ON dbo.PKClustered(dt) ON DateRangeScheme(dt);

CREATE TABLE dbo.PKNonClustered
(
  ID INT, 
  dt DATE, 
  filler CHAR(4000)
   CONSTRAINT df_filler_nc DEFAULT '' NOT NULL, 
  CONSTRAINT pk_nonclust PRIMARY KEY NONCLUSTERED (ID,dt)
);
CREATE CLUSTERED INDEX dt ON dbo.PKNonClustered(dt) ON DateRangeScheme(dt);

Now fill them with some data:

INSERT dbo.PKClustered(ID, dt) SELECT TOP (100) Number, '20141231'
  FROM master.dbo.spt_values WHERE [type] = N'P' ORDER BY Number;

INSERT dbo.PKClustered(ID, dt) SELECT TOP (50) Number, '20150101'
  FROM master.dbo.spt_values WHERE [type] = N'P' ORDER BY Number DESC;

INSERT dbo.PKNonClustered(ID, dt) SELECT ID, dt FROM dbo.PKClustered;

So we should have 100 rows in partition 1, and 50 rows in partition 2, right? sys.partitions confirms:

SELECT [table] = o.name, [index] = i.name, 
  p.partition_number, p.[rows]
FROM sys.tables AS o
INNER JOIN sys.indexes AS i
ON o.[object_id] = i.[object_id]
INNER JOIN sys.partitions AS p
ON i.[object_id] = p.[object_id]
AND i.index_id = p.index_id
WHERE o.name LIKE N'PK%Clustered'
ORDER BY o.name, i.name;

Results:

enter image description here

Notice that in both cases the data in the PK is all stored in a single partition. How does that affect queries? Well, consider these four, which are probably typical (aside from the SELECT *, used only for brevity):

SELECT * FROM dbo.PKClustered WHERE dt >= '20150101';
SELECT * FROM dbo.PKNonClustered WHERE dt >= '20150101';

DELETE dbo.PKClustered WHERE dt >= '20140101' AND dt < '20150101';
DELETE dbo.PKNonClustered WHERE dt >= '20140101' AND dt < '20150101';

Here are some results from SQL Sentry Plan Explorer:*

Estimated costs and actual runtime metrics:

enter image description here

The SELECT * against the non-clustered PK performed an efficient clustered index seek, accessing only a single partition:

enter image description here

enter image description here

When the PK is clustered, it decides instead to perform a clustered index scan, which means it can't eliminate partitions, leading to more reads and therefore a higher I/O cost. Interesting to note, also, that the scan is not ordered.

enter image description here

enter image description here

Similar things happen with the delete. The most expensive part of the delete operation in both cases is the clustered index delete; having the benefit of partition elimination makes the non-clustered PK much more desirable for supporting this operation (even though ultimately the reads required and up being about the same).

enter image description here

enter image description here

With the clustered PK, the source rows are found with a seek (which you might expect to be more efficient), but again the most work is performed by the subsequent delete, so at least at this size it doesn't have much impact at all:

enter image description here

enter image description here

Now, at much higher volumes, that leading scan may wind up tipping the scale in the other direction, so you're going to have to test.

Of course at this low end this has a negative impact on single-row queries where you identify by ID, since you will typically identify the row by an index seek and then have to do a lookup, vs. a single clustered index seek. Let's consider these two queries (again, regarding SELECT *, do as I say, not as I do):

SELECT * FROM dbo.PKClustered WHERE ID = 2045;
SELECT * FROM dbo.PKNonClustered WHERE ID = 2045;

Results from Plan Explorer:

enter image description here

The first one is simple, it just needs a clustered index seek (and therefore no lookups):

enter image description here

But as mentioned, the second one decides on a non-partitioned seek against the PK, but a partitioned key lookup. In this case that ends up being more expensive, but might not always, and might not always be the optimizer's choice, either.

enter image description here

The same kind of thing may happen with certain join queries, depending on how many rows and how the join is constructed.

And again, the optimizer's choices here are often going to be volume-dependent. So, in the end: it depends. My choice with the information you've provided would be to cluster on the partitioning key and use a non-clustered PK. And I would strongly avoid using a GUID for this ID in either case - while that distribution might be good for inserts if you're trying to insert 8 billion rows a second, it's not going to help for anything else you're doing.

Another option is to use a single, combined PK on Date first, then ID:

CREATE TABLE dbo.PKCombined
(
  ID INT, 
  dt DATE, 
  filler CHAR(4000)
   CONSTRAINT df_filler_comb DEFAULT '' NOT NULL, 
  CONSTRAINT pk_comb PRIMARY KEY CLUSTERED (dt,ID) ON DateRangeScheme(dt)
);

This obviously results in fewer rows being stored on fewer pages (no non-clustered index to maintain, for example):

SELECT [table] = o.name, 
  [rows] = SUM(row_count), 
  [pages] = SUM(used_page_count),
  [size_in_kb] = 8.192*SUM(used_page_count)
FROM sys.tables AS o
INNER JOIN sys.indexes AS i
ON o.[object_id] = i.[object_id]
INNER JOIN sys.dm_db_partition_stats AS p
ON i.[object_id] = p.[object_id]
AND i.index_id = p.index_id
WHERE o.name LIKE N'PK%'
GROUP BY o.name
ORDER BY o.name;

Results:

enter image description here

But how does it affect these other queries? The SELECT * is identical to the SELECT * on the non-clustered PK version; a simple clustered index seek. The DELETE, however, is a much simpler plan:

enter image description here

The single-row seek, however, ends up being much more expensive:

enter image description here

You can probably combat that with a non-clustered covering index on ID, which would convert the scan to a seek (with a lookup if the index is non-covering), but would still not benefit from partition elimination.

* Disclaimer: I work for SQL Sentry.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.