42、SQL Server 查询性能管理全解析

SQL Server 查询性能管理全解析

在 SQL Server 中，查询性能的管理至关重要，它直接影响到数据库的运行效率和应用程序的响应速度。下面将详细介绍一些常用的查询性能优化方法和资源管理策略。

1. 特定参数值优化

OPTIMIZE FOR 查询提示在 SQL Server 2005 中引入，允许为参数化查询指定查询优化器在创建执行计划时使用的值。SQL Server 2008 增强了该提示，增加了 UNKNOWN 选项，指示查询优化器在创建执行计划时使用统计信息而非提供的值。

当不同的参数值会创建不同的执行计划时， OPTIMIZE FOR 查询提示非常有用。例如，创建存储过程时，如果执行计划基于不常用的值进行缓存，可能会导致缓存的执行计划不佳。

以下是几个示例查询，展示了执行计划如何根据搜索值变化：

-- 生成使用合并连接执行计划的查询
USE AdventureWorks2008R2  
GO 
SELECT B.CustomerID 
FROM Sales.SalesOrderDetail A JOIN  
     Sales.SalesOrderHeader B 
ON A.SalesOrderID = B.SalesOrderID  
WHERE A.OrderQty = 1

此查询中，查询优化器选择合并连接，因为订单数量为 1 的行约有 75,000 行。

-- 生成使用哈希连接执行计划的查询
USE AdventureWorks2008R2  
GO 
SELECT B.CustomerID 
FROM Sales.SalesOrderDetail A JOIN  
     Sales.SalesOrderHeader B 
ON A.SalesOrderID = B.SalesOrderID  
WHERE A.OrderQty = 10

将 WHERE 子句中的值改为 10 后，由于订单数量为 10 的行只有 768 行，查询优化器使用哈希匹配来连接记录。

-- 使用 OPTIMIZE FOR 查询提示优化执行计划为值 1
USE AdventureWorks2008R2  
GO 
DECLARE @ID int =10 
SELECT B.CustomerID 
FROM Sales.SalesOrderDetail A JOIN  
     Sales.SalesOrderHeader B 
ON A.SalesOrderID = B.SalesOrderID  
WHERE A.OrderQty = @ID  
OPTION (OPTIMIZE FOR (@ID = 1))

此查询使用变量 @ID 并将其值设为 10，但使用 OPTIMIZE FOR 提示优化执行计划为值 1，执行计划现在包含合并连接。

-- 使用 OPTIMIZE FOR 查询提示优化执行计划为未知值
USE AdventureWorks2008R2  
GO 
DECLARE @ID2 int =1 
SELECT B.CustomerID 
FROM Sales.SalesOrderDetail A JOIN  
     Sales.SalesOrderHeader B 
ON A.SalesOrderID = B.SalesOrderID  
WHERE A.OrderQty = @ID2  
OPTION (OPTIMIZE FOR (@ID2 UNKNOWN))

此查询使用 OPTIMIZE FOR 提示的 UNKNOWN 选项，查询优化器根据统计信息选择哈希连接。

使用 OPTIMIZE FOR 查询提示时，要了解应用程序的使用模式，可能需要不时修改优化值，因为当前的最优参数一年后可能不再是最优的。

2. 强制索引查找

FORCESEEK 表提示在 SQL Server 2008 中引入，用于在执行计划中强制使用索引查找而非索引扫描。SQL Server 2008 R2 SP1 增强了该提示，允许使用索引参数。

查询优化器通常能很好地决定查找或扫描哪种更高效，但在某些情况下，可能需要覆盖优化器的决策。例如，使用 LIKE 和 IN 运算符进行基数估计不佳时，查询优化器可能选择索引扫描，而索引查找可能更高效。另一个原因可能是计划重用，初始执行计划基于扫描更高效的参数生成，而现在该计划多次用于查找更高效的参数。

-- 创建聚集索引扫描的查询
USE AdventureWorks2008R2  
GO 
SELECT * 
FROM HumanResources.Employee A JOIN 
    HumanResources.EmployeeDepartmentHistory B  
ON A.BusinessEntityID = B.BusinessEntityID  
WHERE A.BusinessEntityID > 270 OR 
     (A.BusinessEntityID < 10 AND B.DepartmentID =1)

