Solved

sql server resource_semaphore wait type

Posted on 2013-06-07
1
921 Views
Last Modified: 2013-06-27
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.
0
Comment
Question by:dedri
1 Comment
 
LVL 8

Accepted Solution

by:
didnthaveaname earned 500 total points
ID: 39229763
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now