Does the order of columns in a PK index matter?

Question

I have a few very large tables with the same basic strucure. Each one has a RowNumber (bigint) and DataDate (date) column. Data is loaded using SQLBulkImport every night, and no "new" data is ever loaded - its a historical record (SQL Standard, not Enterprise, so no partitioning).

Because each bit of data needs to be tied back to other systems, and each RowNumber/DataDate combination is unique, that is my Primary Key.

I notice that due to the way I defined the PK in SSMS Table Designer, RowNumber is listed first and DataDate second.

I also notice that my fragmentation is always VERY high ~99%.

Now, because each DataDate only appears once, I would expect the indexer to just add to the pages each day, but I wonder if it actually is indexing based on RowNumber first, and hence having to shift everything else around?

---

Rownumber is not an identity column, it's an int generated by an external system (sadly). It resets at the start of each DataDate.

Example Data

RowNumber | DataDate | a | b | c..... 
   1      |2013-08-01| x | y | z 
   2      |2013-08-01| x | y | z 
...
   1      |2013-08-02| x | y | z 
   2      |2013-08-02| x | y | z 
...

Data is being loaded in RowNumber order, one DataDate per load.

Import process is bcp - I have tried loading to a temp table and then selecting in order from there (ORDER BY RowNumber, DataDate) but still comes out high fragmentation.

Answer 1

Does the order of columns in a PK index matter?

Yes it does.

By default, the primary key constraint is enforced in SQL Server by a unique clustered index. The clustered index defines the logical order of rows in the table. There may be a number of extra index pages added to represent the upper levels of the b-tree index, but the lowest (leaf) level of a clustered index is simply the logical order of the data itself.

To be clear about it, rows on a page are not necessarily physically stored in clustered index key order. There is a separate indirection structure within the page that stores a pointer to each row. This structure is sorted by the clustered index keys. Also, each page has a pointer to the previous and next page at the same level in clustered index key order.

With a clustered primary key of (RowNumber, DataDate), the rows are logically sorted first by RowNumber and then by DataDate - so all rows where RowNumber = 1 are logically grouped together, then rows where RowNumber = 2 and so on.

When you add new data (with RowNumbers from 1 to n) the new rows logically belong inside the existing pages, so SQL Server will likely have to do a lot of work splitting pages to make room. All this activity generates a lot of extra work (including logging the changes) for no gain.

Split pages also start off about 50% empty, so excessive splitting can result in low page density (fewer rows than optimal per page) as well. Not only is this bad news for reading from disk (lower density = more pages to read), the lower-density pages also take up more room in memory when cached.

Changing the clustered index to (DataDate, RowNumber) means that new data (with, presumably, higher DataDates than currently stored) is appended to the logical end of the clustered index on fresh pages. This will remove the unnecessary overheads of splitting pages and result in faster load times. Less fragmented data also means that read-ahead activity (reading pages from disk just before they are needed for an in-progress query) can be more efficient.

If nothing else, your queries are much more likely to search on DataDate than RowNumber. A clustered index on (DataDate, RowNumber) supports index seeks on DataDate (and then RowNumber). The existing arrangement only supports seeks on RowNumber (and only then, perhaps, on DataDate). You might well be able to drop the existing nonclustered index on DataDate once the primary key is changed. The clustered index will be wider than the nonclustered index it replaces, so you should test to ensure that performance remains acceptable.

When importing new data with bcp, you may get higher performance if the data within the import file is sorted by the clustered index keys (ideally (DataDate, RowNumber)) and you specify the bcp option:

-h "ORDER(DataDate,RowNumber), TABLOCK"

For best data loading performance, you might try to achieve minimally-logged inserts. For more information, see:

• SQL Server Index Basics by Robert Sheldon
• Effective Clustered Indexes by Michelle Ufford
• Bulk Inserts Via TSQL by Robert Sheldon
• Minimal Logging with INSERT…SELECT into Empty Clustered Tables by me
• Minimal Logging with INSERT…SELECT and Fast Load Context by me

Answer 2

Yes, the order is critical. I highly doubt you ever query by RowNumber (eg WHERE RowNumber=1). Overwhelmingly time series are queried by date (WHERE DataDate BEWEEN @start AND @end) and such queries would require a clustered organization by DataDate.

Fragmentation in general is a red-herring. Reducing fragmentation should not be your goal here, but having a proper organization for your queries should. Getting reduced fragmentation in addition is a good think to have, but is not a goal on its own. If you have a properly organized data model that matches your workload (your queries are properly covered) and you have measurements that show fragmentation as impacting performance then we can talk about it.