February 2, 2015 at 4:14 am
Background - I have several resource governor groups,settings can be seen below. total server memory is 185GB
The following was captured from dm_resource_governor_resource_pools
SELECT * INTO #tmp_GridResults_1
FROM (
SELECT N'1' AS [pool_id], N'2014-11-01 16:44:24.387' AS [statistics_start_time], N'7879089356' AS [total_cpu_usage_ms], N'78696' AS [cache_memory_kb], N'526848' AS [compile_memory_kb], N'0' AS [used_memgrant_kb], N'8210' AS [total_memgrant_count], N'0' AS [total_memgrant_timeout_count], N'0' AS [active_memgrant_count], N'0' AS [active_memgrant_kb], N'0' AS [memgrant_waiter_count], N'179200000' AS [max_memory_kb], N'13266104' AS [used_memory_kb], N'179200000' AS [target_memory_kb], N'0' AS [out_of_memory_count], N'0' AS [min_cpu_percent], N'100' AS [max_cpu_percent], N'0' AS [min_memory_percent], N'100' AS [max_memory_percent], N'100' AS [cap_cpu_percent] UNION ALL
SELECT N'2' AS [pool_id], N'2014-11-01 16:44:24.387' AS [statistics_start_time], N'56739274185' AS [total_cpu_usage_ms], N'277992' AS [cache_memory_kb], N'989632' AS [compile_memory_kb], N'1024' AS [used_memgrant_kb], N'6620884' AS [total_memgrant_count], N'0' AS [total_memgrant_timeout_count], N'4' AS [active_memgrant_count], N'2720' AS [active_memgrant_kb], N'0' AS [memgrant_waiter_count], N'35840000' AS [max_memory_kb], N'1270344' AS [used_memory_kb], N'35840000' AS [target_memory_kb], N'0' AS [out_of_memory_count], N'0' AS [min_cpu_percent], N'20' AS [max_cpu_percent], N'0' AS [min_memory_percent], N'20' AS [max_memory_percent], N'100' AS [cap_cpu_percent] UNION ALL
SELECT N'257' AS [pool_id], N'2014-11-01 16:44:25.837' AS [statistics_start_time], N'5432663704' AS [total_cpu_usage_ms], N'31872' AS [cache_memory_kb], N'33056' AS [compile_memory_kb], N'53602200' AS [used_memgrant_kb], N'4796' AS [total_memgrant_count], N'0' AS [total_memgrant_timeout_count], N'1' AS [active_memgrant_count], N'53616960' AS [active_memgrant_kb], N'0' AS [memgrant_waiter_count], N'71680000' AS [max_memory_kb], N'53681888' AS [used_memory_kb], N'71680000' AS [target_memory_kb], N'0' AS [out_of_memory_count], N'0' AS [min_cpu_percent], N'40' AS [max_cpu_percent], N'0' AS [min_memory_percent], N'40' AS [max_memory_percent], N'100' AS [cap_cpu_percent] UNION ALL
SELECT N'263' AS [pool_id], N'2014-11-01 16:44:25.837' AS [statistics_start_time], N'4423099251' AS [total_cpu_usage_ms], N'234264' AS [cache_memory_kb], N'81136' AS [compile_memory_kb], N'0' AS [used_memgrant_kb], N'2291556' AS [total_memgrant_count], N'0' AS [total_memgrant_timeout_count], N'0' AS [active_memgrant_count], N'0' AS [active_memgrant_kb], N'0' AS [memgrant_waiter_count], N'7168000' AS [max_memory_kb], N'315400' AS [used_memory_kb], N'7168000' AS [target_memory_kb], N'0' AS [out_of_memory_count], N'1' AS [min_cpu_percent], N'4' AS [max_cpu_percent], N'1' AS [min_memory_percent], N'4' AS [max_memory_percent], N'100' AS [cap_cpu_percent] UNION ALL
SELECT N'265' AS [pool_id], N'2014-11-01 16:44:25.837' AS [statistics_start_time], N'529161320' AS [total_cpu_usage_ms], N'4184' AS [cache_memory_kb], N'12320' AS [compile_memory_kb], N'0' AS [used_memgrant_kb], N'4325912' AS [total_memgrant_count], N'0' AS [total_memgrant_timeout_count], N'0' AS [active_memgrant_count], N'0' AS [active_memgrant_kb], N'0' AS [memgrant_waiter_count], N'173824000' AS [max_memory_kb], N'16504' AS [used_memory_kb], N'173824000' AS [target_memory_kb], N'0' AS [out_of_memory_count], N'0' AS [min_cpu_percent], N'100' AS [max_cpu_percent], N'1' AS [min_memory_percent], N'100' AS [max_memory_percent], N'100' AS [cap_cpu_percent] UNION ALL
SELECT N'271' AS [pool_id], N'2014-11-10 13:48:26.003' AS [statistics_start_time], N'4627287131' AS [total_cpu_usage_ms], N'20144' AS [cache_memory_kb], N'3536' AS [compile_memory_kb], N'0' AS [used_memgrant_kb], N'980011' AS [total_memgrant_count], N'0' AS [total_memgrant_timeout_count], N'0' AS [active_memgrant_count], N'0' AS [active_memgrant_kb], N'3' AS [memgrant_waiter_count], N'71680000' AS [max_memory_kb], N'23680' AS [used_memory_kb], N'71680000' AS [target_memory_kb], N'0' AS [out_of_memory_count], N'0' AS [min_cpu_percent], N'40' AS [max_cpu_percent], N'0' AS [min_memory_percent], N'40' AS [max_memory_percent], N'100' AS [cap_cpu_percent] UNION ALL
SELECT N'274' AS [pool_id], N'2014-11-19 12:04:29.843' AS [statistics_start_time], N'3503241700' AS [total_cpu_usage_ms], N'19304' AS [cache_memory_kb], N'29560' AS [compile_memory_kb], N'0' AS [used_memgrant_kb], N'20946702' AS [total_memgrant_count], N'0' AS [total_memgrant_timeout_count], N'0' AS [active_memgrant_count], N'0' AS [active_memgrant_kb], N'0' AS [memgrant_waiter_count], N'173824000' AS [max_memory_kb], N'48864' AS [used_memory_kb], N'173824000' AS [target_memory_kb], N'0' AS [out_of_memory_count], N'0' AS [min_cpu_percent], N'100' AS [max_cpu_percent], N'1' AS [min_memory_percent], N'100' AS [max_memory_percent], N'100' AS [cap_cpu_percent] UNION ALL
SELECT N'275' AS [pool_id], N'2014-12-15 09:34:23.593' AS [statistics_start_time], N'698747322' AS [total_cpu_usage_ms], N'4496' AS [cache_memory_kb], N'35168' AS [compile_memory_kb], N'0' AS [used_memgrant_kb], N'225574' AS [total_memgrant_count], N'0' AS [total_memgrant_timeout_count], N'0' AS [active_memgrant_count], N'0' AS [active_memgrant_kb], N'0' AS [memgrant_waiter_count], N'1792000' AS [max_memory_kb], N'39664' AS [used_memory_kb], N'1792000' AS [target_memory_kb], N'0' AS [out_of_memory_count], N'1' AS [min_cpu_percent], N'1' AS [max_cpu_percent], N'1' AS [min_memory_percent], N'1' AS [max_memory_percent], N'100' AS [cap_cpu_percent] ) t;
SELECT [pool_id], [statistics_start_time], [total_cpu_usage_ms], [cache_memory_kb], [compile_memory_kb], [used_memgrant_kb], [total_memgrant_count], [total_memgrant_timeout_count], [active_memgrant_count], [active_memgrant_kb], [memgrant_waiter_count], [max_memory_kb], [used_memory_kb], [target_memory_kb], [out_of_memory_count], [min_cpu_percent], [max_cpu_percent], [min_memory_percent], [max_memory_percent], [cap_cpu_percent]
FROM #tmp_GridResults_1
DROP TABLE #tmp_GridResults_1
As you can see pool 271 has three processes resource semaphore waiting while the only other significant user is pool 257 which is using 53GB on mem
The following data has been taken from dm_exec_query_memory_grants. The next candidate query was requesting 53 GB and the max/target memory for the pool is 71GB so there is no reason I can see for why this process would experience Resource Semaphore waits - can anyone shed any light of this problem?
SELECT * INTO #tmp_GridResults_1
FROM (
SELECT N'227' AS [session_id], N'0' AS [request_id], N'6' AS [scheduler_id], N'1' AS [dop], N'2015-02-01 12:00:04.163' AS [request_time], N'2015-02-01 12:00:04.163' AS [grant_time], N'2720' AS [requested_memory_kb], N'2720' AS [granted_memory_kb], N'2048' AS [required_memory_kb], N'1024' AS [used_memory_kb], N'1024' AS [max_used_memory_kb], N'0.603954697788718' AS [query_cost], N'25' AS [timeout_sec], N'1' AS [resource_semaphore_id], NULL AS [queue_id], NULL AS [wait_order], NULL AS [is_next_candidate], NULL AS [wait_time_ms], N'2' AS [group_id], N'2' AS [pool_id], N'1' AS [is_small], N'2720' AS [ideal_memory_kb] UNION ALL
SELECT N'187' AS [session_id], N'0' AS [request_id], N'7' AS [scheduler_id], N'1' AS [dop], N'2015-02-01 12:59:52.570' AS [request_time], NULL AS [grant_time], N'1216' AS [requested_memory_kb], NULL AS [granted_memory_kb], N'640' AS [required_memory_kb], NULL AS [used_memory_kb], NULL AS [max_used_memory_kb], N'23.1582103039099' AS [query_cost], N'578' AS [timeout_sec], N'0' AS [resource_semaphore_id], N'1' AS [queue_id], N'0' AS [wait_order], N'0' AS [is_next_candidate], N'7924' AS [wait_time_ms], N'274' AS [group_id], N'271' AS [pool_id], N'0' AS [is_small], N'1216' AS [ideal_memory_kb] UNION ALL
SELECT N'101' AS [session_id], N'0' AS [request_id], N'9' AS [scheduler_id], N'8' AS [dop], N'2015-02-01 11:13:14.913' AS [request_time], N'2015-02-01 11:13:14.913' AS [grant_time], N'53616960' AS [requested_memory_kb], N'53616960' AS [granted_memory_kb], N'18136' AS [required_memory_kb], N'53602200' AS [used_memory_kb], N'53603392' AS [max_used_memory_kb], N'5817555.49681223' AS [query_cost], N'86400' AS [timeout_sec], N'0' AS [resource_semaphore_id], NULL AS [queue_id], NULL AS [wait_order], NULL AS [is_next_candidate], NULL AS [wait_time_ms], N'257' AS [group_id], N'257' AS [pool_id], N'0' AS [is_small], N'7050532248' AS [ideal_memory_kb] UNION ALL
SELECT N'117' AS [session_id], N'0' AS [request_id], N'15' AS [scheduler_id], N'8' AS [dop], N'2015-02-01 10:14:08.687' AS [request_time], NULL AS [grant_time], N'3708632' AS [requested_memory_kb], NULL AS [granted_memory_kb], N'12952' AS [required_memory_kb], NULL AS [used_memory_kb], NULL AS [max_used_memory_kb], N'286970.585840499' AS [query_cost], N'86400' AS [timeout_sec], N'0' AS [resource_semaphore_id], N'4' AS [queue_id], N'1' AS [wait_order], N'0' AS [is_next_candidate], N'9951584' AS [wait_time_ms], N'274' AS [group_id], N'271' AS [pool_id], N'0' AS [is_small], N'3708632' AS [ideal_memory_kb] UNION ALL
SELECT N'98' AS [session_id], N'0' AS [request_id], N'26' AS [scheduler_id], N'8' AS [dop], N'2015-02-01 10:12:04.467' AS [request_time], NULL AS [grant_time], N'53616960' AS [requested_memory_kb], NULL AS [granted_memory_kb], N'32904' AS [required_memory_kb], NULL AS [used_memory_kb], NULL AS [max_used_memory_kb], N'54185.3109486887' AS [query_cost], N'86400' AS [timeout_sec], N'0' AS [resource_semaphore_id], N'4' AS [queue_id], N'0' AS [wait_order], N'1' AS [is_next_candidate], N'10075808' AS [wait_time_ms], N'274' AS [group_id], N'271' AS [pool_id], N'0' AS [is_small], N'174859272' AS [ideal_memory_kb] ) t;
SELECT [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], [group_id], [pool_id], [is_small], [ideal_memory_kb]
FROM #tmp_GridResults_1
DROP TABLE #tmp_GridResults_1
GO
February 2, 2015 at 6:37 am
Are you all patched up? I seem to recall a patch in a CU that may help with this issue. Start reviewing all the SP/CUs you don't have to see if anything matches up.
Also, do you have memory limits in your RG setup that could be at play here?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 2, 2015 at 7:20 am
TheSQLGuru (2/2/2015)
Are you all patched up? I seem to recall a patch in a CU that may help with this issue. Start reviewing all the SP/CUs you don't have to see if anything matches up.Also, do you have memory limits in your RG setup that could be at play here?
We are not all patched up, far from it. We are working on 11.00.3128 dated Jan 2013. I'll try and get this sorted ASAP.
We do have RG limiting the amount of mem that can be used but as shown in the data provided they queries should have enough mem to run.
February 2, 2015 at 7:47 am
I wonder if something funky with NUMA may not be at play then. There have been several NUMA-related hotfixes rolled out since your way-back-when-patch-date too! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply