System Troubleshooting - Pro SQL Server Internals - page 526

Finally, it is worth mentioning that the Data Warehouse and Decision Support Systems usually play under
different rules. In those systems, it is typical to have I/O intensive queries that scan large amounts of data. Performance
tuning of such systems can require different approaches than those found in OLTP environments, and they often lead
to database schema changes rather than index tuning.
Memory-Related Wait Types
The RESOURCE_SEMAPHORE wait type indicates the wait for the query memory grant. As already discussed, every query
in SQL Server requires some memory to execute. When there is no memory available, SQL Server places a query
in one of three queues, based on the memory grant size, where it waits until memory becomes available. A high
percentage of RESOURCE_SEMAPHORE waits indicate that SQL Server does not have enough memory to fulfill all memory
grant requests.
You can confirm the problem by looking at the Memory Grants Pending performance counter in the Memory
Manager Object. This counter shows the number of queries waiting for memory grants. Ideally, the counter value
should be zero all the time. You can also look at the sys.dm_exec_query_memory_grants view, which provides
information about memory grant requests, both pending and outstanding.
Obviously, one of the ways to address this issue is to reduce the memory grant size for the queries. You can
optimize or simplify the queries in a way that removes memory-intensive operators, hashes, and sorts, for example,
from the execution plan. You can obtain the query plan and text from the sys.dm_exec_query_memory_grants view
directly, as shown in Listing 27-7. It is also possible, however, to take a general approach and focus on non-optimized
queries. General query optimization reduces the load on the system, which leaves more server resources available.
Listing 27-7. Obtaining query information from the sys.dm_exec_query_memory_grants view
select
mg.session_id
,t.text as [SQL]
,qp.query_plan as [Plan]
,mg.is_small
,mg.dop
,mg.query_cost
,mg.request_time
,mg.required_memory_kb
,mg.requested_memory_kb
,mg.wait_time_ms
,mg.grant_time
,mg.granted_memory_kb
,mg.used_memory_kb
,mg.max_used_memory_kb
from
sys.dm_exec_query_memory_grants mg with (nolock)
cross apply sys.dm_exec_sql_text(mg.sql_handle) t
cross apply sys.dm_exec_query_plan(mg.plan_handle) as qp
option (recompile)
CXMEMTHREAD is another memory-related wait type that you can encounter in systems. These waits occur when
multiple threads are trying to allocate memory from HEAP simultaneously. You can often observe a high percentage of
these waits in systems with a large number of ad-hoc queries where SQL Server constantly allocates and de-allocates
plan cache memory. Enabling the Optimize for Ad-hoc Workloads configuration setting can help address this problem
if plan cache memory allocation is the root-cause.