August 28, 2016 at 7:35 pm
Ran the execution plan for the script and it showed 50% cost for Key Lookup(Clustered) and 50 %Index Seek (NonClustered). It didn't show any missing indexes, but DTA recommended non-clustered index and creating statistics.
After applying the recommended change, now the execution plan shows 100 % for non-clustered index.
Now DTA doesn't provide any recommendations.
Index Seek (NonClustered)
Scan a particular range of rows from a nonclustered index.
Physical Operation Index Seek
Logical Operation Index Seek
Actual Number of Rows 0
Estimated I/O Cost 0.003125
Estimated CPU Cost 0.0001581
No. of Executions 2
Estimated No. Of Executions 1
Estimated Operator Cost 0.0032831(100%)
Estimated Subtree Cost 0.0032831
Estimated no. of Rows 1
Estimated Row Size 1218 B
Actual Rebinds 0
Acutal Rewinds 0
Ordered True
Node Id 30
August 29, 2016 at 5:42 am
PJ_SQL (8/28/2016)
Ran the execution plan for the script and it showed 50% cost for Key Lookup(Clustered) and 50 %Index Seek (NonClustered). It didn't show any missing indexes, but DTA recommended non-clustered index and creating statistics.After applying the recommended change, now the execution plan shows 100 % for non-clustered index.
Now DTA doesn't provide any recommendations.
Index Seek (NonClustered)
Scan a particular range of rows from a nonclustered index.
Physical Operation Index Seek
Logical Operation Index Seek
Actual Number of Rows 0
Estimated I/O Cost 0.003125
Estimated CPU Cost 0.0001581
No. of Executions 2
Estimated No. Of Executions 1
Estimated Operator Cost 0.0032831(100%)
Estimated Subtree Cost 0.0032831
Estimated no. of Rows 1
Estimated Row Size 1218 B
Actual Rebinds 0
Acutal Rewinds 0
Ordered True
Node Id 30
What's your question?
August 29, 2016 at 5:59 am
Is it ok to have 100% cost for in clustered index lolling at the execution plan ?
Or do you recommend anything else ?
August 29, 2016 at 6:05 am
PJ_SQL (8/29/2016)
Is it ok to have 100% cost for in clustered index lolling at the execution plan ?Or do you recommend anything else ?
Above, you said that the work was being done in a seek on the nonclustered index, which is good. The only other thing I'd say to look at is the estimated number of rows versus the actual number of rows, which you don't show. A wide disparity could point to obsolete statistics. Always look at the actual execution plan (not the estimated) when tuning a query.
How's the execution time?
August 29, 2016 at 6:20 am
PJ_SQL (8/28/2016)
Ran the execution plan for the script and it showed 50% cost for Key Lookup(Clustered) and 50 %Index Seek (NonClustered). It didn't show any missing indexes, but DTA recommended non-clustered index and creating statistics.After applying the recommended change, now the execution plan shows 100 % for non-clustered index.
And now you have redundant indexes (and, to be honest probably didn't need to create one just to get rid of a single row lookup. New index to save maybe a couple ms and 2 or 3 reads not usually a good idea). Don't apply DTA recommendations blindly or without looking at your existing indexes. It's not always right and if you just use it and take recommendations you WILL have a badly over-indexed database.
Is it ok to have 100% cost for in clustered index lolling at the execution plan ?
It's a percentage. The cost has to add to 100%. If none of the other operators in the query take any time, then the index seek has to be 100%
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
August 29, 2016 at 6:22 am
Ed Wagner (8/29/2016)
The only other thing I'd say to look at is the estimated number of rows versus the actual number of rows, which you don't show.
Physical Operation Index Seek
Logical Operation Index Seek
Actual Number of Rows 0
Estimated no. of Rows 1
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
August 29, 2016 at 6:25 am
GilaMonster (8/29/2016)
Ed Wagner (8/29/2016)
The only other thing I'd say to look at is the estimated number of rows versus the actual number of rows, which you don't show.Physical Operation Index Seek
Logical Operation Index Seek
Actual Number of Rows 0
Estimated no. of Rows 1
I looked for it and still missed it. Thanks Gail.
August 29, 2016 at 6:27 am
Ok that makes sense, because initially it had key lookup and after creating nonclustered index the cost came upto 100%
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply