I have a query which is slow when i run from SSMS and from application.I am trying to tune it.I took the execution plan and on checking I can see cost of Clustured Index Seeks on a table is 85%.That table has only two columns and one column is set as primary key.So doing Clustered Index Seek on the table is good i guess.Should i be concerned on that cost of the operation?
Best Answer
Yes and no.
Queries showing a high cost are usually a good place to start, when looking to tune a query. They are places where the query planner believed the query would spend a larger amount of its time and effort to get the query results.
However, when you've got the actual query numbers, one of the key things to check is how the estimate and the actual query run matched up. One of the first things to fix, when possible, are places where there's a big difference between the actual and the estimated numbers. For example, if the estimated and the actual rows are way off, then capturing/updating statistics for the table in question can result in significant gains.
Taking this into account, it may well be transactions that the query planner expected not to be significant, but where the estimated numbers are much smaller than the actual numbers, that are the transactions that most need tuning.
When estimated and actual numbers match well, then those high cost operations are again a good place to focus your tuning efforts. However, they shouldn't be the only thing you pay attention to.