Three Hour Insert

RichardBrianSmith 2016-01-20 07:04:20

This is the worst part of our nightly DW import, and its really slow.

/storage/attachments/1455-tmpF05A.queryanalysis

RichardBrianSmith 2016-01-20 09:08:05

Maybe I should remove some indexes?

Dave Ballantyne 2016-01-20 18:07:07

The first thing that jumps out to me is the amount of spills on the sorts that are shown.
Desired memory is ~13GB granted is ~5.5GB, how much memory have you in the server ?
Is there any resource governor limits in place ?

RichardBrianSmith 2016-01-21 12:09:34

Hi Dave, The machine has 32GB memory and we do not use Resource Govenor.

Dave Ballantyne 2016-01-25 09:04:14

If you have tried Paul's suggestions below and its still not good enough then you could try using the MAX_GRANT_PERCENT hint to 'steal' some/enough working space. https://support.microsoft.com/en-us/kb/3107401
Naturally this mean that that memory will not be available for other concurrent processes, so really is a last resort.

The DDL will help to create a more 'workable' solution, as per Pauls comment the Description column conversion is an interesting one and may be able to be tuned.

SQLkiwi 2016-01-21 05:05:01

On a quick analysis without existing table and index DDL, the following index may help:

CREATE INDEX index_name 
ON dbo.tblDataObjectDetail
(
    DataObjectID, 
    Value
)
INCLUDE 
(
    FieldID
);

This should eliminate the hash join and parallel sort.

If the insert processes a significant number of rows relative to the existing contents of the target table, you may find it quicker to drop/disable the two nonclustered indexes on the target before the insert, rebuilding them afterwards. This will eliminate the spool and sorts associated with nonclustered index maintenance.

You could also consider a WITH (TABLOCKX) hint on the insert, and/or enabling trace flag 610 for this query to enable minimally logged b-tree inserts.

You should patch your instance from SP1 CU11 to SP3 CU1. Also check why the data types of the Description column do not match between source and target.

RichardBrianSmith 2016-01-26 11:24:23

Thanks Paul, I have dropped the 2 indexes, added the hint, and patched to SP3. Due to other issues it will take a week to get definitive feedback.

RichardBrianSmith 2016-01-29 11:32:53

Yes, the insert has come down to 1 hr 5 mins. I commented out the (re-)create for the 2 dropped indexes in the sp (which seems fine), and really must persuade my manager to splurge for the paid-for plan-explorer. Thanks again.

SQLkiwi 2016-01-30 03:29:16

That seems like a very worthwhile improvement. Thanks for the feedback.

January 20, 2016

No Comments