Insert slowing down

Question

Insert slowing down

Viewing 12 posts - 16 through 26 (of 26 total)

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

You said you checked the script durations. Did you do that with sql profiler or was that taken from some application log? Is the log set to autogrow? If so, by how much?

And then again, I might be wrong ...
David Webb

lbdw Old Hand Points: 390 More actions · Answer 2

I used SQL Profiler and the log is allowed for autogrowth by 10% limited to 10Gb, last time I checked.

David Webb-CDS SSCoach Points: 17398 More actions · Answer 3

Is there a trigger on the table? If so, can you post the code for the trigger and any table it references?

And then again, I might be wrong ...
David Webb

lbdw Old Hand Points: 390 More actions · Answer 4

Nope no triggers or any additional processing on the tables.

Stefan_G SSCertifiable Points: 6609 More actions · Answer 5

lbdw (8/6/2010)
Well, let me list some more stats:
average IO write ops 4~47 / sec, avg 188434 bytes/ sec (running raid thus using averages) , half way through it starts to peak 100+ sustained and write ops at 600+ /sec then drops again, and so on and so on....

Exactly what does your disk configuration look like ? How many disks ? raid levels, where are the ldf and mdf files ?

How is your raid implemented ? Hardware controller, software ?

What you are describing sounds like if you have disk heads thrashing around violently. This could for example happen if you have the log and data on the same physical disk, especially if you use raid 5.

Again, please tell us how %disk time varies for each physical disk during the import. It is very important to know if it is the log disk or the data disk that is overloaded.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

Quick question ... is this process loading an empty table? meaning, is the target table empty at the beggining of the load?

Described behavior is consistent with the full loading of a staging table that has a clustered PK on it when loaded data is not ordered by PK.

In this scenario load process will get get slower and slower as load process goes on.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

lbdw Old Hand Points: 390 More actions · Answer 7

Ok, we are using RAID1 6x500Gb into 3x500Gb drives (C,D,E)

TempDB on C, mdf on D and ldf on E (file growth set to 500mb)

External SAN as backup, just for interest sake.

%Disk Time as follow:

C = avg 8%, but fluctuates between 0~29%

D = avg 50%, but fluctuates between 50~700% (ouch)

F = avg <3%

The tables being loaded are active tables, meaning it is not empty, has an avg

of 3 index 1 which is PK ASC on 1 unique column.

Stefan_G SSCertifiable Points: 6609 More actions · Answer 8

Is it possible that any of your tables or indexes are heavily fragmented ?

How large are the tables you are inserting to ?

lbdw Old Hand Points: 390 More actions · Answer 9

Well I periodically run maintenace scripts on it (like re-indexing etc)

My largest involved table is:

2.440Gb in dataspace, 1.213Gb in index space, 19 307 075 total rows

Second largest involved table:

881.711 MB data space, 1,140.789 MB index space, 10 370 100 total rows

Stefan_G SSCertifiable Points: 6609 More actions · Answer 10

OK, I give up.

I would have liked to analyze this on site, but it is just too inefficient and frustrating to try to diagnose this remotely.

One final suggestion is to let the program insert the data into staging tables without any indexes, and then use SQL to move the data from the staging tables to the final destination.

Another way to speed it up is to use SqlBulkCopy for the insert. You could either use SqlBulkCopy to insert data into the final tables directly or into the staging tables.

You need to experiment to find out which way is fastest.

Good luck

lbdw Old Hand Points: 390 More actions · Answer 11

Ok, thanks to everyone’s help!

I’ll just continue trying to speed it up

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 12

This sounds to me like what PaulB is describing. If there is a unique constraint on the table without an index, each insert has to read the table sequentially to determine if the key candidate is really unique. It gets slower and slower as the table fills up.

Converting oxygen into carbon dioxide, since 1955.