CODEWITHSUNDEEP
sql-serverentity-frameworkbulkinsert
Daniel Hilgarth
Daniel Hilgarth

Reputation: 174369

Index gets out of sync on bulk insert

I have a strange problem with my SQL Server database. I am writing bulk data (about 90,000 rows) using SqlBulkCopy.WriteToServer and I am also writing about 30,000 rows in batches of 1,000 using EF's AddRange.

This causes the indexes on these tables to go out of sync and queries take many factors longer than usually (timeout after 10 minutes instead of a result after a few seconds).

After I manually rebuild the indexes, the queries are fast again until another of these imports is happening.

My understanding of bulk loading is that it should also update the index. My question is: Is there a well-known reason for this behavior? If not, how can I go about trouble shooting this?

Upvotes: 1

Views: 1447

Answers (2)

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174369

Based on the comments and answers here, I tried to figure out if I can change that 20% threshold somehow.
And indeed, there is a way to do this, using trace flag 2371

You can enable it like this:

DBCC TRACEON(2371, -1)

I will now wait a few weeks to be sure that this fixed the problem, but I have good hopes about it.

Upvotes: 0

Gabor
Gabor

Reputation: 1499

We have got exactly the same issue some years ago. And as dfundako suggested, the answer is the outdated statistics. SQLServer by defaults updates the statistics if a certain percent of records was changed. This is a problem if your table has a huge number of records, so 90000 added records would not reach the required percentage of number of changed rows. So if you want to be sure, after inserting you have either reindex you table (as you did) or update the statistics of your table

update statistics <your table>

Upvotes: 3

Related Questions