Why am I experiencing Resource Semaphore waits with this Resource Governor set up?

  • 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

  • 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

  • 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.

  • 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