cost of clustered index insert is 100%

Question

cost of clustered index insert is 100%

charipg

SSCrazy Eights

Points: 9888
More actions
September 7, 2014 at 2:59 am

#289763

how to reduce the cost of clustered index insert ?
any suggestions pls?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

charipg (9/7/2014)
how to reduce the cost of clustered index insert ?
any suggestions pls?

Quick thought, it's likely that this is not a problem of any sort, if the action is only an insert then the cost percentage is always going to be 100%.

😎

Can you share some information, execution plans, DDL and sample data?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Eirikur Eiriksson (9/7/2014)
Quick thought, it's likely that this is not a problem of any sort, if the action is only an insert then the cost percentage is always going to be 100%.

Exactly this.

Short of rather stupid suggestions like 'don't insert data', there's nothing much that can be done.

Is the specific insert query a problem? Is it a long-running, high CPU, high reads query that runs frequently?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

charipg SSCrazy Eights Points: 9888 More actions · Answer 3

charipg

SSCrazy Eights

Points: 9888

September 7, 2014 at 11:52 pm

#1743763

yes this is the specific insert query a problem .

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

The cost estimates of all operators within a query execution plan are supposed to add up to 100%. If you're inserting a row then it should be natural to accept that the insert into the clustered index, i.e. the insert into the table itself, will generate 100% of the cost of the query execution.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

charipg (9/7/2014)
yes this is the specific insert query a problem .

What leads you to believe that?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

charipg SSCrazy Eights Points: 9888 More actions · Answer 6

actually it inserting simple single record.

we estimated time should be milli seconds, but its taking 2min.

need to find what might be causing this. not getting any clue.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

charipg (9/8/2014)
actually it inserting simple single record.
we estimated time should be milli seconds, but its taking 2min.
need to find what might be causing this. not getting any clue.

The best clues are provided by the actual execution plan. Can you obtain one and attach it to your next post?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

charipg (9/8/2014)
actually it inserting simple single record.

Then it's probably not the query. If it's inserting 1 row and the insert is 100% of the cost, there isn't going to be all that much that can be done with the query itself.

we estimated time should be milli seconds, but its taking 2min.
need to find what might be causing this. not getting any clue.

You've checked the waits? Ensured that it's not simply that the query is blocked for those 2 min?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass