Sample Header Ad - 728x90

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

7 votes
3 answers
2290 views
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...
Asked by Ravenor (205 rep)
Oct 31, 2014, 09:33 PM
Last activity: Jan 30, 2023, 12:04 PM