Insert Performance Degrades Over Time

Question

Insert Performance Degrades Over Time

Leifton

SSC Veteran

Points: 298
More actions
November 21, 2008 at 8:01 am

#383436

I have a program that performs the following:
1. Collects data from customer site.
2. Writes data to database every 5 minutes.
2a. Opens Connection to DB.
2b. Loops over insert command for each data set collected.
The number of data sets can be up to 1000 each round.
2c. Close Connection.
The table being inserted into has the following structure:
dbo.History
( ts ,
id ,
value )
The table has a clustered index on ts and id for lookups. Initially inserts perform fine, but over time (a few days) the inserts start taking longer than the collection cycle. Thus starting a vicious cycle.
What can be done to help with performance over time? Is the index causing problems? The collection cycle is fixed and cannot be changed. Usually we see around 16 million rows a day depending on site configuration.
Any Ideas?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Is the clustered index in the order the data is being inserted?

If it is not, your inserts will continuously fragment your index because they will need to put data in the middle of the index. As your table gets larger, it will take longer and longer to find the place in the middle of the index in which to put the data and your page splits to make room will end up putting sequential pages really far apart physically making the read heads on the drives move a lot.

For logging tables, you should usually use a clustered index that puts data at the end of the table and create a non-clustered index for your query. For the non-clustered index, use a fill factor that will keep fragmentation to an acceptable level for the length of time you need to support between rebuilding the index.

Leifton SSC Veteran Points: 298 More actions · Answer 2

Thanks for the reply.

I will check to see if the data is in the order of the index.

While I check, would the manner of which the insert is taking place have an impact? Would it help to be in a transaction, besides being autonomous or using the BULK INSERT facility?