此查询在 EmployeeDepartmentHistory 表上使用聚集索引扫描，然后使用合并连接将结果与 Employee 表组合。

-- 强制索引查找的查询
USE AdventureWorks2008R2  
GO 
SELECT * 
FROM HumanResources.Employee A JOIN 
     HumanResources.EmployeeDepartmentHistory B WITH (FORCESEEK)  
ON A.BusinessEntityID = B.BusinessEntityID  
WHERE A.BusinessEntityID > 270 OR 
     (A.BusinessEntityID < 10 and B.DepartmentID =1)

使用 FORCESEEK 表提示后，相同的查询在 EmployeeDepartmentHistory 表上使用聚集索引查找，结果使用嵌套循环与 Employee 表连接。

在考虑使用 FORCESEEK 表提示之前，应确保数据库统计信息是最新的。大多数情况下，查询优化器会为查询选择最佳执行计划，但在极少数情况下， FORCESEEK 表提示可能非常有用。

3. 强制索引扫描

FORCESCAN 表提示在 SQL Server 2008 R2 SP1 中引入，用于在执行计划中强制使用索引扫描。虽然通常认为扫描不好，但并非总是如此。有时 SQL Server 直接扫描索引更高效。

-- 强制索引扫描的查询
USE AdventureWorks2008R2  
GO 
SELECT * 
FROM HumanResources.Employee A WITH (FORCESCAN) JOIN 
     HumanResources.EmployeeDepartmentHistory B  
ON A.BusinessEntityID = B.BusinessEntityID  
WHERE A.BusinessEntityID > 270 OR 
     (A.BusinessEntityID < 10 and B.DepartmentID =1)

使用 FORCESCAN 表提示后，执行计划现在扫描 Employee 表。

使用所有影响执行计划的提示时，必须确保知道查询的最佳计划，并且知道不执行该最佳计划时性能会受影响。

4. 强制特定执行计划

计划强制提供了一种方法，可提供整个执行计划供 SQL Server 执行查询。当查询优化器无法为查询生成最优执行计划，但你知道有更好的执行计划时，计划强制很有用。

可以通过提供 USE PLAN 选项，以 XML 格式提供执行计划。例如：

SELECT *  
FROM Tablel JOIN  
     Table2 
