2

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

  • 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

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?

|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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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