Understanding Relative Query Cost in SQL Server

We gives a brief description of Relative query Cost in SQL Server. It gives descriptions of what it is, how it is shown and the methods it can be applied to determine and resolve bottlenecks in SQL queries. The details provided is to provide understanding and use of this metric on query optimization by the developers.

What is Relative Query Cost?

In SQL Server, Relative Query Cost is a guess at a percentage of the fraction of resources (CPU and I/Os) that each element of a query or a series of queries on a batch is likely to absorb. It acts as comparative measure and you can tell which query or operation consumes more resources as compared to others when executed. It is there in the Execution Plan where the price can be seen in bold as it is the plan that SQL Server uses to give priority to your query to the most effective way.

It is important to know that these values are not exact time and /or CPU measurements. Rather they are relative estimates aimed at making comparisons. They furnish an insight of relative cost of various operations within your query or the set of queries.

Example Scenario:

An example of this in practice would be as follows. Let us assume that you run the following two queries simultaneously in SQL Server Management Studio (SSMS):

SELECT * FROM Employees WHERE Department = 'HR';
SELECT * FROM Departments;

In order to examine the Relative Query Cost, you should turn on the display of the Execution Plan. To achieve this in SSMS, you may use Ctrl + M before executing the queries. Alternatively, you can press the option of Display Estimated Execution Plan in the toolbar.

Having run the queries with the Execution Plan, you will get a graphical display of how the SQL Server will perform the queries. In this plan, the Relative Query Cost of every query will be found. As an example, you could see the following:

Query 1: Relative cost = 80%
Query 2: Relative cost = 20%
Interpretation

What does it imply? It states that Query 1 that does the querying of employees in the 'HR' department will be estimated to consume much system resources as compared to Query 2 that does the querying of all departments. Although both queries may be fast in terms of absolute sense, the Relative Query Cost shows that Query 1 is relatively expensive. This data cannot be overestimated when it comes to performance tuning. It enables you to optimize your efforts on the queries that are consuming the most resources. In the above example, you would focus on Query 1 and analyze it and possibly optimize it to boost the overall.

Practical Applications and Considerations

Some working applications and considerations of Relative Query Cost usage can be as follows:

Determining Bottlenecks: Relative Query Cost is mostly used in determining areas of performance bottlenecks in a set of queries. This technique will allow you to optimize the most costly areas of your workload by knowing what queries are the most expensive.
Query Optimization: After you have identified a costly query you can then use the Execution Plan to further break down the operations which are causing the query to be costly. It may include analysing table scans, index usage, or other elements, which may affect performance.
Index Tuning: In case of Execution Plan shows that a query is scanning the tables rather than using index, it is possible that you reconsider using indexes or creating indexes to enhance performance.
Query Rewriting: Query rewriting can also be used to facilitate performance in certain times. Execution Plan and Relative Query Cost may enable you to find the possibility of rewriting queries in a more efficient form.
Considerations of Hardware: Though the Relative Query Cost is a software-defined measure, one should keep the fact in mind that even at the hardware level, the performance might be compromised due to limits. It you have already made your queries as good as it can come and performance issues are still being experienced then you may need to upgrade your hardware.
It is not an Exact Measurement: It is imperative to reiterate the point that Relative Query Cost is not an exact measure unit. It is an estimation done in the consideration of multiple factors and can be less or more than the actual consumption of resources used in the execution of that particular workload and system structure.
Context is Relevant: The Relative Query Cost cannot be read out of context of the total number of queries. It may still be acceptable to have a high Relative Query Cost on a query that may simply be run rarely. However, when a costly query is run a lot, it could have a mountain of effect to the overall performance.

It implies that Query 1 is projected to consume higher system resources than Query 2, despite being able to execute at a fast pace.

Conclusion:

Relative Query Cost is a great command that can be used to determine the bottlenecks in the performance of tasks, and that can help when making an audit to optimize the queries in SQL Server. It is not a precise metric, but it can be used to get a proper comparative gauge of the resource workload of various queries or operations. In knowing how to use Relative Query Cost it will allow the developer to be able to tune SQL queries and achieve the overall better performance of the application. By addressing the most resource-costly sections first, one can attain a desirable performance improvement and make sure that his SQL Server environment is functioning with efficiency.

Performance

Understanding Relative Query Cost in SQL Server