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] QueryTextFROM 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 current waits or last waits along with a quick overview of the type of waits (seen in the first image). This helps us confirm what we’ll need to investigate.

如果我们没有任何常规的监视，我们将只运行这样的查询–否则，我们将查看常规监视中的当前等待或最后等待以及快速的等待类型概述（在第一个中看到）图片）。 这有助于我们确定需要调查的内容。

对查询等待进行故障排除 (Troubleshooting query waits)

Following the 80-20 principle with SQL Server performance tuning, we want to get fast wins as quickly as possible. In cases where our memory settings match our function, we may have the resources to extend memory on the server and solve this issue while reviewing costly queries or other performance optimizations. If we can’t wait on a solution and we have the capability of doing this, we should do it.

遵循80-20原则和SQL Server性能调整，我们希望尽快获得成功。 如果我们的内存设置与我们的功能匹配，则我们可能有资源来扩展服务器上的内存并解决此问题，同时查看昂贵的查询或其他性能优化。 如果我们迫不及待想要解决方案，并且我们有能力做到这一点，那么我们应该这样做。

记忆体设定 (Memory settings)

In the below image, we see that our test server, in this case, is set to use 80% of the available server’s memory and that we use the default setting for minimum memory per query used. In some cases, we may want to use up to 90% of the server’s memory if we have nothing else running on the server, or we may want to use much less if our server has other applications that are memory intensive.

在下图中，我们看到在这种情况下，我们的测试服务器被设置为使用可用服务器内存的80％，并且我们使用默认设置为每个查询使用的最小内存。 在某些情况下，如果我们在服务器上没有其他任何东西运行，我们可能希望使用最多90％的服务器内存，或者如果我们的服务器上还有其他占用大量内存的应用程序，那么我们可能希望使用更少的内存。

使用RESOURCE_SEMAPHORE等待的频繁查询的索引编制和适当的索引编制 (Indexing and appropriate indexing on frequent queries with RESOURCE_SEMAPHORE waits)

In addition to the above query that returns the last wait along with the query text, we can also review which queries are requesting memory for better SQL Server performance tuning.

除了上面的查询会返回最后的等待时间以及查询文本之外，我们还可以查看哪些查询正在请求内存以更好地调整SQL Server性能。

In the below query, I look at queries by their memory requests, status, along with the seconds before these terminate. According to Microsoft, the column requested_memory_kb is measured in kilobytes, so I convert it to megabytes for organizing the most expensive along with determining the ratio of requests to allotted memory.

在下面的查询中，我通过查询的内存请求，状态以及终止之前的秒数来查看查询。 根据Microsoft的说法， requested_memory_kb列以千字节为单位，因此我将其转换为兆字节，以便组织最昂贵的磁盘以及确定请求与分配内存的比率。

In addition, the grant_time tells us when the query was granted memory, but if it’s NULL, Microsoft points out this means the query is waiting on being granted memory. Although I have the query text commented out, I would review these queries if I see unusual activity based on the amount of memory requested:

另外， grant_time告诉我们何时向查询授予内存，但是如果它为NULL，Microsoft指出这意味着查询正在等待被授予内存。 尽管我的查询文本已被注释掉，但是如果我根据请求的内存量看到异常活动，我将审阅以下查询：

SELECT   ((t1.requested_memory_kb)/1024.00) MemoryRequestedMB  , CASE WHEN t1.grant_time IS NULL THEN 'Waiting' ELSE 'Granted' END AS RequestStatus  , t1.timeout_sec SecondsToTerminate  --, t2.[text] QueryTextFROM sys.dm_exec_query_memory_grants t1  CROSS APPLY sys.dm_exec_sql_text(t1.sql_handle) t2

For troubleshooting, I want to look at the queries that are requesting large memory grants and review their execution plans for costly sort, hash, or table scans. We may find tables without indexes or tables with inappropriate indexes for queries. In most cases, I’ve found SQL Server performance tuning for this wait with indexing – new indexes or additional indexes. In rarer situations, I’ve found the statistics are completely out of date.

对于故障排除，我想查看请求大内存授予的查询，并查看其执行计划以进行昂贵的排序，哈希或表扫描。 我们可能会发现没有索引的表或具有不合适的索引的表以进行查询。 在大多数情况下，我发现使用索引（新索引或其他索引）来进行此等待SQL Server性能调整。 在极少数情况下，我发现统计信息完全过时了。

集群列存储索引 (Clustered columnstore indexes)

If we see frequent queries that are performing large aggregates on tables (80-100% of the entire table) with scheduled loads, we may want to review our indexing, if we’re not using clustered columnstore indexes on these tables. While loading tables with clustered columnstore indexes can add memory overhead (thus, we can drop before a load and add these after a load), these will reduce our memory footprint in queries that perform large aggregates on tables.

如果我们看到频繁查询在具有计划负载的表上执行大型聚合（占整个表的80-100％），那么如果我们不在这些表上使用聚集的列存储索引，则可能需要查看索引。 虽然使用群集的列存储索引加载表可能会增加内存开销（因此，我们可以在加载之前删除，而在加载之后添加），但这会减少在对表执行大型聚合的查询中的内存占用。

