Speeding up insert in SQL Server

Question

I have a stored procedure which inserts some records in a few tables. In couple of tables at least , the inserted records number 10000+. Not more than 15K though. Takes 3-5 mins for this procedure to complete. Also same procedure can be called from multiple user sessions which results in some sessions waiting for 20 mins to get a response. Is there something I can do to reduce this time?

The database recovery mode is FULL(cannot be changed) and hence per my understanding sqlBulkCopy wont help here. Would love to hear your thoughts on this.

The table contains 15 columns. Among which 5 columns are foreign key to this table. There is no identity column but clustered index in combination of all the 5 foreign key columns. I have couple of index on other key columns . The rest of columns are decimal and varchar(50). I do have one varchar(max) column though.

Trying to get query or query same for your reference . A screen grab of most expensive operation(54) in the query:

If the clustered index of the table is on combination of columns instead of an identity column , will the inserts be impacted?

Basically the query is 'insert into this_table select from this table join with 5 other tables whose primary keys are foreign keys here to update those values. Joins are all on keys between the tables. I wouldn't mind posting the query but would you need the schema all tables involved in query as well?

EDIT2: First of all thanks all of you guys for your answers,comments and thoughts. The object from which read happens turned out to be a heap with no clustered index. Modifying it did give significant improvement in read operation which improved overall .

I marking David Spillet answer as accepted as it gave a methodical way to approach the problem. Learned a few things about posting questions :)

Also thanks for Frisbee for comments and answers. I know i kept on modifying the question :)

Answer 1

Without more detail about the procedure^[1] and the table it is inserting into we can't offer any specific help. Some general advice though:

• If you are inserting the rows individually, try to arrange them so that they are inserted in large blocks, all at once if possible.
• Confirm that the procedure is in fact being slow due to its own activity rather than waiting for locks held by other processes to clear.
• If you are pulling the inserted rows from a view or complex inline statement make sure that this is optimised (perhaps there is something like an avoidable table scan happening when reading what it needs before it even tries to write the new rows?).
• If you are using a complex statement and/or processing data in steps, check that you are not spooling stuff to tempdb unnecessary.

^{[1] if it is "top secret" so you can't just include the code directly[2] or (if it is long) via pastebin or similar then perhaps a sanitised version?
[2] though if it is that top secret maybe you should be paying a consultant rather than asking for free advice in public ;-)}

Update

The query plan posted does suggest that for that particular statement you have a read issue not a write issue. It look like you have a correlated sub-query that is providing an aggregated figure per row and there isn't a suitable index to filter on (though there is one that allows it to avoid an extra sort operation, hence the index scan was not followed by a sort). More than that (which is educated guesswork anyway) I can't say without the details already requested. For this specific statement: the SQL for statement 54 and the table+index structure of "object8" and "object9", preferably without obfuscation[3].

^{[3] By disguising names you hide something that might give us clues about what you are actually trying to achieve. Help us help you, provide the details requested and don't actively hide details unless you really have to.}

Regarding Cost Percentages

As Aaron said, the cost percentages are based on approximate times for a particular configuration of developer PC at Microsoft in the late 90s. Even then they were not intended to be anything other than estimates/indicators and IIRC the calculations have not been jiggled to account for changes in CPU and memory technology since. Also the calculation works on logical operations: they don't differentiate between work performed in memory and physical IO operations. Be careful too to consider the rest of the query and not just that one expensive chunk - it may be that the expense is caused by a missed optimisation elsewhere that results in an otherwise efficient operation to be run millions of times instead of a few or just one - the cost % may lead you to try to optimise that part when in fact the core problem is a step or a few away.

Answer 2

15K in 5 minutes 50 / second - that is reasonable

The likely culprit is page splits and index fragmentation

Give this a try
On the insert sort by the order of the PK on the insert
Put a fill factor of like 50 on all the indexes
Try an insert
If this work then tune the fill factor and add in index maintenance
Maybe even tune the order of PK to put those with a more common insert sort early - like if one FK is a customerID and you load by customerID then have it early
If it does not work then go back to your prior fill factors

You might also try tablock holdlock
Some times it is faster just to take a tablock get your business done and get out

If you have lock contention you can create a test table or test when usage is low