SQL Server Resource_semaphore Wait status

Original: SQL Server Resource_semaphore wait state

Overview:

When a SQL Server instance is running slowly, you should do some checking, such as checking the wait state. The best approach is to build a performance baseline at the outset for performance comparisons. When you find that there is a memory pressure when compared to a performance baseline, find out what causes it. You can check the wait state of a transaction where Resource_semaphore waits can occur most. Here's how to deal with this problem:

After checking all the wait types of a transaction, you may find that resource_semaphore this wait type appears very much, which increases the IO wait for some pages. Because these transactions do not have enough memory to handle their operations, the IO waits on the page.

Resource_semaphore wait:

First, let's figure out what Resource_semaphore is waiting for. When SQL Server receives a user request (or a query). A compiled plan is created first, and then an execution plan is created on that basis. When SQL Server creates a compiled plan, it calculates two memory grant parameters, which becomes: request memory (required memories) and extra memory (additional memories)

Request memory is the minimum amount of memory required to run a sort and hash connection, which is called "request" because the query does not need to request this part of memory at the outset. The extra memory is the portion of memory that is needed to hold the temporary data into memory. If there is not enough memory, the data required for the query will be saved to the hard disk.

First, the server calculates the memory required to run a particular query. This section is usually equal to the sum of the requested memory and additional memory. However, when the instance uses parallel execution, the required memory is (request memory * parallelism) plus the sum of the extra memory. The server checks to see if there is enough memory to run each query, and then reduces the amount of extra memory, knowing that all the total memory demand is just up to the limit of memory. This partially modified memory becomes the demand memory (requested). Within SQL Server, there is a setting called resource semaphore, which is used to grant demand memory for querying. When the query does not get enough memory, it will change the waiting state to: Resource_semaphore. You can query from the sysprocesses system table or the sys.dm_exec_request DMV.

When Resource_semaphore accepts a new request, it first checks if a query is still waiting, and if it finds one, puts the new request in the FIFO queue, Resource semaphore attempts to grant memory to the query that is not waiting. This portion of memory may be the memory returned after the previous query has been executed. If enough memory is found, the memory is assigned to a query in the resource semaphore wait state to start running. If not enough, the query is placed in the wait queue and marked as Resource_semaphore wait. Therefore, looking at this wait state can find memory pressure.

Identify Resource_semaphore waits: Step 1:

Execute the following statement and filter the data that Resource_semaphore waits for:

SELECT  *from    sys. Sysprocesseswhere   lastwaittype = ' Resource_semaphore ' ORDER by lastwaittype

Because this situation is not good to simulate, so no.

Step 2:

From the results obtained in step 1, you may see many transactions in the Resourcesemaphore wait state, and you can now run the following statement to see the current state of the queries that have been allocated to memory, and the number of queries that have not been allocated memory. The DMV returns two rows, one for resource_semaphore_id 0, and the other for 1, where the small memory is less than 5M. Here you can get total grant memory and the total available memory on the instance. You can see that Grantee_count and Waiter_count,grantee_count are the total number of queries that have been allocated memory, and Waiter_count is the total number of queries waiting in the queue to grant memory:

SELECT  *from    sys.dm_exec_query_resource_semaphores

Step 3:

Then use DMV:sys.dm_exec_query_memory_grants to get the details of the memory needed for queries in the waiting queue. The Grant_time and granted_memory_kb of these queries may be null. You can also get plan_handle and sql_handle from this DMV:

SELECT  *from    sys.dm_exec_query_memory_grants

We want to focus on the following 3 columns:

Step 4:

Now you are going to find a query that centralizes memory consumption, and you can see all the required memory in the waiting query. When you see this part of the memory is too large, then find the plan_handle of these queries, and see their execution plan:

SELECT TOP Ten        *from    sys.dm_exec_query_memory_grants

Step 5: Copy the plan_handle data that you queried in step 4, and then execute:

SELECT * from Sys.dm_exec_sql_text (sql_handle)

Note Replace the sql_handle in parentheses. Then view its execution plan.

Summary: After you have found the memory-intensive queries through the steps above, you should adjust the statements so that they consume fewer resources. To resolve memory pressure.

SQL Server Resource_semaphore Wait status