Workspace Memory / Query Memory Tuning - Resource_Semaphore / IO_Completion / Sleep_Task Waits

SQL Server is configured to use a lot of memory, but my query is slow and not using all the memory available and it is spilling the sort or the hash match operation to tempdb, how can I tune the configuration and the query?

Memory allocating queries request memory based on estimation (ideal memory) and query memory (workspace memory) availability, when they don’t get the right amount of memory they spill to tempdb and lead to performance issues. Previous articles discussed ways to make the estimation better; this article discusses ways to address query memory availability.

You can find the ideal amount of memory a query needs (when the query is executing) using sys.dm_exec_query_memory_grants. Common memory allocating queries are that perform Sort and do Hash Match operations like Hash Join or Hash Aggregation or Hash Union.

select * from sys . dm_exec_query_memory_grants

go

Column ideal_memory_kb indicates the ideal amount of memory the query needs. This is based on estimation, this might be incorrect for various reasons including out of date statistics, in some cases (more common that you might think) under estimation of memory by the optimizer even when the statistics are up to date (refer to article http://jahaines.blogspot.com/2010/03/performance-tuning-101-what-you-will.html) and due to plan caching (refer to article Plan Caching and Query Memory). Some might recommend tweaking the index/column statistics in an undocumented way to inflate the estimation for additional memory, I suggest following the MSSQL Tip http://www.mssqltips.com/sqlservertip/1955 and webcast 1 and 2 at www.sqlworkshops.com/webcasts to understand the issue and the recommendations.

The ideal amount of memory a query needs is based on estimation. Requested memory is based on ideal memory and maximum available workspace memory. There is a possibility the estimation is correct (let’s say you have up to date statistics and/or fixed the under estimation issues by following the above articles) but the available workspace memory is not enough and hence ideal amount is low. The next question is: what is available workspace memory.

Available workspace memory, also known as query memory, is the amount of memory available for common memory allocating queries that perform Sort and do Hash Match operations. This is automatically calculated based on your system configuration. You can monitor the currently available workspace memory and maximum workspace memory by executing the command dbcc memorystatus and looking for ‘Available’ and ‘Current Max’ under ‘Query Memory Objects (default)’ and ‘Small Query Memory Objects (default)’. Or using Performance Monitor counters ‘Granted Workspace Memory (KB)’ and ‘Maximum Workspace Memory (KB)’ under object Memory Manager. ‘Maximum Workspace Memory (KB)’ is the sum of ‘Current Max’ of both ‘Query Memory Objects (default)’ and ‘Small Query Memory Objects (default)’, note ‘Current Max’ is in pages (* 8 = KB).

Here is a query to find the ‘Maximum Workspace Memory (KB)’ using sys.dm_os_performance_counters, ‘Maximum Workspace Memory (KB)’ can be up to 75% of ‘Target Server Memory (KB)’.

The best way to learn is to practice. To create the below tables and reproduce the behavior,  subscribe to our Newsletter and I will send you the table creation script.

--Example provided by www.sqlworkshops.com

select(selectcntr_value

from sys . dm_os_performance_counters   

whereobject_namelike'%Memory Manager%'andcounter_namelike'Maximum

Workspace Memory (KB)%')as'Maximum Workspace Memory (KB)',

  (selectcntr_value

from sys . dm_os_performance_counters

   whereobject_namelike'%Memory Manager%'andcounter_namelike'Target Server Memory (KB)%')as'Target Server Memory (KB)',

    ( select cntr_value from sys . dm_os_performance_counters

   whereobject_namelike'%Memory Manager%'andcounter_namelike'Maximum Workspace Memory (KB)%')*100.0/

    ( select cntr_value from sys . dm_os_performance_counters

   whereobject_namelike'%Memory Manager%'andcounter_namelike'Target Server Memory (KB)%')asRatio

go

Don't miss our SQL Server Performance Tuning Hands-on Workshop! Register

SQL Server Performance Tuning Consulting & Health Check

sp_whopro - SQL Server Activity Monitoring and Logging Stored Procedure - Free Download

SQLTest - SQL Server Performance, Load, Stress and Unit Test Tool - Free Download

SQLVideo - SQL Server Performance Tuning and Troubleshooting - Free Youtube Videos, now also available on MSDN Channel 9!

In my server, when Target Server Memory is 4096 MB, Maximum Workspace Memory is 3077 MB, which is about 75% of Target Server Memory.

By default a query will not request more than 25% of this Maximum Workspace Memory in SQL Server 2008 and above and this Memory Grant 25% can be changed using Resource Governor Workload Group settings. With SQL Server 2005 and below, this is 20% and cannot be changed without the support of Microsoft (with a combination of undocumented trace flag and changes to the configuration parameters).

Why should you care about 25%? You might have a customer executing a heavy reporting query in the night and they want to use more than 25% of Maximum Workspace Memory for that single query. Let’s assume the customer configured 4096 MB for their SQL Server instance (the Target Server Memory might be less than the configured memory if there is memory pressure on the server), let's say their workspace memory is 3077 MB (‘Query Memory Objects (default)’->‘Current Max’ = 381175 pages = 3049400 KB + ‘Small Query Memory Objects (default)’ ->‘Current Max’ = 12800 pages = 102400 KB; 3049400 KB + 102400 KB = 3077 MB. And this customer’s query executing the report is limited to a maximum of 3049400 KB / 4 = 744 MB.

Let’s say the customer’s query executing the report needs 1,470MB of query memory (ideal memory) in order not to spill the sort to tempdb, but the query will request only 744MB. In case the customer can set the ‘request_max_memory_grant_percent’ (Memory Grant %) of the Workload Group setting to 50%, then the query can request up to 1,488MB and in this case the query will request the ideal memory it needs which is 1,470 MB.

SQL Server has memory grant queues based on cost, if queries need more memory and there is not enough memory available in the queue, then the query will wait, you can get additional details using the command dbcc memorystatus. If a query requests huge amount of memory, but doesn’t utilize it (due to over estimation), this memory will be reserved and cannot be used by other queries, in some cases this will lead to unnecessary memory grant waits. So one has to be very careful not to overestimate (also described in www.sqlworkshops.com/webcasts) too much memory as it will affect concurrency. When the query waits for memory, the wait type will be ‘RESOURCE_SEMAPHORE’.

Let's set 'max server memory (MB)' to 4 GB.

Scenario 1: This query will underestimate memory due to optimizer issues and will also request less memory (744 MB) due to the 25% Resource Governor Workload Group Memory Grant setting. The query will be slow spilling the sort to tempdb.

When the sort spills to tempdb the wait type will be ‘IO_COMPLETION’ but when the Hash Match operation spills to tempdb the wait type will be ‘SLEEP_TASK’.

We are using option (maxdop 1) to disable parallelism, to learn more about monitoring and tuning parallel query execution, refer to the Webcast and article Parallel Sort and Merge Join – Watch out for unpredictability in performance.

Scenario 2: This query will have better memory estimation due to the option clause with optimize for hint, but will still request less memory (744 MB) due to the 25% Resource Governor Workload Group Memory Grant setting. The query will also be slow spilling the sort to tempdb.

Scenario 3: This query will have better memory estimation due to the option clause with optimize for hint and will also request enough memory (1,470MB) due to the 50% Resource Governor Workload Group Memory Grant setting. The query will be fast with no spilling of sort to tempdb.  Please read the entire article and answer the challenge posted at the end of this article before changing the Resource Governor Workload Group Memory Grant setting in your production server as there are major disadvantages of changing the Resource Governor Workload Group Memory Grant setting, don't do it without understanding the full picture.

alterworkloadgroup[default]with(request_max_memory_grant_percent=50)

go

alterresourcegovernorreconfigure

go

setstatisticstimeon

go

--Example provided by www.sqlworkshops.com

declare@c1int,@c2int,@c3char(2000)

declare@iint

set@i= 500000

select@c1=c1,@c2=c2,@c3=c3

   fromtab7

   wherec1<@i

   orderbyc2

   option(optimizefor(@i= 600000),maxdop 1)

-- Option optimize for is used to inflate memory request

-- without this the query will spill the sort to tempdb due

-- to query optimizer under estimation of memory.

-- One possibility is to inflate the number of rows,

-- the other possibility is to inflate the row size

-- For more information refer to webcasts 1 & 2

-- at www.sqlworkshops.com/webcasts.

go

 

Scenario 4:

Let's execute 2 of this query concurrently in 2 sessions, with 25% Rsource Governor Workload Group Memory Grant Setting.

alter workload group [Default] with (request_max_memory_grant_percent=25)

go

alter resource governor reconfigure

go

Session 1:

Let's execute this query in a loop and measure the performance of the query executed in session 2. This query will have better memory estimation due to the option clause with optimize for hint, but will still request less memory (744MB) due to the 25% Resource Governor Workload Group Memory Grant setting. The query will be spilling the sort to tempdb.

while 1=1begin

--Example provided by www.sqlworkshops.com

declare @c1 int, @c2 int, @c3 char (2000)

declare @i int

set @i = 500000

select @c1 = c1, @c2 = c2, @c3 = c3

from tab7

where c1 < @i

order by c2

option (optimize for (@i = 600000), maxdop 1)

-- Option optimize for is used to inflate memory request

-- without this the query will spill the sort to tempdb due

-- to query optimizer under estimation of memory.

-- One possibility is to inflate the number of rows,

-- the other possibility is to inflate the row size

-- For more information refer to webcasts 1 & 2

-- at www.sqlworkshops.com/webcasts.

end

go

Session 2:

Let's execute this query few times. This query will have better memory estimation due to the option clause with optimize for hint, but will still request less memory (744 MB) due to the 25% Resource Governor Workload Group Memory Grant setting. The query will be slow spilling the sort to tempdb. This query might take up to twice the amount of time to complete compared to Scenario 2, because 2 queries from 2 sessions are concurrently spilling to tempdb. There is no wait for memory grants (no RESOURCE_SEMAPHORE waits).

set statistics time ongo

--Example provided by www.sqlworkshops.com

declare @c1 int, @c2 int, @c3 char (2000)

declare @i int

set @i = 500000

select @c1 = c1, @c2 = c2, @c3 = c3

from tab7

where c1 < @i

order by c2

option (optimize for (@i = 600000), maxdop 1)

-- Option optimize for is used to inflate memory request

-- without this the query will spill the sort to tempdb due

-- to query optimizer under estimation of memory.

-- One possibility is to inflate the number of rows,

-- the other possibility is to inflate the row size

-- For more information refer to webcasts 1 & 2

-- at www.sqlworkshops.com/webcasts.

go

 

Scenario 5:

Let's execute 2 of this query concurrently in 2 sessions, with 50% Resource Governor Workload Group Memory Grant setting.

alter workload group [default] with (request_max_memory_grant_percent=50)

go

alter resource governor reconfigure

go

Session 1:

Let's execute this query in a loop and measure the performance of the query executed in session 2. This query will have better memory estimation due to the option clause with optimize for hint and will also request enough memory (1,470MB) due to the 50% Resource Governor Workload Group Memory Grant setting. The query will have no spilling of sort to tempdb.

while 1=1begin

--Example provided by www.sqlworkshops.com

declare @c1 int, @c2 int, @c3 char (2000)

declare @i int

set @i = 500000 select @c1 = c1, @c2 = c2, @c3 = c3

from tab7

where c1 < @i

order by c2

 option (optimize for (@i = 600000), maxdop 1)

-- Option optimize for is used to inflate memory request

-- without this the query will spill the sort to tempdb due

-- to query optimizer under estimation of memory.

-- One possibility is to inflate the number of rows,

-- the other possibility is to inflate the row size

-- For more information refer to webcasts 1 & 2

-- at www.sqlworkshops.com/webcasts.

end

go

Session 2:

Let's execute this query few times. This query will have better memory estimation due to the option clause with optimize for hint and will also request enough memory (1,470 MB) due to the 50% Resource Governor Workload Group Memory Grant setting. The query will be fast with no spilling of sort to tempdb. But this query might take up to twice the amount of time to complete compared to Scenario 3, because 2 queries from 2 sessions are concurrently requesting nearly 50% of workspace memory. There is wait for memory grants (RESOURCE_SEMAPHORE waits), SQL Server grants memory to one query at a time as the memory in the grant queue is not enough to grant simultaneously the requested memory to both queries.

set statistics time ongo

--Example provided by www.sqlworkshops.com

declare @c1 int, @c2 int, @c3 char (2000)

declare @i int

set @i = 500000

select @c1 = c1, @c2 = c2, @c3 = c3

from tab7

where c1 < @i

order by c2

option (optimize for (@i = 600000), maxdop 1)

-- Option optimize for is used to inflate memory request

-- without this the query will spill the sort to tempdb due

-- to query optimizer under estimation of memory.

-- One possibility is to inflate the number of rows,

-- the other possibility is to inflate the row size

-- For more information refer to webcasts 1 & 2

-- at www.sqlworkshops.com/webcasts.

go

Overall performance of scenario 5 is better than scenario 4 even though there was memory grant waits. In this case waiting for memory is far worse than spilling the sort to tempdb.

Scenario 6:

Let's execute two queries with less memory requirement concurrently in two sessions (so memory can be granted for both queries simultaneously from the same memory grant queue), with 50% Resource Governor Workload Group Memory Grant setting.

Session 1:

Let's execute this query in a loop and measure the performance of the query executed in session 2. This query will have better memory estimation due to the option clause with optimize for hint and will also request enough memory (1,102 MB) due to the 50% Resource Governor Workload Group Memory Grant setting. The query will have no spilling of sort to tempdb.

while 1=1 begin

--Example provided by www.sqlworkshops.com

declare @c1 int, @c2 int, @c3 char (2000)

declare @i int

 set @i = 400000

select @c1 = c1, @c2 = c2, @c3 = c3

from tab7

where c1 < @i

order by c2

option (optimize for (@i = 450000), maxdop 1)

-- Option optimize for is used to inflate memory request

-- without this the query will spill the sort to tempdb due

-- to query optimizer under estimation of memory.

-- One possibility is to inflate the number of rows,

-- the other possibility is to inflate the row size

-- For more information refer to webcasts 1 & 2

-- at www.sqlworkshops.com/webcasts.

end

go

Session 2:

Let's execute this query few times. This query will have better memory estimation due to the option clause with optimize for hint and will also request enough memory (1,102 MB) due to the 50% Resource Governor Workload Group Memory Grant setting. The query will be fast with no spilling of sort to tempdb. There will be no wait for memory grants (RESOURCE_SEMAPHORE waits), SQL Server grants memory to both queries simultaneously as the memory in the grant queue is enough to grant the requested memory to both queries.

set statistics time ongo

--Example provided by www.sqlworkshops.com

declare @c1 int, @c2 int, @c3 char (2000)

declare @i int

set @i = 400000

select @c1 = c1, @c2 = c2, @c3 = c3

from tab7

where c1 < @i

order by c2

option (optimize for (@i = 450000), maxdop 1)

-- Option optimize for is used to inflate memory request

-- without this the query will spill the sort to tempdb due

-- to query optimizer under estimation of memory.

-- One possibility is to inflate the number of rows,

-- the other possibility is to inflate the row size

-- For more information refer to webcasts 1 & 2

-- at www.sqlworkshops.com/webcasts

go

Challenge:  There are major disadvantages of changing the Resource Governor Workload Group Memory Grant setting, so don't make any changes without understanding the full picture. I will cover this is the next article, meanwhile if you can guess some reasons, write to me: Contact.

This and similar topics are covered in my workshops.

If you are not able to attend any of our workshops but would like to learn more performance tuning techniques, I strongly recommend you to watch our Free Webcasts.

To receive example SQL Scripts used in the webcasts  subscribe to our Newsletter.

Read some more SQL Server Performance Tuning Articles

This article is for informational purposes only; you use any of the suggestions given here entirely at your own risk.

© 2010-2018 www.sqlworkshops.com. All rights reserved.

Back to Top