SQL SERVER – Query Cost 100%

How to tune a T-SQL with Query Cost 100%?

SQL SERVER - Query Cost 100% querycost0-800x143

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.

SQL SERVER - Query Cost 100% querycost1

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.

SQL SERVER - Query Cost 100% querycost2

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%.

SQL SERVER - Query Cost 100% querycost3

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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Execution Plan, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Finding Queries Using Particular Index
Next Post
SQL SERVER – Dirty Pages vs Clean Pages in Memory

Related Posts

Leave a Reply