示例工程数据库教程后端【免费下载链接】sql-server-samplesAzure Data SQL Samples - Official Microsoft GitHub Repository containing code samples for SQL Server, Azure SQL, Azure Synapse, and Azure SQL Edge项目地址https://gitcode.com/gh_mirrors/sq/sql-server-samples点击查看免费下载导读本指南围绕 SQL Server 官方示例仓库中 samples/demos/Showplan 目录下的三个演示脚本展开讲解如何通过获取实际执行计划Actual Execution Plan观察查询执行计划中的三类经典警告Hash Spill哈希递归溢出、Sort Spill排序溢出与内存授予不足警告Memory Grant Warning。读完本文你将掌握每个演示的建库、建表、注入数据、执行查询的完整步骤并理解警告产生的原因、如何从执行计划属性中定位关键证据如Spill类型、MaxQueryMemory等以及相应的调优思路。一、演示准备理解仓库结构与运行前提该目录的 README.md 明确指出每个文件都包含以注释形式书写的运行说明务必注意脚本中的USE子句。如果某个演示需要 AdventureWorks 系列之外的专用数据库同目录下会提供对应的 Setup 脚本。这正是该演示包的设计原则——所有演示均面向 SQL Server 实际环境运行先建立正确数据库上下文再执行查询观察计划。目录下共包含 4 个文件文件演示主题HashWarning.sql参数嗅探导致的 Hash 递归溢出SortWarning.sql排序溢出Worktable 中的单趟排序MemoryGrant_Warning.sql内存授予警告与资源池限制MemoryGrant_Warning_Setup.sql为内存授予演示创建专用数据库memgrants并填充数据运行演示前请确认已安装 SQL Server Management StudioSSMS并连接到目标实例通过工具栏或快捷键如 CtrlM开启包括实际执行计划Include Actual Execution PlanHashWarning.sql默认使用AdventureWorks2016CTP3SortWarning.sql默认使用AdventureWorks2014若实例中数据库名称不同请自行修改USE子句或切换被注释的备选数据库。二、Hash Spill参数嗅探与哈希递归溢出2.1 演示脚本的核心流程HashWarning.sql 首先构造一张与Sales.Customer关联的辅助表人为制造数据分布严重倾斜的场景USE AdventureWorks2016CTP3 GO DROP TABLE CustomersState GO CREATE TABLE CustomersState (CustomerID int PRIMARY KEY, [Address] CHAR(200), [State] CHAR(2)) GO INSERT INTO CustomersState (CustomerID, [Address]) SELECT CustomerID, Address FROM Sales.Customer GO UPDATE CustomersState SET [State] NY WHERE CustomerID % 100 1 UPDATE CustomersState SET [State] WA WHERE CustomerID % 100 1 GO UPDATE STATISTICS CustomersState WITH FULLSCAN GO CREATE PROCEDURE CustomersByState State CHAR(2) AS BEGIN DECLARE CustomerID int SELECT CustomerID e.CustomerID FROM Sales.Customer e INNER JOIN CustomersState es ON e.CustomerID es.CustomerID WHERE es.[State] State OPTION (MAXDOP 1) END GO核心思路约 99% 的行标记为NY约 1% 的行标记为WA随后用UPDATE STATISTICS ... WITH FULLSCAN保证统计信息精确反映这种倾斜再封装成存储过程CustomersByState并用OPTION (MAXDOP 1)强制单线程执行从而让内存授予与溢出行为更容易被观察。2.2 复现参数嗅探脚本先清空过程缓存再执行存储过程DBCC FREEPROCCACHE GO EXEC CustomersByState WA -- 只选择 1% 的数据 GO EXEC CustomersByState NY -- 选择 99% 的数据 GO第一次以WA调用时优化器依据1% 数据量编译计划内存授予很小第二次以NY调用时复用了为WA编译的计划——这就是经典的参数嗅探Param Sniffing。面对 99% 的行小内存授予不足哈希连接构建输入无法全部装入内存从而触发溢出。2.3 观察溢出类型Recursion在 SSMS 中查看第二次执行的实际执行计划找到哈希匹配运算符Hash Match其属性中会显示溢出信息。脚本注释明确说明本例观察到的类型为Recursion递归溢出当构建输入build input无法装入可用内存时输入会被拆分为多个分区分别处理如果某个分区仍无法装入内存则继续拆分为子分区如此递归进行直到每个分区都能装入内存或达到最大递归级别。在本例中递归在第 1 级停止。需要区分两种哈希溢出类型Recursion递归构建输入按内存上限拆分后部分分区仍需再次拆分产生多个处理阶段Grace构建输入一次性拆分为多个分区每个分区都能独立装入内存只需一趟即可完成。两者都意味着内存授予不足但递归溢出通常代价更高。优化方向包括为哈希连接提供更大的内存授予如使用MIN_GRANT_PERCENT提示见下文、改善统计信息、或改写为嵌套循环连接等。三、Sort Spill排序溢出的单趟处理3.1 演示脚本与执行SortWarning.sql 通过强制使用旧版基数估计Cardinality Estimator来放大排序所需内存从而复现溢出USE AdventureWorks2014 GO DBCC FREEPROCCACHE GO SELECT * FROM Sales.SalesOrderDetail SOD INNER JOIN Production.Product P ON SOD.ProductID P.ProductID ORDER BY Style OPTION (QUERYTRACEON 9481) GO关键点OPTION (QUERYTRACEON 9481)将查询强制切换到SQL Server 2014 之前的旧基数估计器对Sales.SalesOrderDetail这种大表的行数估计偏差更大导致授予的内存小于实际排序所需ORDER BY Style需要对结果集进行显式排序Sort 运算符当授予内存不足以容纳全部排序数据时排序会借助 tempdb 中的Worktable完成。3.2 观察 Spill 1查看执行计划中 Sort 运算符的属性脚本注释明确指出应观察到的溢出类型观察 Spill 的类型 1意味着只需对数据做**一次遍历one pass**即可在 Worktable 中完成排序。Spill属性值含义1单趟溢出数据只被写出并重新读回一次代价相对可控2或更高多趟溢出数据被多次写出读回代价显著升高。与哈希溢出不同排序溢出的表现更直观地反映在Spill次数上。实际生产中若频繁出现Spill 2应重点关注排序所需内存是否被低估、是否应增加查询内存授予、或是否可以通过索引避免排序例如在排序列上建立覆盖索引。四、Memory Grant Warning内存授予不足与资源池限制4.1 专用数据库 Setup与其他演示不同本演示需要一个专用数据库memgrants由 MemoryGrant_Warning_Setup.sql 创建。脚本首先在master中检查并创建数据库USE [master] GO IF NOT EXISTS (SELECT name FROM sys.databases WHERE name Nmemgrants) CREATE DATABASE [memgrants] GO随后创建两张表并填充数据dbo.orders10,000,000 行三列均为intcol1为聚集主键dbo.orders_detail10,000 行其中col3为char(5000)的宽列并在(col1, col2)上建立唯一聚集索引od_cl_idx。两张表通过orders.col2 orders_detail.col1关联宽列char(5000)会显著增加排序/哈希过程中的内存压力是复现内存授予警告的关键设计。4.2 触发内存授予警告MemoryGrant_Warning.sql 的注释特别说明MIN_GRANT_PERCENT是专为 SQL Server 2014 SP2 与 2016 复现该场景而加入的因为该问题场景的修复正是包含在这两个版本中。查询如下USE [memgrants] GO DBCC FREEPROCCACHE GO SELECT o.col3, o.col2, d.col2 FROM orders o JOIN orders_detail d ON o.col2 d.col1 WHERE o.col3 8000 OPTION (LOOP JOIN, MAXDOP 1, MIN_GRANT_PERCENT 20) GO各提示的作用LOOP JOIN强制嵌套循环连接配合后续提示观察连接之外的内存需求MAXDOP 1单线程执行避免并行分摊内存MIN_GRANT_PERCENT 20要求内存授予至少占可用内存的 20%。当实际需求超过资源池允许的上限时授予会被截断产生警告。4.3 从 SELECT 节点属性中定位证据运行后在实际执行计划中打开SELECT 运算符的属性脚本注释给出两个关键属性MaxQueryMemory在MAX_MEMORY_PERCENT提示下查询在资源池Resource Governor中可用的最大内存授予MaxCompileMemory编译期间查询优化器在资源池中可用的最大内存KB。当计划上出现MemoryGrantWarning图标时说明授予内存低于优化器估计值可能与资源池限制、MIN_GRANT_PERCENT截断或统计信息偏差有关。可进一步在属性中对比RequestedMemory请求的内存与GrantedMemory实际授予的内存的差值确认是否被资源池上限截断。五、从警告到调优统一的诊断思路将三个演示串起来可以得到一套通用的执行计划警告诊断流程开启包括实际执行计划复现问题查询定位出现警告图标黄色三角形的运算符Hash Match、Sort 或 SELECT 节点读取关键属性Hash/Sort 溢出查看Spill类型Recursion/Grace与趟数1/2/…内存授予对比RequestedMemory与GrantedMemory并核对MaxQueryMemory、MaxCompileMemory是否受资源池限制结合DBCC FREEPROCCACHE后的首次/后续执行差异识别参数嗅探是否参与其中如 HashWarning 演示所示针对根因选择调优手段修正统计信息、调整内存授予提示如MIN_GRANT_PERCENT、增加索引避免排序、或使用查询提示改变连接/并行策略。六、延伸阅读本演示目录遵循仓库统一的演示规范每个脚本以注释说明运行方式、注意USE子句、专用数据库配套 Setup 脚本同样规范的目录还有 samples/demos/xEvents 与 samples/demos/LQS如需更深入的内存授予分析可参考 samples/demos/xEvents/MemoryGrant_XE_Demo.sql 中基于扩展事件的内存授予监控方式三个演示共同印证执行计划警告Hash Spill、Sort Spill、内存授予警告是查询性能问题最直观的信号掌握从执行计划属性中读取证据的能力是进行 SQL Server 查询调优的基本功。赞分享示例工程数据库教程后端【免费下载链接】sql-server-samplesAzure Data SQL Samples - Official Microsoft GitHub Repository containing code samples for SQL Server, Azure SQL, Azure Synapse, and Azure SQL Edge项目地址https://gitcode.com/gh_mirrors/sq/sql-server-samples点击查看免费下载相关推荐Nightingale 集成 SQL Server 监控Categraf 采集配置、权限授予与告警验证实战Nightingale 集成 SQL Server 监控Categraf 采集配置、权限授予与告警验证实战 Nightingale 通过内置的 SQL Ser后端运维观测告警可观测性人工智能AI AgentSQL Server 扩展事件性能诊断实战基于 sql-server-samples 的 Spill / Memory Grant / 查询剖析 XEvent 演示指南SQL Server 扩展事件性能诊断实战基于 sql server samples 的 Spill / Memory Grant / 查询剖析 XEvent示例工程数据库教程后端Apache DolphinScheduler 告警组件Alert使用指南告警插件、告警实例与告警组配置实战Apache DolphinScheduler 告警组件Alert使用指南告警插件、告警实例与告警组配置实战 Apache DolphinSchedule任务调度大数据后端前端创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考