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

Virul Patel
  • 171
  • 1
  • 3
  • 8
  • 'Estimated Cost' not 'Actual Cost'. Those percentages are just estimates. – Mark Sinkinson Apr 01 '15 at 12:31
  • I am getting same cost in Actual execution plan – Virul Patel Apr 01 '15 at 12:32
  • 2
    The percentages are still estimated costs in the Actual Execution Plan... – Mark Sinkinson Apr 01 '15 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 ? – Virul Patel Apr 01 '15 at 12:33
  • I would recommend you post both execution plans. It's likely there will be a 'Sort' Operator on one of them which will take up a percentage of the cost. – Mark Sinkinson Apr 01 '15 at 12:34
  • Why do both examples show a Clustered Index being used if you're removing the only clustered index in one of these scenarios? – LowlyDBA - John M Apr 01 '15 at 12:47
  • John, Mark - I attached both execution plan. Image 1 is plan with Clustured Index. Image 2 is plan after clustered index converted to non clustered index. we are thinking to convert clustered index to non clustered because process is not using clustered index, however we need to keep index because it is created on primary key (identity) – Virul Patel Apr 01 '15 at 12:50
  • @VirulPatel Like I said, both images show a Clustered Index Seek, so you did not remove/convert the clustered index in the second query run. – LowlyDBA - John M Apr 01 '15 at 13:19
  • John, First image has 2 clustered index, one from base table and another from parent table. while second image has only one clustered index from parent table (Table has a foreign key from parent table), hope that helps – Virul Patel Apr 01 '15 at 13:31
  • 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 01 '15 at 13:32

1 Answers1

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.

Rob Farley
  • 16,199
  • 2
  • 38
  • 61