October 27, 2015 at 4:03 am
Hi Experts,
Was checking an SP execution plan and found an Clustered Index Scan with cost 33% .There were other 2 index seek with cost 33%.
Created a Non Clustered Index on a column which is used in filter criteria ,now the cost is 25% and scan changed to seek but also created a key lookup on clustered index. Also all the index seek cost is 25%.
Which one is better ? adding the new index or not? Even with the new Index its using Clustered Index right ,what about the cost? Experts please help.
October 27, 2015 at 4:39 am
October 27, 2015 at 5:42 am
Ratheesh.K.Nair (10/27/2015)
Hi Experts,Was checking an SP execution plan and found an Clustered Index Scan with cost 33% .There were other 2 index seek with cost 33%.
Created a Non Clustered Index on a column which is used in filter criteria ,now the cost is 25% and scan changed to seek but also created a key lookup on clustered index. Also all the index seek cost is 25%.
Which one is better ? adding the new index or not? Even with the new Index its using Clustered Index right ,what about the cost? Experts please help.
Before changes and after, whichever was fastest over several executions was "better". A more precise answer will require more precise information.
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
October 27, 2015 at 5:57 am
Most likely you are having a regular NC Index that is hooking up to a Clustered Index to present the final results to the End user Query. Try creating a covering index with the columns used in the Query and check the performance. Please note that Covering index can take lots of space and resources based on the scenario and hence you would want to do that carefully to ensure other unintended problems.
October 27, 2015 at 6:30 am
October 27, 2015 at 11:35 am
Don't look at the costs. Those are estimates, and they have to add to 100%. Knowing that something is 33% or 25% is useless, it says nothing of any value. 50% in a query that takes 10ms is faster than 2% of a query that takes 10 minutes.
Look at the duration. Look at the CPU time. Look at the reads. Don't look at costs when you're tuning queries.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply