SQL Server how do I reduce the amount of memory granted by stored procedure

Question

I'm using SQL Server 2008 R2

Running sys.dm_os_waiting_tasks shows me the following:

There's a lot of process stuck on CXPACKET. Googling this issue indicated that this has something to do with parallel execution of the queries and that I have to mess around with MAXDOP settings, but the resource description for all of the processes make little sense:

exchangeEvent id=Pipe271035100 WaitType=e_waitPipeGetRow nodeId=5

Waittype showing e_waitPipeGetRow seems to indicate that it has something to do with either deadlock or massive amount of queries being locked, but I might be wrong.

My question is, how do I go about troubleshooting this issue? I'm not even sure where to look.

Edit:

Since the posting of this question, the below issue went away all of a sudden for a while. Today though, same issue occurred and here's what I found with sys.dm_exec_requests:

There's several PROCID showing lots of cxpacket. Here's an example of one of them: 123 0 2015-02-10 16:12:21.617 suspended SELECT 0x0300070048642C323B8DB30036A400000100000000000000 1304 11288 0x0500070048642C3240210C14030000000000000000000000 7 5 59676462-0CB3-4FB8-96FC-530B3892578D 0 RESOURCE_SEMAPHORE 248137 RESOURCE_SEMAPHORE 0 1 1583321 0x 0 0 0 248138 8 0x000000000460A748 0 0 0 -1 한국어 ymd 7 1 0 1 0 1 1 1 1 2 -1 0 0 0 4 0 0 1 0x941A9D1F032CAA8A 0x1CCA978D548EB09E

Wait type showing RESOURCE_SEMAPHORE seems to indicate that the threads are in contention for resources while running the query in parallel, but I'm not sure. How do I troubleshoot this?

Edit2:

Ah now I'm finally beginning to understand the core issue

Running sys.dm_exec_query_memory_grants showed me pretty surprising info:

Several processes are being granted a huge amount of memory (3 Gigs from above picture). In bad cases, several processes start requesting that amount of memory, leading to resource contention. This is what caused all of the RESOURCE_SEMAPHORE wait types.

Digging deeper, I've found that this occurs when a particular stored procedure is repeatedly called. We will eventually fix the underlying SQL problems in it. I believe it has something to do with parameter sniffing but to mitigate the resource contention issue immediately, I've tried taking the following measures:

First, I ran DBCC FREEPROCCACHE to clear the plan cache. This didn't reduce the amount of memory requested.

Then I tried to alter the procedure with OPTION RECOMPILE. This didn't do anything either.

So I'm pretty lost on where to go about this. How do I make the SP request for less memory?

Answer 1

If you are running Enterprise Edition of SQL Server you can set the maximum memory that will be allocated to a single query as a percentage of the available server memory using:

ALTER WORKLOAD GROUP [DEFAULT] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 25)
GO
-- RECONFIGURE to make the setting take effect
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

25% is the default value, reducing this may cause as many problems as it solves though. If you can isolate the connections executing the problem queries it is possible to configure separate limits for those connections see:

Sql Server Resource Governor

Resource Governor is a enterprise only feature, I understand all editions use the default workload group for standard resource allocation though. I would been interested if anyone knows if reconfiguring the default workload group works on non-Enterprise instances?

To find help fine the cause of your problem:

If you have narrowed the issue down to a problem stored procedure the execution plan can tell you what operators are causing the large memory grants. In SSMS you need to open the operator properties to see all the memory grant information.

The root node in the execution plan will have information about the memory grant for the query.

Operators that are allocated working memory will have a Memory Fraction property which specifies the fraction of the queries total memory grant the operator is excepted to use.

You are looking for operators that have a memory fraction close to 1, Hash Match and Sort operators are a good place to start looking.

If you can locate the problem area altering the query or indexing to eliminate the operation or reduce the estimated number of rows involved should reduce the memory grant requested.

Answer 2

This wait type is encountered when a query executes a parallel plan and some of the threads finish before others. The threads that are waiting for the others to finish display this wait type. As for the wait description, my take on it is that it's a way to describe the fact that that thread is waiting for its results to be consumed. So, in short, there's not much to worry about here.

One thing to look at though is: why are the threads getting such uneven work loads? An easy way to see how many rows are being consumed by each operator in an execution plan is with the Plan Explorer tool from SQL Sentry. My guess is that you'll see a skew. In my experience, this is usually caused by inaccurate statistics.