Situation : 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 of
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
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.
