SQL Server Indexes - Initial slow performance after creation

Question

Using SQL Server 2005. This is something I've noticed while doing some performance analysis.

I have a large table with about 100 million rows. I'm comparing the performance of different indexes on the table, to see what the most optimal is for my test scenario which is doing about 10,000 inserts on that table, among other things on other tables. While my test is running, I'm capturing an SQL Profiler trace which I load in to an SQL table when the test has finished so I can analyse the stats.

The first test run after recreating a different set of indexes on the table is very noticeably slower than subsequent runs - typically about 10-15 times slower for the inserts on this table on the first run after the index creation.

Each time, I clear the data and execution plan cache before the test.

What I want to know, is the reason for this initial poorer performance with a newly created set of indexes? Is there a way I can monitor what is happening to cause this for the first run?

Answer 1

One possibility is that the default fill factor of zero is coming in to play.

This means that there's 'no room' in the index to accommodate your inserts. When you insert, a page split in the index is needed, which adds some empty space to store the new index information. As you carry out more inserts, more space is created in the index. After a while the rate of splitting will go down, because your inserts are hitting pages that are not fully filled, so splits are not needed. An insert requiring page splits is more expensive than one that doesn't.

You can set the fill factor when you create the index. Its a classic trade off between space used and performance of different operations.

I'm going go include a link to some Sybase ASE docs, 'cos they are nicely written and mostly applicable to SQL Server too.

Answer 2

Just to clarify:

1) You build an index on a table with 100m pre-existing rows.

2) You insert 10k rows into the table

3) You insert another 10k rows into the table

Step 3 is 10x faster than step 2?

What kind of index is the new index - not clustered, right? Because inserts on a clustered index will cause very different behavior. In addition, is there any significant difference in the profile of the 2 inserts, because depending on the clustered index, they will have different behavior. Typically, it should either have no clustered index or be clustered on an increasing key.