How to tune a T-SQL with Query Cost 100%?
This is indeed an interesting question and I have received during Comprehensive Database Performance Health Check. Let us discuss it today.
I asked few more questions to my client to understand what they meant by T-SQL with a Cost of query at 100%. It seems that they ran a single query, and the query’s cost was 100%. As per them, every query they ran, had a query cost of the 100%. This indeed surprised them and wanted me to help them tune the query so the cost goes down.
Here is the image for you to understand what they mean when they are discussing the cost of a query.
Well, here is the answer. If you are running a single query, the query’s cost will always be 100% as it is a single query. However, if you are running more than 1 query, the cost will be distributed among various queries. For example, if you run 3 queries and if they are all equal you may see their percentages like 33% each.
In my case, the client was running a single query and that was the reason they were seeing the cost of the query as 100%.
However, there are some scenarios when you will see one query showing the cost 100% and another query showing only 0%. In that case, you definitely need to pay attention to the query which is very expensive and start tuning it.
Here is one thing I must say that you should not 100% depend on the % numbers. It is quite possible that they are misleading. Here is the video which you should watch to understand how to write efficient queries.
If you liked this blog, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.
Here are my few recent videos and I would like to know what is your feedback about them.
- Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
- COUNT(*) and Index – SQL in Sixty Seconds #175
- Index Scans – Good or Bad? – SQL in Sixty Seconds #174
- Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
- Avoid Join Hints – SQL in Sixty Seconds #172
- One Query Many Plans – SQL in Sixty Seconds #171
Reference: Pinal Dave (http://blog.SQLAuthority.com)