ON Tablel.Column = Table2.Column  
OPTION (USE PLAN 'N <ShowPlanXML 
xmlns=”http://schemas.microsoft.com/sqlserver/2004/07/showplan”> 
</ShowPlanXML>')

要提供执行计划，必须先以 XML 格式捕获它。可以通过以下几种方式捕获 XML 执行计划：
- 使用 SHOWPLAN XML SET 语句返回 XML 执行计划而不运行查询：

SET SHOWPLAN_XML ON GO
SELECT * FROM Table  
SET SHOWPLAN_XML OFF  
GO

• 使用 STATISTICS XML SET 语句在查询结果集后返回 XML 执行计划：

SET STATISTICS XML ON GO
SELECT * FROM Table  
SET STATISTICS XML OFF  
GO

• 使用 sys.dm_exec_query_plan 动态管理函数中的 query_plan 列：

SELECT est.text, eqp.query_plan  
FROM sys.dm_exec_cached_plans ecp
  CROSS APPLY sys.dm_exec_query_plan(ecp.plan_handle) eqp 
  CROSS APPLY sys.dm_exec_sql_text(ecp.plan_handle) est

• 使用 SQL Server Profiler 中的 Showplan XML 、 Showplan XML Statistics Profile 和 Showplan XML For Query Compile 事件类。
• 使用 SQL Server Management Studio 中的 Display Estimated Execution Plan 和 Include Actual Execution Plan 选项，右键单击执行计划，从上下文菜单中选择 Show Execution Plan XML 。

如果 XML 执行计划无效，查询将失败。某些底层数据库架构更改可能导致执行计划无效。因此，在进行数据库更改后，测试所有带有 USE PLAN 查询提示的查询非常重要。使用 USE PLAN 查询提示时应格外谨慎，只有在尝试了其他选项（如创建适当的索引和更新统计信息）后才使用。

5. 通过计划指南添加提示

计划指南在 SQL Server 2005 中引入，允许在不手动更改查询的情况下为查询添加提示。许多时候，数据库会收到来自某些应用程序的查询，计划指南可向这些传入的应用程序查询提供提示，以生成更高效的查询计划。

SQL Server 2008 进一步增强了计划指南，允许将整个 XML 查询计划作为查询提示提供。与 USE PLAN 查询提示不同，使用计划指南提供的无效 XML 查询计划不会导致查询失败。

创建计划指南

创建新计划指南的步骤如下：
1. 展开要创建计划指南的数据库下的 Programmability 节点。
2. 右键单击 Plan Guides 文件夹，从上下文菜单中选择 New Plan Guide 以显示 New Plan Guide 对话框。

创建新计划指南时的可用选项如下：
| 选项 | 说明 |
| ---- | ---- |
| 名称 | 为计划指南输入描述性名称，如 PlanGuide-Test 。 |
| 语句 | 输入要应用计划指南的 T-SQL 语句，确保输入的 T-SQL 语句与发送到 SQL Server 的完全一致，即使额外的空格也会导致查询优化器忽略计划指南。 |
| 作用域类型 | 指定将 T-SQL 语句与计划指南匹配的上下文，有效值为 Object 、 Sql 和 Template 。 Object 是包含 T-SQL 语句的存储过程、标量函数、多语句表值函数或 T-SQL 数据操作语言 (DML) 触发器； Sql 表示 T-SQL 语句在独立语句或批处理的上下文中； Template 用于更改默认查询参数化行为。如果选择 Template ，只能使用 PARAMETERIZATION {FORCED | SIMPLE} 查询提示。 |
| 作用域批处理 | 输入包含要应用计划指南的语句的 T-SQL 批处理。如果留空，将使用 Statement 文本。 |
| 作用域架构名称 | 如果作用域类型为 Object ，输入包含对象的架构名称。 |
| 作用域对象名称 | 如果作用域类型为 Object ，输入包含要应用计划指南的 T-SQL 语句的对象名称。 |
| 参数 | 为 T-SQL 语句输入参数名称和数据类型，参数必须与使用 spexecutesql 语句时的显示方式完全一致。如果作用域类型为 Template ，则需要参数。 |
| 提示 | 输入要应用于 T-SQL 语句的查询提示或 XML 执行计划。例如，可以使用 FORCESEEK 表提示作为查询提示： OPTION (TABLE HINT (B, FORCESEEK)) 。 |

创建计划指南的最终 T-SQL 命令如下：

USE AdventureWorks2008R2  
GO 
EXEC sp_create_plan_guide 
     @name = N'[PlanGuide-Test]',  
     @stmt = N'SELECT *  
               FROM HumanResources.Employee A JOIN 
                    HumanResources.EmployeeDepartmentHistory B  
                      ON A.BusinessEntityID = B.BusinessEntityID  
               WHERE A.BusinessEntityID > 270 OR 
                    (A.BusinessEntityID < 10 AND B.DepartmentID =1)', 
     @type = N'SQL', 
     @hints = N'OPTION (TABLE HINT(B, FORCESEEK))'  
GO

可以查询 sys.planguides 目录视图以返回当前数据库中创建的每个计划指南的信息。

验证计划指南

由于计划指南不会导致查询失败，因此验证计划指南很重要。这在升级到 SQL Server 或更改数据库的底层数据结构后尤为必要。如果计划指南无效，查询优化器将忽略它并为查询生成新的执行计划。

可以使用 sys.fn_validate_plan_guide 函数验证计划指南，通过传递 sys.plan_guides 目录视图中的 plan_guide_id 。该函数将返回应用计划指南到查询时遇到的第一个错误消息。如果收到空结果集，则计划指南不包含任何错误。

USE AdventureWorks2008R2  
GO 
SELECT plan_guide_id, msgnum, severity, state, message 
FROM sys.plan_guides CROSS APPLY 
     sys.fn_validate_plan_guide(plan_guide_id)

可以使用 SQL Server Profiler 中位于 Performance 节点下的 Plan Guide Successful 和 Plan Guide Unsuccessful 事件来捕获查询优化器使用计划指南的有效和无效尝试。启动新的 Profiler 跟踪，选择 Show All Events ，展开 Performance 节点，选择 Plan Guide Successful 和 Plan Guide Unsuccessful ，然后选择 Run 开始跟踪。

6. 使用资源管理器管理资源

在某些情况下，查询可能已经尽可能高效地运行，但由于资源有限而运行缓慢。例如，备份作业或长时间运行的报告可能会消耗所有系统资源，从而减慢生产查询的速度。幸运的是，从 SQL Server 2008 开始，可以使用资源管理器限制这些资源密集型操作，以减少对环境的影响。

资源管理器在 SQL Server 2008 中作为企业版功能引入，可用于限制传入请求的资源消耗并对工作负载进行优先级排序，以维护更可预测的环境。目前，只能限制 CPU 和内存，不能限制 IO。资源管理器仅适用于数据库引擎，这意味着不能使用它来管理外部 SQL Server 进程，如分析服务、集成服务和报告服务。此外，资源管理器仅限于单个 SQL Server 实例，限制了在实例之间管理资源的能力。

资源管理器由三个主要组件组成：资源池、工作负载组和分类器函数。资源池用于划分 SQL Server 实例内可用的物理系统资源。工作负载组分配给资源池，用于将类似的查询分组，以便将它们视为一个单元。最后，分类器函数用于将传入请求分配到适当的工作负载组。

graph LR
    A[传入请求] --> B[分类器函数]
    B --> C{工作负载组}
    C --> D[资源池]

资源池

资源池基于配置资源管理器的 SQL Server 实例可用的内存和 CPU，而不是操作系统可用的总资源。资源池就像实例内的小型 SQL Server 实例，操作系统在 SQL Server 实例之间共享内存和 CPU 的方式与 SQL Server 实例在资源池之间共享内存和 CPU 的方式相同。

安装 SQL Server 时会创建两个资源池： internal 和 default 。 internal 资源池用于所有内部数据库引擎处理，不能修改该资源池，除了内部工作负载组外，任何工作负载组都无法访问它。此外， internal 资源池的 CPU 和内存从不受限，它会根据需要占用资源，其余资源池会相应地划分剩余的 CPU 和内存。 default 资源池是所有未分配到特定资源池的进程运行的地方。

可以创建用户定义的资源池，并设置 CPU 和内存的 Min 和 Max 属性，甚至可以设置 CPU 百分比的硬上限并指定调度程序关联。可以动态更改资源池的属性，新设置将应用于任何新的传入请求。

-- 创建备份资源池
CREATE RESOURCE POOL BackupPool 
WITH ( MIN_CPU_PERCENT = 0, 
       MAX_CPU_PERCENT = 20, 
       MIN_MEMORY_PERCENT = 0, 
       MAX_MEMORY_PERCENT = 20)  
GO 

-- 创建报告资源池
CREATE RESOURCE POOL ReportingPool 
WITH ( MIN_CPU_PERCENT = 0, 
       MAX_CPU_PERCENT = 25, 
       MIN_MEMORY_PERCENT = 0, 
       MAX_MEMORY_PERCENT = 25)  
GO 

-- 启用资源管理器
ALTER RESOURCE GOVERNOR RECONFIGURE 
GO

上述代码创建了 BackupPool 和 ReportingPool 资源池，并启用了资源管理器。将 BackupPool 的最大 CPU 和内存设置为 20%， ReportingPool 的最大 CPU 和内存设置为 25%。这意味着在资源紧张时， BackupPool 和 ReportingPool 平均只占用 45% 的可用资源，为 default 资源池中的所有其他请求留下 55% 的资源。

要禁用资源管理器，运行 ALTER RESOURCE GOVERNOR DISABLE 语句。

通过以上方法，可以有效地管理 SQL Server 的查询性能和资源使用，提高数据库的运行效率和稳定性。在实际应用中，应根据具体情况选择合适的优化方法和资源管理策略。

SQL Server 查询性能管理全解析

7. 资源池的详细配置与注意事项

在配置资源池时，有一些关键的细节和注意事项需要我们关注。

资源池的最小和最大设置有着严格的规则。CPU 和内存的最小设置总和不能超过 100%，因为这代表着对可用资源的保证分配，超过 100% 就意味着无法满足所有资源池的最小需求。而最大设置可以重叠，这是因为在资源充足时，资源池可以使用超过最大设置的资源，只有在资源紧张时，最大设置才会生效。例如，如果一个资源池的最大 CPU 设置为 20%，但在资源充足时，它可能会使用 80% 的 CPU，除非使用 CAP_CPU_PERCENT 参数设置了硬上限。

另外，资源管理器对通过专用管理员连接（DAC）发起的传入请求不适用。为了便于故障排除，建议在运行资源管理器的 SQL Server 实例上启用 DAC。

以下是一个总结资源池配置要点的表格：
| 配置项 | 说明 | 限制 |
| ---- | ---- | ---- |
| 最小 CPU 百分比 | 保证资源池始终拥有的 CPU 资源比例 | 所有资源池总和不超过 100% |
| 最大 CPU 百分比 | 资源池在资源紧张时可使用的最大 CPU 资源比例 | 大于最小设置且不超过 100%，可重叠 |
| 最小内存百分比 | 保证资源池始终拥有的内存资源比例 | 所有资源池总和不超过 100% |
| 最大内存百分比 | 资源池在资源紧张时可使用的最大内存资源比例 | 大于最小设置且不超过 100%，可重叠 |
| CAP_CPU_PERCENT | 设置 CPU 使用的硬上限 | - |

8. 工作负载组的作用与管理

工作负载组是资源管理器的重要组成部分，它的主要作用是将类似的查询分组，以便对这些查询进行统一的资源管理和优先级排序。

工作负载组与资源池紧密相关，每个工作负载组都被分配到一个特定的资源池。通过将查询分配到不同的工作负载组，可以根据查询的重要性、资源需求等因素，为不同的查询分配不同的资源。

例如，对于生产环境中的关键业务查询，可以将其分配到一个资源充足的工作负载组，以确保这些查询能够快速执行。而对于一些非关键的查询，如定期的统计报表查询，可以将其分配到一个资源相对较少的工作负载组。

创建工作负载组的语法如下：

-- 创建工作负载组
CREATE WORKLOAD GROUP MyWorkloadGroup
WITH (
    IMPORTANCE = MEDIUM,
    REQUEST_MAX_CPU_TIME_SEC = 0,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 20
)
USING BackupPool;
GO

在上述代码中，创建了一个名为 MyWorkloadGroup 的工作负载组，并将其分配到 BackupPool 资源池。同时，设置了该工作负载组的重要性为 MEDIUM ，每个请求的最大 CPU 时间为无限制（0 表示无限制），每个请求的最大内存授予百分比为 20%。

9. 分类器函数的实现与应用

分类器函数是资源管理器的核心组件之一，它的主要作用是根据传入请求的特征，将请求分配到合适的工作负载组。

分类器函数是一个用户定义的函数，它接受一个请求的上下文信息作为输入，并返回一个工作负载组的名称作为输出。通过编写自定义的分类器函数，可以根据不同的业务需求，灵活地对请求进行分类。

以下是一个简单的分类器函数示例：

-- 创建分类器函数
CREATE FUNCTION dbo.MyClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @WorkloadGroup sysname;
    -- 根据会话的应用程序名称进行分类
    IF APP_NAME() LIKE '%ProductionQuery%'
        SET @WorkloadGroup = 'ProductionWorkloadGroup';
    ELSE
        SET @WorkloadGroup = 'DefaultWorkloadGroup';
    RETURN @WorkloadGroup;
END;
GO

-- 将分类器函数应用到资源管理器
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.MyClassifier);
GO

