37

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.

0

2 Answers 2

54

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:

2
  • 4
    An excellent answer - I now know WHAT I should do AND why. I had thought so, but not KNOWN so! Thank you.
    – BlueChippy
    Aug 21, 2013 at 9:05
  • Took a LOOOOONG while to get the DB into my local SQL Server for testing: Before altering the index load took 45 mins...after, it took just 5!!!
    – BlueChippy
    Aug 22, 2013 at 7:39
15

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.

2
  • I also have a non-clustered index(es) on DataDate, which as you say is often WHERE clause in queries.
    – BlueChippy
    Aug 21, 2013 at 8:21
  • 1
    If ORDER of the columns is critical, would the impact of the incorrecrt order see my I/O increase? My thought is that it's ordering by RowNumber and therefore having to doa lot of work on the indexes every time, whereas it should be based on DataDate?
    – BlueChippy
    Aug 21, 2013 at 8:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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