Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with field Id (bigint, IDENTITY) as primary key and clustered index on it. I inserted 400 rows and saw execution plan. I got: the relative query cost for this insertion 36% and for this query "Clustered Index Insert"'s cost 97%. What's going on? Why does it work so slow?

ADDED:

enter image description here

continue enter image description here

See also SQL Server insert performance

share|improve this question
    
Is it actually slow? What are the timings? What else does the batch do such that the cluster insert is 97%? I.e. what consumes the other 3%? –  Mark Storey-Smith Sep 1 '11 at 10:54
    
@Mark Inserted scan or Deleted scan 3% -> Top 0% -> Compute Scalar 0% -> Compute Scalar 0% -> Clustered Index Insert 97% –  garik Sep 1 '11 at 11:00
    
@Mark Total batch execution time 34 seconds, relative problem query cost 36% (I have the second one with the almost same cost 37%). So 73% of 34 seconds is too much, what do you think? Oh, these inserts are performed in trigger. :) –  garik Sep 1 '11 at 11:06
1  
Suggest you post the execution plan and scripts for the insert and triggers, it's blind guesswork otherwise. –  Mark Storey-Smith Sep 1 '11 at 11:15

2 Answers 2

up vote 2 down vote accepted

The relative cost of the components of an execution plan is not always reliable.

The INSERTED scan or deleted scan shouts TRIGGER to me. This is your problem most likely.

Are you looping over 400 rows and sending an email in an extreme example?

share|improve this answer
    
no, I am doing some calculations, getting 400 records and insert them in transaction. strange. Also I am doing some checks in trigger (checking update mask with stored one in table). maybe it is better to stop doing this and compare mask with predefined one. –  garik Sep 1 '11 at 15:02
    
It seams execution plan cheats :). Result does not depends on how many records code inserts (or too indirectly depends), it estimates ONLY. Thank you. –  garik Sep 5 '11 at 8:29

Are you specifying the identity value? Is your identity value in ASC or DESC order? How about your clustered index? CAn you post the execution plan somewhere?

share|improve this answer
    
If you already have the ID value you can specify the value instead of have the SQL Server specify the new value. I would need the entire plan. Based on what you've uploaded from the plan the actual insert into the clustered index is doing all the work. –  mrdenny Sep 3 '11 at 20:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.