dedri
asked on
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 _semaphore s
SELECT * FROM sys.dm_exec_query_memory_g rants
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.
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
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
SELECT * FROM sys.dm_exec_query_memory_g
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.