在上述代码中，创建了一个名为 dbo.MyClassifier 的分类器函数。该函数根据会话的应用程序名称进行分类，如果应用程序名称包含 ProductionQuery ，则将请求分配到 ProductionWorkloadGroup 工作负载组，否则分配到 DefaultWorkloadGroup 工作负载组。然后，使用 ALTER RESOURCE GOVERNOR 语句将该分类器函数应用到资源管理器。

10. 综合应用案例分析

为了更好地理解如何综合运用上述方法来管理 SQL Server 的查询性能和资源使用，下面通过一个具体的案例进行分析。

假设我们有一个生产数据库，其中包含关键业务查询和定期的统计报表查询。关键业务查询对响应时间要求较高，而统计报表查询可以在资源相对空闲时执行。

首先，我们创建两个用户定义的资源池： CriticalPool 和 ReportingPool ，并设置相应的资源限制。

-- 创建关键业务资源池
CREATE RESOURCE POOL CriticalPool
WITH (
    MIN_CPU_PERCENT = 30,
    MAX_CPU_PERCENT = 60,
    MIN_MEMORY_PERCENT = 30,
    MAX_MEMORY_PERCENT = 60
);
GO

-- 创建统计报表资源池
CREATE RESOURCE POOL ReportingPool
WITH (
    MIN_CPU_PERCENT = 10,
    MAX_CPU_PERCENT = 20,
    MIN_MEMORY_PERCENT = 10,
    MAX_MEMORY_PERCENT = 20
);
GO

-- 启用资源管理器
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

然后，创建两个工作负载组： CriticalWorkloadGroup 和 ReportingWorkloadGroup ，并将它们分别分配到相应的资源池。

-- 创建关键业务工作负载组
CREATE WORKLOAD GROUP CriticalWorkloadGroup
WITH (
    IMPORTANCE = HIGH,
    REQUEST_MAX_CPU_TIME_SEC = 0,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 40
)
USING CriticalPool;
GO

-- 创建统计报表工作负载组
CREATE WORKLOAD GROUP ReportingWorkloadGroup
WITH (
    IMPORTANCE = LOW,
    REQUEST_MAX_CPU_TIME_SEC = 3600,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 10
)
USING ReportingPool;
GO

接着，创建一个分类器函数，根据查询的来源将请求分配到不同的工作负载组。

-- 创建分类器函数
CREATE FUNCTION dbo.MyClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @WorkloadGroup sysname;
    -- 根据会话的应用程序名称进行分类
    IF APP_NAME() LIKE '%CriticalQuery%'
        SET @WorkloadGroup = 'CriticalWorkloadGroup';
    ELSE
        SET @WorkloadGroup = 'ReportingWorkloadGroup';
    RETURN @WorkloadGroup;
END;
GO

-- 将分类器函数应用到资源管理器
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.MyClassifier);
GO

