Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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);
share|improve this question
    
If you want to partition by date (which usually makes the most sense, since it makes old partitions easy to identify and manage), then it should be the leading key column IMHO. –  Aaron Bertrand Aug 15 at 14:46
    
Hi Aaron, thanks for the reply. Unfortunately I cannot use the date column as the leading key column. What would be the impact of adding the column to the existing Primary Key? –  DBAFromTheCold Aug 15 at 14:53
2  
What does "I cannot" mean? What do you mean? The problem with using ID as the range as it makes it nearly impossible to partition by date - and this is usually how you want to manage old data, and deal with active queries on current data. Do you want to archive the lowest 50,000 IDs in the table, or the oldest month? If you partition by ID then as your volume increases you end up having more partitions represent the same amount of time. Do you want your queries for the current month to access one partition, or several? What evidence do you have that you even should partition? –  Aaron Bertrand Aug 15 at 14:53
    
I want to partition the table so that we can switch out old partitions to another table based on the date (as an archiving strategy). However instead of changing the Primary Key's leading column, I was wondering what the impact of adding the date as a secondary column would be? –  DBAFromTheCold Aug 15 at 14:56
1  
Right, and of course, if you partition on NEWID() then you will spray new rows across every single partition. That does not make archiving or querying by date very effective at all. –  Aaron Bertrand Aug 15 at 15:15

1 Answer 1

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.

share|improve this answer
1  
Aaron, thank you very much for the detailed answer! I've gone through the code and the design I have been testing is the PKClustered table but created on the partition scheme with no non-clustered index (PRIMARY KEY CLUSTERED (ID,dt) ON DateRangeScheme(dt)). This does seem to partition the data by date but from your examples I can see it is not a good design because of the performance impacts. Again, thank you very much, Andrew. –  DBAFromTheCold Aug 15 at 17:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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