(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

In today’s blog posting I want to talk about Query Memory in SQL Server, and want to show you how fast it can degrade the performance of your queries. Before we dive into the details about how SQL Server is managing Query Memory, I want to talk briefly about what Query Memory actually is.

Query Memory

SQL Server has to allocate Query Memory for various Execution Plans – based on their used operators. The following Execution Plan operators need Query Memory:

• Sort Operators
  • Sort
  • Sort (TOP N Sort)
• Hash Operators
  • Hash Match
  • Hash Match Aggregate
• Exchange Operators
  • Parallelism (Distribute Streams)
  • Parallelism (Repartition Streams)
  • Parallelism (Gather Streams)

Resource Semaphores

The Query Memory that is requested by an Execution Plan (the so-called Query Memory Grant) is taken from Buffer Pool Memory (with a maximum of 75%), and the used Resource Governor Workload Group defines the maximum per each query (up to 25% by default).

The Query Memory itself is a limited resource in SQL Server, and is therefore protected by a so-called Resource Semaphore. A semaphore itself is nothing more like a synchronization object that is used to control the access to a shared resource – in our case to the Query Memory. The nice thing about a semaphore is that it can allow the access to the shared resource to multiple threads in parallel. It’s not like a Spinlock that is a simple Mutex: you hold the Spinlock, or not!

In the context of SQL Server multiple queries can request Query Memory through the Resource Semaphores. But when the Query Memory is exhausted (everything is currently in use), queries have to wait until other queries are releasing Query Memory back to SQL Server. Then they can get their Query Memory.

Let’s have now a more detailed look on these Resource Semaphores that SQL Server is using here. In my case I have configured SQL Server with a Maximum Server Memory setting of only 500 MB to make it easy to reproduce some performance problems. Therefore the whole available Query Memory is about 375 MB large – 75% of the Buffer Pool Memory. For each Resource Governor Resource Pool you get 2 Resource Semaphores:

• Small Resource Semaphore: 5% of the whole Query Memory
• Large Resource Semaphore: 95% of the whole Query Memory

You can see and monitor these Resource Semaphores in the DMV sys.dm_exec_query_resource_semaphores. As you can see from the following picture, my SQL Server instance has 4 Resource Semaphores: 2 Resource Semaphores for the default Resource Pool, and 2 Resource Semaphores for the internal Resource Pool.

This DMV exposes you the following important columns for performance troubleshooting:

• resource_semphore_id: 0: small Resource Semaphore, 1: large Resource Semaphore
• max_target_memory_kb: How much Query Memory one query can get
• total_memory_kb: How much Query Memory is held and managed by that Resource Semaphore
• available_memory_kb: How much Query Memory is currently available by that Resource Semaphore
• granted_memory_kb: How much Query memory is currently granted by that Resource Semaphore

Resource Semaphore Queues

To make now things a little bit more complicated, each Resource Semaphore also has multiple queues available. And a submitted query which needs some Query Memory has to use the corresponding queue based on the query plan cost factor:

• Query Cost < 10: queue_id of 5
• Query Cost between 10 and 99: queue_id of 6
• Query Cost between 100 and 999: queue_id of 7
• Query Cost between 1000 and 9999: queue_id of 8
• Query Cost >= 10000: queue_id of 9

Which Resource Semaphore Queue a query is currently using can be seen through the DMV sys.dm_exec_query_memory_grants. This DMV can also tell you if a query has already successfully allocated Query Memory, or if the query is still waiting on the Query Memory Grant. Let’s have a more detailed look on this DMV. The following picture shows the output from this DMV while I was running a query that has requested some Query Memory.

This DMV exposes you the following important columns for troubleshooting:

• request_time: The time when the request for Query Memory was made
• grant_time: The time when the request for Query Memory was fulfilled by SQL Server
• requested_memory_kb: How much Query Memory the query requested
• granted_memory_kb: How much Query Memory the query got from SQL Server
• query_cost: The costs of the Execution Plan
• queue_id: The used queue based on the cost factor
• resource_semaphore_id: The used Resource Semaphore (small or large)

The most important column for me here is the column grant_time. If you see here a NULL value for a given query, it means that the query is waiting for Query Memory. In that case the query reports you a wait type RESOURCE_SEMAPHORE, which is more or less very terrible. Because a query can only start when the requested Query Memory was granted to the query! Therefore a wait type RESOURCE_SEMAPHORE means that the query is also not yet started! Just think about that…

With the column queue_id you can also see in which Resource Semaphore Queue the query was put by SQL Server. As I have described above, the query gets put into a queue based on the cost factor of the query plan. A cheap query from an OLTP workload uses a different queue than a very expensive query from a reporting or DWH workload.

And now we are coming to the most important point of this blog posting:

A query which is waiting in a queue can be only executed when ALL lower-cost queues do not contain *any* other waiting queries!!!

And this can lead to serious performance problems. Imagine you have your large DWH query, which is currently waiting in queue_id 9. And in addition you have some recurring small queries from an OLTP workload that are always put into queue_id 5. Your DWH query will wait a very long time until it can be finally executed. I have tried to illustrate that behaviour with the following picture.

Let’s work now with a few different queries to demonstrate this problem. In the first step I have created a simple Stored Procedure in the ContosoRetailDW database that generates a query plan with a cost factor of 295.

CREATE PROCEDURE ReportingWorkload
AS
BEGIN
SELECT TOP 10000 * FROM
(
SELECT TOP 1000000 * FROM FactOnlineSales
) AS s
ORDER BY ProductKey
OPTION (MAXDOP 1)
END
GO

Afterwards I have executed this stored procecure with 10 parallel users through the Stress Testing Tool ostress.exe that is part of the RML Utilities. As mentioned before my Maximum Server Memory setting is configured with only 500 MB.

ostress.exe -S"sqlag-node1" -Q"EXEC ContosoRetailDW.dbo.ReportingWorkload" -n10

When you run that Stored Procedure with 10 parallel users, you can already see in sys.dm_exec_query_memory_grants that a lot of queries are waiting on outstanding Query Memory Grants: for almost all queries the column grant_time is NULL, and only one query got a Query Memory Grant.

When you concurrently look into sys.dm_exec_requests, you can see that these waiting queries are reporting a wait type RESOURCE_SEMAPHORE back to SQL Server:

As you can see from the output of sys.dm_exec_query_memory_grants the query with a cost factor of around 295 was put into the Resource Semaphore Queue 7. Let’s try to run now concurrently some other queries which are dependent on Query Memory. The following listing shows 2 queries: one query with a cost factor of 2.97, and another one with a cost factor of 3064.

-- Query Cost Factor: 2.97
-- queue_id: 5
SELECT TOP 10000 * FROM
(
SELECT TOP 40000 * FROM FactOnlineSales
) AS s
ORDER BY ProductKey
OPTION (MAXDOP 1)
GO
-- Query Cost Factor: 3064
-- queue_id: 8
SELECT TOP 10000 * FROM
(
SELECT TOP 10000000 * FROM FactOnlineSales
) AS s
ORDER BY ProductKey
OPTION (MAXDOP 1)
GO

The query with the cost factor of 2.97 is put into the Resource Semaphore Queue 5, and is executed almost immediately. But the query with the cost factor of 3064 is put into the queue 8, and must wait until all lower queues don’t contain any queries anymore. So it must wait a much longer time and reports a longer time the wait type RESOURCE_SEMAPHORE.

Query Memory Waits

How long a query waits for Query Memory depends on its cost factor. By default it waits (in seconds) 25 times of the cost factor with a maximum of 86400 seconds (24 hours). And after that wait time the query is finally executed by SQL Server.

You can override that default query wait behaviour with the instance setting query_wait (s). The following listing shows how you can change that setting to 20 seconds.

sp_configure 'query wait (s)', 20
RECONFIGURE
GO

When you have done the required RECONFIGURE, queries are only waiting 20 seconds for a Query Memory Grant. And afterwards SQL Server schedules them for execution, but with a much smaller Query Memory Grant. Therefore the query will spill over to TempDb. And this of course also introduces some physical I/O overhead which leads to a longer query execution time.

Besides the instance setting query_wait (s), you can also configure the query wait time on the Resource Governor Workload Group through the option request_memory_grant_timeout_sec. The following listing shows how you can accomplish the same thing by changing the Memory Grant Timeout on default Workload Group to 20 seconds.

ALTER WORKLOAD GROUP [default] WITH
(
request_memory_grant_timeout_sec = 20
)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

The outcome is the same: the query waits for a maximum of 20 seconds, and is executed afterwards with a smaller Query Memory Grant…

Summary

As you have seem from this blog posting, Query Memory Grants and Resource Semaphores can be really dangerous in SQL Server. It can get really problematic when you have a mixed workload (OLTP and DWH) on the same SQL Server instance. Because then your large DHW queries can be slowed down by your small OLTP queries, when they are dependent on Query Memory Grants.

Therefore I always suggest to make your OLTP queries as simple as possible, and you should make sure that these query plans don’t use operators which are dependent on Query Memory Grants. And this can be accomplished by working on your Indexing Strategy. If you have a good Indexing Strategy in place, there is no need for Sort/Hash operations, and for no parallel Execution Plans. Please keep that in mind.

Thanks for your time,

-Klaus