SQL Server clustered index, index balancing and insert performance using NewID

Question

I have a large (6db) trace table. It has a clustered key (DateTime) which is created through GETDATE().

The connection pool for connections to this database/table rises as high as 50 on average across a cluster of 10 computers, so on average we have at ~500 concurrent connections attempting to insert.

The database fits in memory and hardly any IO is seen at all.

I am trying to figure out whether under sustained INSERT load the clustered index gets to a point where it rebalances the tree, and whether this will cause a slowdown in the number of inserts that the system can sustain.

There is some question in my mind as to whether the rebalancing an index is something SQL Server does on a clustered index (and even on a non-clustered index).

Questions-

1. Are there any reasons for periodic/cyclic slow-down of insert performance?
2. Do rebalance operations automatically trigger on clustered indexes?
3. Do rebalance operations automatically trigger on non-clustered indexes?

Other info

• SQL Server 2008
• Really BIG server - 256Gb, 40 cores, 40mbit LAN...

Answer 1

Are there any reasons for periodic/cyclic slow-down of insert performance?

Yes. check point events. With a write intensive workload, big RAM server, as you describe, a large number of 'dirty' pages accumulate in memory. At the predetermined checkpoint interval all these dirty pages get written to disk, causing a spike of IO requests. This in turn slows down the log commit writes, which manifests as the increase in INSERT response time you observe periodically. QED. This is, of course, just a guess, in lack of a proper investigation. For a more certain response, I recommend you read How to analyse SQL Server performance and apply the techniques described there to identify the problem.

If the problem is indeed caused by checkpoint, then SQL Server 2012 comes with Indirect Checkpoints:

Indirect checkpoints, new in SQL Server 2012, provide a configurable database-level alternative to automatic checkpoints. ... Indirect checkpoints reduce checkpoint-related I/O spiking by continually writing dirty pages to disk in the background.

For a more detailed discussion about chekcpoint impact on performance read SQL Q&A: Fine Tuning for Optimal Performance:

In Search of Spikes
Q. I’m troubleshooting an issue where we see periodic I/O spikes from one of our SQL Servers. I’ve narrowed it down to checkpoints using PerfMon, but I can’t tell which database is the major culprit. How can I drill in further?

Pre-SQL Server 2012 you have the option to reduce the recovery interval value. This will increase the frequency of checkpoints, but will reduce the number of dirty pages each checkpoint has to write. Spreading out the data IO helps (buy more spindles). Separating the log IO to it's own path (own spindle) does not help the checkpoint, but isolates the log commits from the effects and thus keep the INSERT responsive. SSDs work miracles.

I would advice against any structural changes. In my opinion you already have the best clustered index for time series. Any structural change would have to be backed by root-cause- performance analysis pointing to the current structure as a problem.

Answer 2

SQL Server does not "rebalance the tree" as a periodic event. I have last heard this term in the context of Oracle. All that SQL Server does it increase the tree height when necessary. This is an event that happens only a few times in the entire existence of a B-tree.

In a DML heavy workload there can be many small tree adjustments called page splits. These are indeed detrimental to CPU and IO usage and they can cause fragmentation. If you are inserting in ascending date order this problem does not occur because tree "appends" are a special case that SQL Server optimizes for. In any case a page split only affect a handful of pages.

No periodically occurring tree operations.

Clustered indexes have (almost) the same structure as non-clustered indexes.

All the usual SQL Server B-tree index advice applies: Chose the key wisely (seems like you have a good one based on ascending datetime values) and have a strategy for fragmentation and for reclaiming space in case of deletes.

Answer 3

There is a situation in your current setup that would/could cause some slowdown related to an auto-incrementing key (IDENTITY, GETDATE(), NEWSEQUENTIALID()): under high-concurrency INSERT operations, there can be contention related to placing rows on the same page. This is called a "hotspot" and is one of the few drawbacks to auto-incrementing values as they, by nature, are going to be right next to each other.

I found conflicting info as to whether or not the "hotspot" issue was still relevant:

• Still relevant: Link
• Obsolete: http://www.sqlskills.com/blogs/kimberly/the-clustered-index-debate-continues/
• There is even another question here on DBA.SE that asks about this hotspot issue in relation to the Clustered Index being based on an ever-increasing value: Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?

There are some interesting things to note regarding those three links:

1. Some of the answers in that DBA.SE question mention the two other links above. It is pointed out by @gbn that the article showing that the hotspot issue still exists "uses a non-unique clustered index on TranTime. This requires a uniquifier to be added. Which means the index in not strictly monotonically increasing (and too wide)."
2. Technically speaking, the uniquifier value (and hence space taken up by that hidden field) only exists on rows that are not unique. Hence it is possible to add rows, one at a time, in a single-threaded manner and it would be unique values that would be ever-increasing and there would be no uniqifier value.
3. But, that test simulated 400 concurrent connections running the test proc 200 times (per each connection, I assume) so it is very likely that several of those INSERT operations happened at the same millisecond and received the same value from GETDATE().
4. Ergo, while it might be appropriate to rule out that particular test as invalid with respect to "Do hotspots occur when using a unique, ever-increasing value as the clustered index?", that test might be highly relevant here. The description of the index in this question is that it has "a clustered key (DateTime) which is created through GETDATE()". It seems safe to assume that the index in this question is not unique (especially if it is just that one DATETIME field). And he tested 400 concurrent connections while this question states that there are approximately 500 concurrent connections? That sounds like a very similar setup. So it makes sense to run the same "SQL Server Perf Stats" script to see if you are also seeing similar LATCH contention.

Another thing to consider is that while index maintenance (REBUILD / REORGANIZE) is not done automatically, the updating of statistics is done automatically (given a sliding-scale of % of rows changed). This is the default setting for databases, unless you set "Auto Update Statistics" to "false". There is a related option that is "false" by default, and that is "Auto Update Statistics Asynchronously" that will not cause any blocking during that auto-update operation. The blocking caused by the auto-update stats operation occurs during plan generation for any plans that are needing information on the particular statistic being updated at that moment. The "Auto Update Statistics Asynchronously" option allows the Query Optimizer to use statistics that are known to be stale and are being updated; once the stats are updated they will be used.

Another thing that can cause periodic slowdown of INSERTs (as well as some UPDATEs) is auto-growth operations of the data and log files. Obviously the tran log will grow even with DELETE operations. But INSERT operations, and UPDATE operations where the new row is larger than the previous version of that row, will potentially need new pages allocated if there is no room left on the appropriate page. If there is no more space available to allocate the page, SQL Server will try to grow the data file (unless this has been disabled). While the data (or log) file is being grown, operations against that file are blocked. This is why it is important to properly size the data files so that there is room for the tables in them to grow without needing auto-grow, or at least not often.

And for the sake of completeness, there is the CHECKPOINT behavior as pointed out by @Remus in another answer to this question.

---

It should be noted that Page Splits are not a function of DML operations in general or under heavy load; they are a function of:

1. (the order in which data is inserted, OR
2. an increase in row size for data that is updated), AND
3. whether or not there is room on the appropriate page for either of those events

Single-threaded INSERT operations of an auto-incrementing key should never cause a page split. Multi-threaded INSERT operations of an auto-incrementing key could (I believe) be done out-of-order (and hence potentially causing a page split) in a high-volume, concurrent INSERT scenario depending on if the Scheduler (the SQL OS multithreading) would do something like assigning the value from the GETDATE() but then placing that thread on hold while another gets inserted, only to come back to this one for the actual insert. I highlighted the "if" since I have not proven that this happens. And UPDATE operations, at any volume, shouldn't cause page splits if the row size is not increasing.