patternsqlMinor
SQL Server clustered index, index balancing and insert performance using NewID
Viewed 0 times
clusteredinsertsqlandperformancebalancingusingserverindexnewid
Problem
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-
Other info
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-
- Are there any reasons for periodic/cyclic slow-down of insert performance?
- Do rebalance operations automatically trigger on clustered indexes?
- Do rebalance operations automatically trigger on non-clustered indexes?
Other info
- SQL Server 2008
- Really BIG server - 256Gb, 40 cores, 40mbit LAN...
Solution
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.
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.
Context
StackExchange Database Administrators Q#81631, answer score: 8
Revisions (0)
No revisions yet.