通过以上配置，关键业务查询将被分配到 CriticalWorkloadGroup 工作负载组，使用 CriticalPool 资源池，确保这些查询能够获得足够的资源。而统计报表查询将被分配到 ReportingWorkloadGroup 工作负载组，使用 ReportingPool 资源池，在资源空闲时执行。

11. 总结与最佳实践

在 SQL Server 中管理查询性能和资源使用是一个复杂而重要的任务。通过合理使用查询提示、计划指南和资源管理器等工具，可以显著提高数据库的运行效率和稳定性。

以下是一些最佳实践总结：
1. 查询优化 ：
- 了解应用程序的使用模式，合理使用 OPTIMIZE FOR 查询提示，根据不同的参数值优化执行计划。
- 在使用 FORCESEEK 和 FORCESCAN 表提示之前，确保数据库统计信息是最新的，并且只有在必要时才使用这些提示。
- 使用 USE PLAN 查询提示时要格外谨慎，先尝试其他优化方法，如创建适当的索引和更新统计信息。
2. 计划指南 ：
- 创建计划指南时，确保输入的 T-SQL 语句与发送到 SQL Server 的完全一致，避免因额外的空格等问题导致计划指南被忽略。
- 定期验证计划指南的有效性，特别是在升级 SQL Server 或更改数据库结构后。
3. 资源管理器 ：
- 根据业务需求合理配置资源池和工作负载组，确保关键业务查询能够获得足够的资源。
- 编写有效的分类器函数，根据请求的特征将请求分配到合适的工作负载组。
- 动态监控资源使用情况，根据实际情况调整资源池和工作负载组的配置。

通过遵循这些最佳实践，可以更好地管理 SQL Server 的查询性能和资源使用，为企业的业务系统提供稳定、高效的支持。