2

enter image description hereSituation : We have table It has millions of records. Table has 1 Clustured Index and 1 Non Clustured Index. It was found that Clustured Index is not used by any ofenter image description here SQL Query. But Insert Execution plan has 86% cost on Clustured Index.

Action: If I convert Clustured Index to Non Clustured Index, Execution plan gives me Table Cost 90%.

Which one is better for performance, If I convert clustered index to non clustered index, will it be helpful.

Thanks, Virul

10
  • 'Estimated Cost' not 'Actual Cost'. Those percentages are just estimates. Apr 1, 2015 at 12:31
  • I am getting same cost in Actual execution plan Apr 1, 2015 at 12:32
  • 2
    The percentages are still estimated costs in the Actual Execution Plan... Apr 1, 2015 at 12:33
  • OK, Got it. Which I go for now, I just want to understand which one is better Table Insert Cost or Clustured Index Insert ? Apr 1, 2015 at 12:33
  • 3
    For a great many reasons, I would argue any serious table in SQL Server should always have a clustered index - therefore, that discussion is kinda moot..... heaps (tables without CI) have serious drawbacks and performance penalties - too many to even bother - use a CI - always.
    – marc_s
    Apr 1, 2015 at 13:32

1 Answer 1

4

The decision about whether to use a heap or a clustered index for your underlying table structure is a lot more complicated than the speed of insertion, although that certainly can be a factor.

When you insert a new row, it is typically going to be quicker to get the row into the heap, but there are a lot of things going on that can affect both the estimate and the actual cost.

In your case, it sounds like you have additional indexes in play, and the maintenance of these would be weighing into those estimates. If you look in the Properties (hit F4), you will see the list of objects affected by Insert, and if you have made a non-clustered index instead of your clustered one, you are probably doing more work in your Table Scan, as there is one extra object to maintain.

If performance isn't actually a problem, I'd suggest leaving things as they are. If you're wanting to tune the pants off it, you're already getting deeper than you're likely to get in a forum like this. Heaps can be good, but they can also be awful. Clustered Indexes have drawbacks too, so your decision is a lot more complicated.

As to why it's higher - check the object list.

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