SQL Server – Partitioning Query for Performance

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:

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:

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

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.

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

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:

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:

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

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.

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:

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:

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

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.