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 GO 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 ORDER BY LineTotal DESC
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.
WHILE 1=1 SELECT * FROM Sales.SalesOrderDetail ORDER BY LineTotal DESC 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.
