sql server resource_semaphore wait type

Recently, I see a lot of resource_semaphores on one of our database servers. Queries are not executed and there are 10-20 queries which wait for resource_semaphore.
As I know this is memory wait type.

when I query the to see what exactly happens I see the following:

SELECT * FROM sys.dm_exec_query_resource_semaphores

resource_semaphore_id	target_memory_kb	max_target_memory_kb	total_memory_kb	available_memory_kb	granted_memory_kb	used_memory_kb	grantee_count	waiter_count	timeout_error_count	forced_grant_count	pool_id
0	3206936	9286736	3206936	3206936	0	0	0	0	0	0	1
1	168784	NULL	168784	168784	0	0	0	0	NULL	NULL	1
0	8982744	8982744	8982744	1749664	7233080	2520768	5	29	0	3	2
1	472776	NULL	472776	472776	0	0	0	0	NULL	NULL	2

Open in new window


SELECT * FROM sys.dm_exec_query_memory_grants

session_id	request_id	scheduler_id	dop	request_time	grant_time	requested_memory_kb	granted_memory_kb	required_memory_kb	used_memory_kb	max_used_memory_kb	query_cost	timeout_sec	resource_semaphore_id	queue_id	wait_order	is_next_candidate	wait_time_ms	plan_handle	sql_handle	group_id	pool_id	is_small	ideal_memory_kb
212	0	0	1	2013-06-07 10:33:17.390	NULL	2245680	NULL	8192	NULL	NULL	561.530778496422	14038	0	7	0	1	1996719	0x0600090012A20F0540A12443010000000000000000000000	0x0200000012A20F0530F6305437154D498A1E93FB64D51E95	2	2	0	3168456
170	0	0	1	2013-06-07 10:45:47.143	NULL	159112	NULL	9216	NULL	NULL	337.066533307906	8426	0	7	4	0	1247000	0x06000800FE85C435406158D0020000000000000000000000	0x02000000FE85C435051AFC6540AC8878427E35700D5B0187	2	2	0	159112
73	0	0	4	2013-06-07 10:46:11.443	NULL	70016	NULL	35680	NULL	NULL	437.459864113475	10936	0	7	5	0	1222703	0x0600090043B7C80840417854010000000000000000000000	0x0200000043B7C8087229F479AFB8BD84E155E49D89F2C7BC	2	2	0	70016
171	0	0	1	2013-06-07 10:50:31.170	NULL	1446616	NULL	5632	NULL	NULL	753.046952517677	18826	0	7	9	0	962984	0x05000900A3DFF540404114AD020000000000000000000000	0x03000900A3DFF5401087B20043A100000100000000000000	2	2	0	1446616
200	0	0	4	2013-06-07 10:58:33.230	NULL	70016	NULL	35680	NULL	NULL	437.459864113475	10936	0	7	16	0	480937	0x0600090043B7C80840417854010000000000000000000000	0x0200000043B7C8087229F479AFB8BD84E155E49D89F2C7BC	2	2	0	70016
216	0	0	4	2013-06-07 10:59:23.210	NULL	70016	NULL	35680	NULL	NULL	437.459864113475	10936	0	7	17	0	430969	0x0600090043B7C80840417854010000000000000000000000	0x0200000043B7C8087229F479AFB8BD84E155E49D89F2C7BC	2	2	0	70016
95	0	0	1	2013-06-07 11:01:09.523	NULL	2245680	NULL	8192	NULL	NULL	561.530778496422	14038	0	7	18	0	324656	0x0600090012A20F0540A12443010000000000000000000000	0x0200000012A20F0530F6305437154D498A1E93FB64D51E95	2	2	0	3168456
240	0	1	1	2013-06-07 10:36:37.333	NULL	1090312	NULL	13952	NULL	NULL	3345.65508936084	83641	0	8	0	0	1796781	0x060008008AB4470840C139D6020000000000000000000000	0x020000008AB447086E75503C5CF6B9FD03F4687855D1BA59	2	2	0	1090312
72	0	1	4	2013-06-07 10:39:30.580	NULL	70016	NULL	35680	NULL	NULL	437.459864113475	10936	0	7	3	0	1623547	0x0600090043B7C80840417854010000000000000000000000	0x0200000043B7C8087229F479AFB8BD84E155E49D89F2C7BC	2	2	0	70016
149	0	1	4	2013-06-07 10:50:22.253	NULL	70016	NULL	35680	NULL	NULL	437.459864113475	10936	0	7	8	0	971906	0x0600090043B7C80840417854010000000000000000000000	0x0200000043B7C8087229F479AFB8BD84E155E49D89F2C7BC	2	2	0	70016
99	0	1	1	2013-06-07 10:50:39.207	NULL	1446616	NULL	5632	NULL	NULL	753.046952517677	18826	0	7	10	0	954953	0x05000900A3DFF540404114AD020000000000000000000000	0x03000900A3DFF5401087B20043A100000100000000000000	2	2	0	1446616
234	0	1	1	2013-06-07 11:03:55.997	NULL	3104	NULL	2560	NULL	NULL	10.498727292283	262	0	6	1	0	158187	0x06000900D981D0034021DCCC000000000000000000000000	0x02000000D981D0035682B07B46EF45AA545F5325BAC8F794	2	2	0	3104
238	0	1	1	2013-06-07 11:05:15.063	NULL	4192	NULL	2048	NULL	NULL	16.3535015479505	408	0	6	2	0	79125	0x0600090065390F0F40815553010000000000000000000000	0x0200000065390F0F8F33F6533513C5DEC0FD8E8DD90DAD9D	2	2	0	4192
159	0	1	4	2013-06-07 11:05:23.950	NULL	70016	NULL	35680	NULL	NULL	437.459864113475	10936	0	7	19	0	70234	0x0600090043B7C80840417854010000000000000000000000	0x0200000043B7C8087229F479AFB8BD84E155E49D89F2C7BC	2	2	0	70016
205	0	2	1	2013-06-07 10:22:53.707	2013-06-07 10:51:14.740	1446616	1446616	5632	503832	503832	753.046952517677	18826	0	NULL	NULL	NULL	NULL	0x05000900A3DFF540404114AD020000000000000000000000	0x03000900A3DFF5401087B20043A100000100000000000000	2	2	0	1446616
80	0	2	1	2013-06-07 10:31:04.190	2013-06-07 10:55:55.237	1446616	1446616	5632	503832	503832	753.046952517677	18826	0	NULL	NULL	NULL	NULL	0x05000900A3DFF540404114AD020000000000000000000000	0x03000900A3DFF5401087B20043A100000100000000000000	2	2	0	1446616
96	0	2	1	2013-06-07 10:31:26.903	2013-06-07 11:03:54.507	1446616	1446616	5632	504472	504472	753.046952517677	18826	0	NULL	NULL	NULL	NULL	0x05000900A3DFF540404114AD020000000000000000000000	0x03000900A3DFF5401087B20043A100000100000000000000	2	2	0	1446616
85	0	2	4	2013-06-07 10:49:12.033	NULL	70016	NULL	35680	NULL	NULL	437.459864113475	10936	0	7	6	0	1042125	0x0600090043B7C80840417854010000000000000000000000	0x0200000043B7C8087229F479AFB8BD84E155E49D89F2C7BC	2	2	0	70016
192	0	2	1	2013-06-07 10:52:21.400	NULL	1090312	NULL	13952	NULL	NULL	3345.65508936084	83641	0	8	2	0	852750	0x060008008AB4470840C139D6020000000000000000000000	0x020000008AB447086E75503C5CF6B9FD03F4687855D1BA59	2	2	0	1090312
69	0	2	4	2013-06-07 10:55:39.377	NULL	70016	NULL	35680	NULL	NULL	437.459864113475	10936	0	7	14	0	654797	0x0600090043B7C80840417854010000000000000000000000	0x0200000043B7C8087229F479AFB8BD84E155E49D89F2C7BC	2	2	0	70016
122	0	2	4	2013-06-07 11:03:51.657	NULL	32584	NULL	4840	NULL	NULL	17.6286314768235	440	0	6	0	0	162531	0x06000900D63E340340414E8F000000000000000000000000	0x02000000D63E3403D126EDFFF80222E3E8D22AB3B4A2B4E3	2	2	0	32584
112	0	2	4	2013-06-07 11:05:15.073	NULL	67584	NULL	21088	NULL	NULL	43.8809281063909	1097	0	6	3	0	79109	0x0600090069955A194081F40B030000000000000000000000	0x0200000069955A19FE9586AC025A84BA2664EAAD6ECB13E8	2	2	0	67584
162	0	2	1	2013-06-07 11:05:53.973	NULL	2245680	NULL	8192	NULL	NULL	561.530778496422	14038	0	7	20	0	40219	0x0600090012A20F0540A12443010000000000000000000000	0x0200000012A20F0530F6305437154D498A1E93FB64D51E95	2	2	0	3168456
67	0	3	1	2013-06-07 10:26:11.680	2013-06-07 10:55:39.007	1446616	1446616	5632	504472	504472	753.046952517677	18826	0	NULL	NULL	NULL	NULL	0x05000900A3DFF540404114AD020000000000000000000000	0x03000900A3DFF5401087B20043A100000100000000000000	2	2	0	1446616
126	0	3	4	2013-06-07 10:36:43.570	NULL	70016	NULL	35680	NULL	NULL	437.459864113475	10936	0	7	1	0	1790547	0x0600090043B7C80840417854010000000000000000000000	0x0200000043B7C8087229F479AFB8BD84E155E49D89F2C7BC	2	2	0	70016
172	0	3	1	2013-06-07 10:37:25.000	NULL	1446616	NULL	5632	NULL	NULL	753.046952517677	18826	0	7	2	0	1749125	0x05000900A3DFF540404114AD020000000000000000000000	0x03000900A3DFF5401087B20043A100000100000000000000	2	2	0	1446616
184	0	3	1	2013-06-07 10:50:05.473	NULL	159112	NULL	9216	NULL	NULL	337.066533307906	8426	0	7	7	0	988687	0x06000800FE85C435406158D0020000000000000000000000	0x02000000FE85C435051AFC6540AC8878427E35700D5B0187	2	2	0	159112
188	0	3	1	2013-06-07 10:51:29.310	NULL	1090312	NULL	13952	NULL	NULL	3345.65508936084	83641	0	8	1	0	904844	0x060008008AB4470840C139D6020000000000000000000000	0x020000008AB447086E75503C5CF6B9FD03F4687855D1BA59	2	2	0	1090312
197	0	3	1	2013-06-07 10:53:05.940	NULL	2245680	NULL	8192	NULL	NULL	561.530778496422	14038	0	7	11	0	808219	0x0600090012A20F0540A12443010000000000000000000000	0x0200000012A20F0530F6305437154D498A1E93FB64D51E95	2	2	0	3168456
136	0	3	1	2013-06-07 10:53:06.883	NULL	1446616	NULL	5632	NULL	NULL	753.046952517677	18826	0	7	12	0	807281	0x05000900A3DFF540404114AD020000000000000000000000	0x03000900A3DFF5401087B20043A100000100000000000000	2	2	0	1446616
222	0	3	1	2013-06-07 10:54:06.173	NULL	1446616	NULL	5632	NULL	NULL	753.046952517677	18826	0	7	13	0	747984	0x05000900A3DFF540404114AD020000000000000000000000	0x03000900A3DFF5401087B20043A100000100000000000000	2	2	0	1446616
187	0	3	1	2013-06-07 10:56:11.633	NULL	7888	NULL	4608	NULL	NULL	122.294567883837	3057	0	7	15	0	622531	0x06000900A063742140A1C6D7020000000000000000000000	0x02000000A06374219FC61B350E15407D66C29921BC11A063	2	2	0	7888

Open in new window


Could you help me in solving the problem?
Also could you explain me what the fields mean:
requested_memory_kb
granted_memory_kb
required_memory_kb
used_memory_kb
max_used_memory_kb
query_cost

SQL server is 2008R2-SP2 and is used by SQL Reporting Services.
dedriAsked:
Who is Participating?
 
didnthaveanameConnect With a Mentor Commented:
The following query will at least give you some more useful information from the sys.dm_exec_query_resource_semaphores view:

select 
	SQL.text,
	QRS.*,
        QMG.*
from
	sys.dm_exec_query_resource_semaphores as QRS
		inner join sys.dm_exec_query_memory_grants as QMG on QRS.resource_semaphore_id = QMG.resource_semaphore_id
		cross apply sys.fn_get_sql( QMG.sql_handle ) as SQL;

Open in new window


As far as what the columns actually return, all I've got are:

sys.dm_exec_query_resource_semaphores - http://msdn.microsoft.com/en-us/library/ms366321.aspx

sys.dm_exec_query_memory_grants - http://msdn.microsoft.com/en-us/library/ms365393.aspx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.