I have a big database (200GB+) that contains some log info. And I want to speed up SELECT queries and stored procedures. I have a table with a GeneratedOnUtc datetime column, and have a non-clustered index on it.

I'm thinking to change it to a clustered index.

Reasons for:

  • Big amount of data (~40 millions rows)

  • Column is used in multiple Where clauses (between, >, <)

  • Column is used in ROW_NUMBER() OVER (ORDER BY d.GeneratedOnUtc asc) AS Row queries

Reason against:

  • Large amount of inserts (~60k per day) may lead to frequent B-tree rebuilds.
  • 8
    If the inserted dates are monotonically increasing, you won't have "bad" page splits. You could ensure this by using the time on the server as the log date, at insert time. – Max Vernon Jan 27 '15 at 12:48
  • 1
    Does the table already have a clustered index that you would be replacing, or is it currently a heap? And do you know roughly what percentage of queries against this table (including joins against it) would include the GeneratedOnUtc field? – DeadZone Jan 27 '15 at 18:49
  • 2
    @MaxVernon but you could risk getting into latch contention blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/… – Tom V - try topanswers.xyz Apr 24 '15 at 13:15
  • 2
    A nice article @TomV I never thought about, thanks for sharing :) still, with the purpose of speeding SELECT queries and stored procedures in mind I'd go with @MaxVernon's answer. If the 60k inserts per day are distributed over several hours, the peak of inserts/sec shouldn't be a worry, but here I don't have the data. Partitioning the table could be of interest too. – JoseTeixeira May 6 '15 at 17:33
  • 1
    A clustered index on timestamp is good to maximize insert performance. I wouldn't worry about Page latch contention unless the insert rate is sustained at over 20K/sec+ or so. Consider select query performance with your index strategy. – Dan Guzman May 13 '15 at 13:49

Changing your table from a heap to having a clustered index should significantly improve your performance on both queries and perhaps even on inserts. Generally speaking, your clustered index should be narrow, unique, and ever increasing. Using a datetime that you can't guarantee to be unique is not ideal because it's 8 bytes and, since it isn't unique, sql will add a four byte uniquifier to non unique rows. You may be better off using an identity column with an int as your clustered index and, since that's what the "row_number" queries are really after anyway (an ever increasing unique number) that might be a great way to go especially if you have a number of nonclustered indexes already (because the clustered is used as a row pointer by the nonclustered so it adds size to them). The 4 billion available int values leave you decades of growth.

I suggest you make a testing copy of your database and then test it with a clustered index on your datetime value and contrast that with a test using a clustered index on a new identity column (and a nonclustered index on your datetime). See which one does better with your query load. Both scenarios will outperform a heap.

  • 1
    The 4 bytes would be added only for rows that have identical timestamps. An 8-byte datetime is not changing, could be ever-increasing (if inserted in order, as @MaxVernon's comment) and be considered narrow. I'm not convinced that an identity is a better solution for the specific case. – ypercubeᵀᴹ Jul 26 '15 at 23:25
  • 1
    Thanks @ypercube, I always thought that the uniquifier was added to every row, but some digging confirmed that you are right. I updated my answer to reflect that. It's always a good day when you learn new things. You may be right about the identity being the wrong solution here. I believe that the right solution depends on the number and size of the non clustered indexes as well as the queries that are reading data. That's why I recommended that Alex try both scenarios. – ubergeek Jul 27 '15 at 16:11
  • Yeah, I agree with your suggestion to test with their data volume and indexes. 8 bytes vs 4 can be a huge amount if there are a lot of secondary indexes. I only meant that if most of the queries have a range condition with the datetime, it may be a suitable choice for the CI (but test, test, test, first!) – ypercubeᵀᴹ Jul 27 '15 at 16:46

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.