SQL Server how to reduce the amount of memory provided by a stored procedure

Question

I am using SQL Server 2008 R2

Running sys.dm_os_waiting_tasks shows me the following:

There are a lot of delays in CXPACKET. As a result of this problem, it was pointed out that this is due to parallel query execution and that I have to deal with the MAXDOP settings, but the description of resources for all processes is unlikely:

exchangeEvent id=Pipe271035100 WaitType=e_waitPipeGetRow nodeId=5 

A wait indicator showing that e_waitPipeGetRow seems to indicate that it has something to do with the deadlock or a lot of requests blocked, but I could be wrong.

My question is: how do I solve the problem? I'm not even sure where to look.

Edit:

Since the publication of this question, the problem below has disappeared suddenly for a while. Today, though, the same problem arose and this is what I found with sys.dm_exec_requests:

There are several PROCIDs showing a lot of cxpacket. Here is an example of one of them 123 0 10.02.2015 16: 12: 21.617 suspended CHOICE 0x0300070048642C323B8DB30036A400000100000000000000 1304 11288 0x0500070048642C3240210C14030000000000000000000000 May 7 59676462-0CB3-4FB8-96FC-530B3892578D 0 RESOURCE_SEMAPHORE 248137 RESOURCE_SEMAPHORE 0x 0 1 1583321 0 0 0 248 138 0 8 0x000000000460A748 0 0 -1 한국어 YMD 7 1 0 1 0 1 1 1 1 2 1 0 0 0 4 0 0 1 0x941A9D1F032CAA8A 0x1CCA978D548EB09E

The wait type showing that RESOURCE_SEMAPHORE seems to indicate that the threads are conflicting over resources when the request is run in parallel, but I'm not sure. How to fix this problem?

Edit2:

And now I'm starting to understand the main problem

Running sys.dm_exec_query_memory_grants showed me some pretty unexpected information:

A huge amount of memory is provided to some processes (3 Gigs on top). In bad cases, several processes start asking for memory, which leads to resource competition . This caused all wait types RESOURCE_SEMAPHORE.

Digging deeper, I discovered that this happens when some specific stored procedure is called repeatedly. Ultimately, we will fix the basic SQL problems in it. I believe this has something to do with parametric sniffing, but in order to immediately alleviate the resource conflict problem, I tried to take the following measures:

First I ran DBCC FREEPROCCACHE to clear the plan cache. This did not reduce the amount of requested memory.

Then I tried to change the procedure using OPTION RECOMPILE. It didn't do anything either.

So I pretty much lost where to do it. How to make an SP request for less memory?

Answer 1

If you are using Enterprise Edition SQL Server, you can set the maximum memory that will be allocated for 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, which reduces this, can cause as many problems as it solves. If you can isolate connections that perform problematic queries, you can configure separate limits for these connections, see:

Sql Server Resource Governor

Resource Governor is the only corporate function, I understand that all editions use the standard working group for the standard distribution of resources. I would be wondering if anyone knows if the default workload group reconfiguration works on instances other than Enterprise?

To find help, explain the reason for your problem:

If you narrowed down the problem to a problematic stored procedure, the execution plan can tell you which statements cause large memory grants. In SSMS, you need to open the properties of the operator to view all the information about the provision of memory.

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

Operators that are allocated working memory will have the Memory Fraction property, which sets the fraction of the total amount of query memory that the operator excludes for use.

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

If you can find a problem area that modifies a query or index, to eliminate the operation or reduce the approximate number of rows involved, you need to reduce the requested memory.

Answer 2

This type of wait occurs when a request executes a parallel plan, and some threads end before others. Threads waiting for the rest to complete display this type of wait. Regarding the description of the expectation, I believe that this is a way to describe the fact that this thread is waiting for its results to be consumed. In short, there is nothing to worry about.

One thing you need to pay attention to: why do threads get such uneven workloads? An easy way to see how many rows are consumed by each statement in the execution plan is the SQL Sentry Plan Explorer tool. I assume that you will see a skew. In my experience, this is usually caused by inaccurate statistics.

Answer 3

There are only a few reasons why the SQL server is requesting the amount of memory, if we assume that this is not an error (since it worked fine before), it should not be the optimal query plan. A quick fix will update statistics and restore indexes.

If this does not help, I'm afraid you will need to debug at least a little. Knowing what kind of request is causing your age helps a lot. Entering OPTION(RECOMPILE, FORCE ORDER) prompts on demand helps in many cases.

The following statement can also help you type in a problematic query. It lists all requests from the plan cache, as well as the number of actions performed and average execution time. Beware, this is a heavy application, disabling the query plan selection will be fixed.

 SELECT [qh] .*, [qp] .query_plan FROM (SELECT [cp]. [objtype] , [Query Hash] = [qs2].[query_hash] , [Query Plan Hash] = [qs2].[query_plan_hash] , [Total MB] = SUM ( [cp].[size_in_bytes] ) / 1024.00 / 1024.00 , [Avg CPU Time] = SUM ([qs2] .[total_worker_time]) / SUM ( [qs2].[execution_count] ) , [Execution Total] = SUM ([qs2] .[execution_count]) , [Total Cost] = SUM ([qs2] .[total_worker_time]) , [Example Statement Text] = MIN ([qs2] .[statement_text]) , [plan_handle] = MIN ( [qs2].[plan_handle] ) , [statement_start_offset] = MIN ( [qs2].[statement_start_offset] ) FROM (SELECT [qs].*, SUBSTRING ( [st].[text] , ([qs]. [statement_start_offset] / 2) + 1, ((CASE [statement_end_offset] WHEN -1 THEN DATALENGTH ( [st].[text] ) ELSE [qs] .[statement_end_offset] END - [qs].[statement_start_offset] ) / 2) + 1) AS [statement_text] FROM [sys] .[dm_exec_query_stats] AS [qs] CROSS APPLY [sys]. [dm_exec_sql_text] ([qs]. [sql_handle]) AS [st]) AS [qs2] INNER JOIN [sys]. [dm_exec_cached_plans] AS [cp] ON [qs2] .[plan_handle] = [cp] .[plan_handle] GROUP BY [cp]. [objtype], [qs2].[query_hash] , [qs2].[query_plan_hash] ) AS [qh] CROSS APPLY [sys]. [dm_exec_query_plan] ([qh]. [plan_handle]) AS [qp] -- For the demo, use the ORDER BY [Example Statement Text] ORDER BY [Example Statement Text] -- For the real-world, use the following order by: -- ORDER BY [qh].[Total Cost] DESC ; GO 

Answer 4

You can try the Resource Governor that ships with SQL Server to limit or save resources for SQL tasks.

Answer 5

In the first step, follow these steps and retest

 sp_configure 'show advanced options' ,1 reconfigure go sp_configure 'max degree of parallelism',1 reconfigure go sp_configure 'show advanced options' ,0 reconfigure go 

After this configuration, continue testing and see if the problem is resolved.