February 24, 2009 at 3:09 pm
I have an application that inserts roughly 400 million rows to the database. The developer has horizontally partitioned the table into 4 separate tables, and inserts roughly 100 million rows to each of the tables via threads from the front end.
The insertion process is as follows... insert data, create index.. join with main thread.
The problem I have is that the nonclustered index creation steps blocks between the threads.. although the index creation is on different tables.
Can anyone explain how this would happen ? Could this be resolved if the four tables were created on different file groups ?
TIA,
RutgersDBA
"Who then will explain the explanation? Who then will explain the explanation?" Lord Byron
February 25, 2009 at 12:30 am
What's the blocking resource?
Can you show some of the code?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 1, 2009 at 11:31 pm
For posterity sake... this was solved by reducing parallelism for sql server on the server. Worker threads in the index generation process were suspending and locking each other.
"Who then will explain the explanation? Who then will explain the explanation?" Lord Byron
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply