SQL Server Query Cost, Memory Grant & SQLRESERVATIONS Clerk

In today’s blog, we will be looking into SQL Server Query Cost, Memory Grant & SQLRESERVATIONS Clerk.

Every query has a cost, which SQL Server records in the Execution Plan. This is the cost assigned to the query by the optimizer. While troubleshooting query performance in SQL Server, it is crucial to be are aware of this cost. In many cases, queries are sorting and hashing as a result of internal operations. For such queries, SQL Server requires an additional memory grant which is requested from a clerk called SQLRESERVATIONS.

For this demo, we will be using the AdventureWorks2014 database. Having the Execution Plan turned ON, a SELECT statement is executed to return all entries in the table SalesOrderDetail.

USE AdventureWorks2014
SELECT * FROM Sales.SalesOrderDetail


Hovering over the SELECT operator in the Execution Plan, the details on one of the tooltips, Estimated Subtree Cost can be acquired that displays the total cost of the plan. This is the cost of the query assigned by the optimizer. Internally, SQL Server will use this cost in various cases such as converting a plan from a serial to a parallel one. SQL Server has a higher preference for low-cost queries as far as the order of execution is concerned. The queries with a higher cost value are executed later as they are more time-consuming. The cost of the plan will increase in case of more complex queries involving multiple joins, sorts, or hash operations. This particular query does not have a memory grant, as there are no sort or hash operations in progress.

Next, a descending ORDER BY clause is added to LineTotal.  As shown below, upon executing the query, a Sort operation takes place and as a result, the query needs an additional memory grant of 27456KB to complete the execution successfully.

SELECT * FROM Sales.SalesOrderDetail


Next, we will look into an expensive query that requires a much higher memory grant, thus making it relatively inefficient. SQL Server has a limited amount of memory to grant to queries and when this limit is exceeded, it puts these queries on hold, until the less costly queries are done with their execution. In such cases, SQL Server assigns a wait type to those threads called RESORCE_SEMAPHORE. This Wait Type is beyond the scope of this blog.


SQL Server has a concept of Clerks, which allows the tracking of memory usage with respect to components. For sake of demonstration, we ORDER BY the column pages_kb in descending, which returns the memory consumption in kilobytes. On doing so, the following results are obtained.

SELECT * FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC


The result set displays the list of Clerks and the amount of memory consumed by them in kilobytes. It is to be noted that once the query that requires a memory grant has been successfully executed, the memory is released.

On running a relatively inexpensive query such as below.

Note: Stop the below query once the observations are done.

SELECT * FROM Sales.SalesOrderDetail
In another tab check the memory usage of clerks. 
SELECT * FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC

SQLRESERVATIONS is climbing up on the table with a memory grant of 28848KB.


Next, a more expensive query (EXEC MemoryHog) is executed that demands a higher memory grant as a result of multiple sort operations. On doing so, SQLRESERVATIONS is at the top of the list with a memory grant value of almost 1GB. The moment this particular workload is stopped, the memory reserved by the Clerk is released back to SQL Server.


To conclude, in our query tuning endeavors, it is important to keep a track of the query costs and memory grants to achieve optimum performance.


About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

Leave a Reply

Your email address will not be published.