Notice the impact of the SQL Server performance tuning involving index types in the below example where I run an aggregate query against two identical tables of data with one having a non-clustered index on price (nonCCI table) and the other having a clustered columnstore index on the entire table (CCI table). The clustered columnstore index table sees better statistically significant results in performance:

请注意以下示例中涉及索引类型SQL Server性能调整的影响，在该示例中，我对两个相同的数据表运行聚合查询，其中一个表的价格具有非聚集索引（nonCCI表），另一个表具有聚集的列存储索引在整个表（CCI表）上。 群集的列存储索引表在性能上具有更好的统计意义的结果：

SET STATISTICS IO ON  SELECT SUM(Price), AVG(Price)FROM nonCCI SELECT SUM(Price), AVG(Price)FROM CCI SET STATISTICS IO OFF

Table ‘nonCCI’. Scan count 1, logical reads 1120, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

表“ nonCCI”。 扫描计数1，逻辑读1120，物理读0，预读0，lob逻辑读0，lob物理读0，lob预读0。

Table ‘CCI’. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 4, lob physical reads 0, lob read-ahead reads 0.

表“ CCI”。 扫描计数1，逻辑读为0，物理读为0，预读为0，lob逻辑读为4，lob物理读为0，lob预读为0。

Table ‘CCI’. Segment reads 1, segment skipped 0.

表“ CCI”。 段读为1，段跳过了0。

Aggregate queries are seldom the problem here with this wait, but reducing an aggregate query’s memory impact when they do run will help us with other queries by freeing up resources. If we can reduce resource use in general, we should take every opportunity to do so. For SQL Server performance tuning, we’ll generally want these clustered columnstore indexes in OLAP designs or environments, whereas we’ll want to avoid them in tables with high data write and read volume throughout the day.

聚合查询在这种等待中很少出现问题，但是减少聚合查询运行时对内存的影响将通过释放资源来帮助我们进行其他查询。 如果我们总体上可以减少资源使用，我们应该抓住一切机会这样做。 为了进行SQL Server性能调整，我们通常希望在OLAP设计或环境中使用这些群集的列存储索引，而我们希望避免在一整天具有较高数据读写量的表中使用它们。

其他等待可能会使问题复杂化 (Other waits that may compound problems)

In some cases, we may experience a compounded problem with this wait affecting our queries because of other waits that are also causing delays. In addition to memory pressure, we may be waiting on a response from another server (ASYNC_NETWORK_IO) or on a log bottleneck (WRITE_LOG). If we identify these other weaknesses, we’ll also want to improve the performance of these other servers, disks, or bottlenecks.

在某些情况下，此等待可能会影响到我们的查询，因为其他等待也会导致延迟，因此可能会遇到一个复杂的问题。 除了内存压力外，我们可能还在等待其他服务器的响应（ASYNC_NETWORK_IO）或日志瓶颈（WRITE_LOG）。 如果我们发现了其他弱点，我们还将希望提高其他服务器，磁盘或瓶颈的性能。

混合环境 (Hybrid environments)

In most environments today, we run both OLAP and OLTP designs. These hybrid environments will continue to become more frequent, and we must remember that the type of querying will differ. The best design for SQL Server performance tuning is to keep OLAP and OLTP environments demarcated on different servers if we have the resources to do so. This will help us optimize for each environment.

在当今的大多数环境中，我们同时运行OLAP和OLTP设计。 这些混合环境将继续变得更加频繁，并且我们必须记住，查询的类型将有所不同。 SQL Server性能调整的最佳设计是，如果我们有资源的话，请在不同服务器上划分OLAP和OLTP环境。 这将帮助我们针对每种环境进行优化。

摘要 (Summary)

Let’s consider that we’ve looked at the SQL Server performance tuning of the RESOURCE_SEMAPHORE waits on a server where we shouldn’t see this wait. If we are running SQL Server from a limited machine with a web application on top of it along with other memory-intensive applications, it would be unusual not to see this wait. Likewise, if we have a server with very little memory relative to our data and query load, it would also be unusual to not see this wait. Adding more resources may not be the preferred solution, but it may be the most appropriate solution. Also, we may use this as a chance to improve our queries through improved indexing and type of indexing.

让我们考虑一下，我们已经研究了在不应该看到此等待的服务器上对RESOURCE_SEMAPHORE等待SQL Server性能调整。 如果我们从一台有限的机器上运行SQL Server，并在上面运行一个Web应用程序以及其他占用大量内存的应用程序，那么通常不会看到这种等待。 同样，如果我们的服务器相对于数据和查询负载而言内存很少，那么不等待该等待时间也很不寻常。 添加更多资源可能不是首选解决方案，但可能是最合适的解决方案。 另外，我们可以借此机会通过改进索引编制和索引类型来改善查询。

翻译自:

semaphore性能问题