semaphore性能问题_SQL Server性能调整– RESOURCE_SEMAPHORE等待

semaphore性能问题

When dealing with SQL Server performance tuning waits, we may see RESOURCE_SEMAPHORE waits along with other related monitoring that indicates memory as a possible pain point for our server (such as the below image that shows memory being one of the top waits overall).

在处理SQL Server性能优化等待时，我们可能会看到RESOURCE_SEMAPHORE等待以及其他相关监视，这些监视表明内存可能是服务器的痛点（例如下图，显示内存是总体上等待最多的之一）。

We should expect to see some waits like SOS_SCHEDULER_YIELD or CX_PACKET, but this wait in high numbers indicates that we want to investigate SQL Server and memory, assuming our server architecture is designed to handle the functionality.

我们应该期望看到一些等待，例如SOS_SCHEDULER_YIELD或CX_PACKET，但是这种等待数量很高，表明我们假设我们的服务器体系结构旨在处理该功能，因此我们希望研究SQL Server和内存。

For example, if we’re using a 4GB memory laptop to run an application server, SQL Server, and ETL processes along with other memory-intensive applications, our design doesn’t match our function. We won’t investigate designs like this and will assume our design matches our function.

例如，如果我们使用4GB内存的笔记本电脑来运行应用程序服务器，SQL Server和ETL进程以及其他占用大量内存的应用程序，则我们的设计与我们的功能不匹配。 我们不会调查这种设计，并会假设我们的设计与我们的功能相符。

正在发现RESOURCE_SEMAPHORE等待 (Uncovering RESOURCE_SEMAPHORE waits)

In the below query, before we do any SQL Server performance tuning, we look at the running queries to find four that return RESOURCE_SEMAPHORE waits (the query text is intentionally commented out):

在下面的查询中，在进行任何SQL Server性能调整之前，我们查看正在运行的查询以找到四个返回RESOURCE_SEMAPHORE等待的查询（查询文本被有意地注释掉）：

SELECT
  t1.[last_wait_type] LastWait
  --, t2.[text] QueryText
FROM sys.dm_exec_requests t1
    CROSS APPLY sys.dm_exec_sql_text(t.sql_handle) t2

We would only run queries like this if we don’t have any regular monitoring in place – otherwise, we would look at our regular monitoring for the cur