There is a situation in your current setup that would/could cause some slowdown related to an auto-incrementing key (
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:
There are some interesting things to note regarding those three links:
- 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)."
- 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.
- 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
- 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:
- (the order in which data is inserted, OR
- an increase in row size for data that is updated), AND
- 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.