8

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.
11
  • 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.
    – Hannah Vernon
    Commented Jan 27, 2015 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
    Commented Jan 27, 2015 at 18:49
  • 2
    @MaxVernon but you could risk getting into latch contention blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/…
    – Tom V
    Commented Apr 24, 2015 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. Commented May 6, 2015 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
    Commented May 13, 2015 at 13:49

1 Answer 1

4

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.

3
  • 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. Commented Jul 26, 2015 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
    Commented Jul 27, 2015 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!) Commented Jul 27, 2015 at 16:46

Your Answer

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

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