典型 SQL Server 数据库工作负载的捕获
4.33/5 (2投票s)
典型 SQL Server 数据库工作负载的捕获
问题描述
这项工作的主要目的是从生产 SQL 数据库中提取典型工作负载。我们理解的典型工作负载是指在监控期间数据库中处理的一组参数化 SQL 语句（JDD、JDM、SELECT 或函数调用）。我们希望收集这样一组 SQL 语句，以及几个参数值示例和监控期间发生的近似调用次数，但我们希望避免 SQL 语句重复。换句话说，如果相同的 SQL 语句只因参数值略有差异而被调用百万次，我们希望只将此 SQL 命令存储一次在我们的典型工作负载存储中，并附带几个参数值示例。典型工作负载可用于单元测试目的、SQL 命令性能调优以及概述生产环境中使用的不同 SQL 语句的范围及其频率。
SQL Server 中有一些工具允许收集特定时期的 SQL 语句。这些工具的问题在于，以这种方式记录的数据量可能不少，并且记录的数据需要进一步处理。此外，使用这些工具可能并不容易。本文介绍了一种解决上述问题的工具。该工具可以自动收集有关已执行 SQL 语句的信息，对其进行分析，并将所需信息存储在自定义 SQL 数据库中，避免存储不必要或重复的 SQL 语句。
要求
- Management Studio 2012 或更低版本
- 以 sysadmin 服务器角色登录到数据库
解决方案预览
图 1 显示了解决方案的预览。本文稍后将提出两种解决方案
为了方便工作，创建了 WorkLoad 插件，详见 #WorkLoad 插件。
解决问题的方法
SQL Server Management Studio（2012 或更低版本）的工作负载插件
我们创建了一个 SQL Server Management Studio 插件，可以简单地设置后续章节中提出的方法。WorkLoad 插件的详细信息请参见 #WorkLoad 插件。
通用对象
我们将“通用对象”命名为解决方案中所有由两种方法共享的部分。这包括 CLR 存储过程 PG_WorkLoad、PG_DeleteFile 以及 CLR 函数 FN_IsFileExists。
PG_WorkLoad – CLR 存储过程，用于处理捕获的 SQL 语句，详见 #处理 SQL 语句。输入参数是需要处理的 SQL 语句文本。处理完 SQL 语句后，将处理后的 SQL 语句存储在 #工作负载存储 中描述的典型工作负载表中。
SQL 语句规范化
该模块的主要功能是执行 SQL 语句规范化。SQL 语句规范化是指将 SQL 语句转换为标准化形式的过程，在该形式中，只有当两个 SQL 语句具有相同的语义时，它们才被认为是相同的。这消除了由相同语义的不同语法生成的 SQL 语句之间的差异。该模块被创建为一个名为“PG_WorkLoad”的 CLR 存储过程。在创建该模块时，我使用了“gudusoft.gsqlparser.dll”库的试用版，可以从官方网站下载。此版本的库有其局限性。如果有人需要使用该库的所有功能，可以在 http://www.sqlparser.com/ 购买许可证。
典型工作负载存储
在图 2 中，我们可以看到用作工作负载存储的表。这些表在监控期间用作主要存储单元，但是，我们可以将数据导出/导入到/从 XML 文件中。TB_Query 表用于存储唯一的 SQL 语句及其信息，如 SQL 语句文本、出现次数、SQL 语句类型、参数数量和创建日期时间。“TB_Param”表代表 SQL 语句参数以及诸如参数值和 SQL 语句中的顺序之类的信息。最后一个表“TB_QueryParam”代表 SQL 语句的个体处理及其创建日期时间。在一次处理步骤中，可以为每个 SQL 语句获取其参数。
存储在表中的典型工作负载可以导出/导入到/从 XML 文件中。在下面的列表中，您可以看到 XML 格式。
<?xml version="1.0" encoding="utf-8"?>
<workload>
<sql count="1" id="1" paramcount="1"
text="SELECT * FROM AUKCE T0 WHERE T0.ID = @VAR1" type="dql">
<values>
<p>10</p>
</values>
</sql>
<sql count="1" id="2" paramcount="0"
text="SELECT @@TRANCOUNT" type="dql">
<sql count="2" id="3" paramcount="1"
text="SELECT T0.NAME
FROM SYS.DATABASE_PRINCIPALS T0 WHERE T0.TYPE = @VAR1"
type="dql">
<values>
<p>'A'</p>
</values>
<values>
<p>'R'</p>
</values>
</sql>
</workload>
FN_IsFileExists– CLR 存储函数，用于检查文件是否在磁盘上存在。输入参数是文件的路径。PG_DeleteFile– CLR 存储过程，用于从磁盘删除文件。输入参数是要删除的文件的路径。
基于扩展事件的方法
此方法结合使用了多种技术来捕获典型工作负载。图 3 包含该方法的主要逻辑。
- 扩展事件 – 扩展事件用于捕获 SQL Server 中执行的 SQL 语句。一旦执行 SQL 语句，扩展事件就会将有关它的信息存储到文件中。
PG_ExtendedEventsJob– 该过程分析从扩展事件文件中读取的 SQL 语句，并在必要时将其存储在表中。- 作业 – 作业定期运行存储过程
PG_ExtendedEventsJob。在安装过程中，您可以选择自己的作业名称。创建的作业设置为每分钟运行一次。 - SQL 计划缓存 – SQL 计划缓存存储有关 SQL 数据库中处理的 SQL 语句的查询计划的信息。我们使用它来检测没有处理新 SQL 命令的情况。在这种情况下，没有必要分析扩展事件文件。
- TB_SQLHash – 存储从 SQL 计划缓存获得的 SQL 语句哈希值的表。
- 文件 Query1.xel 和 Query2.xel – 扩展事件将原始 SQL 命令写入这些文件。
基于动态管理视图（DMV）的方法
与基于扩展事件的方法相比，此解决方案更简单，资源消耗更少，因为我们避免了读取和分析文件中可能存储的大量 SQL 语句。另一方面，缺点是捕获相同数量的不同 SQL 语句需要更长的时间。可以使用两个不同的 DMV 作为 SQL 语句源
sys.dm_exec_requests– 这是一个存储当前正在处理的 SQL 语句的视图。- SQL 计划缓存（参见上一章）
DMV 的选择由 @FromCache 变量的值驱动，我们可以在稍后介绍的插件中轻松选择源 DMV。图 4 描绘了在基于 DMV 的方法中使用到的逻辑。
PG_RunningQueriesJob– 该过程从 DMV 收集 SQL 语句。我们使用当前正在处理的 SQL 语句的 DMV（sys.dm_exec_requests）或对应于 SQL 计划缓存的 DMV。Job– 作业定期运行存储过程“PG_ExtendedEventsJob”。在安装过程中，您可以选择自己的作业名称。作业每 10 秒调用一次，如果我们分别使用sys.dm_exec_requests和 SQL 计划缓存，则每 5 分钟调用一次。tmpStatementTab- 用于存储从 DMV 获取的 SQL 语句的临时表。
基于当前正在处理的 SQL 语句的方法（sys.dm_exec_requests）
在这种情况下，我们描述了读取 sys.dm_exec_requests 的“PG_RunningQueriesJob”过程。来自 DMV 的 SQL 语句存储在存储过程中创建的“tmpStatementTab”临时表中。由于我们可能在同一时刻命中新的 SQL 语句的可能性非常小。因此，我们重复了几次 DVM 读取。这将增加找到新 SQL 语句的概率，并更快地收敛到基于扩展事件的方法的结果。重复次数设置为 30。如果成功率仍然很低，用户可以手动增加重复次数。然后，该过程使用“PG_WorkLoad”CLR 过程分析临时表中的每个 SQL 语句，并将新 SQL 语句存储在我们工作负载存储中。
基于 SQL Server 计划缓存的方法
如果从 SQL Server 计划缓存读取 SQL 语句，“PG_RunningQueriesJob”过程的逻辑非常相似。主要区别在于无需重复从计划缓存读取。因此，该过程仅填充临时表，使用“PG_WorkLoad”CLR 过程进行分析，然后存储新 SQL 语句。另一个区别是 SQL Server 计划缓存通常存储参数化 SQL 命令，因此在这种情况下，我们无法在工作负载存储中获取 SQL 语句的值。
WorkLoad 插件安装
Workload 插件安装应用程序旨在简化插件安装过程。值得一提的是，在不安装 WorkLoad 插件的情况下，也可以执行创建和操作基于扩展事件的方法和基于 DMV 的方法的脚本，但使用 Workload 插件会容易得多。脚本位于installer/WorkLoadAddin/data 文件夹中。
安装程序是一个带有 GUI 的 WinForms 应用程序。安装包含以下步骤
步骤 1
用户必须首先允许用户帐户控制（UAC），否则安装应用程序无法继续。UAC 是必需的，因为我们需要写入需要管理员权限的“Program Files”文件夹。然后用户会看到第一个窗口，如果他想继续，则需要单击“下一步”。
第二步
在此步骤中，您可以选择安装目录以及启动 SQL Server Management Studio 时运行的 SSMS 插件目录。默认情况下，安装目录设置为“C:\Program Files (x86)\WorkLoadAddin”，因为此路径是任何 Windows 安装的隐式路径。
此路径默认设置为“C:\ProgramData\Microsoft\MSEnvShared\Addins”。这里是 SQL Server Management Studio 使用的所有插件的所有 XML 文件。单击“下一步”继续第 3 步。
步骤 3
WorkLoad 插件使用的所有文件都将复制到“Program Files”中的“WorkLoadAddin”文件夹。XML 文件将复制到“C:\ProgramData\Microsoft\MSEnvShared\Addins”，最后，WorkLoad 插件配置文件将复制到路径“C:\Users\UserName\AppData\Local\WorkLoadAddin”。复制完成后且没有错误，WorkLoad 插件安装即完成。
Uninstall
步骤 1
卸载程序默认存储在安装路径“C:\Program Files (x86)\WorkLoadAddin\Uninstall.exe”或 WorkLoad 插件安装的任何位置。
启动卸载后，将再次向用户显示 UAC，并且用户必须确认才能继续。确认 UAC 后，将出现一个窗口，其中选择卸载应用程序的选项。单击“下一步”按钮将向用户显示他是否真的要卸载。
第二步
在此步骤中，将卸载 WorkLoad 插件。卸载意味着从包含 SQL Server Management Studio 所有插件的文件夹中删除 WorkLoad 插件。接下来，删除 WorkLoad 插件的配置设置。最后，删除“Program Files”中的文件夹。删除所有上述文件和文件夹后，卸载完成。
在 SQL Server Management Studio 中卸载 WorkLoad 插件后，您需要重新启动 SQL Server Management Studio。如果您不重新启动并单击“工具”选项卡中的“WorkLoad”，则会显示一条错误消息，表明此类插件已不存在。
使用 WorkLoad 插件
安装后，可以在“工具”菜单下的“WorkLoad 插件设置”中找到 WorkLoad 插件。WorkLoad 插件窗口如图 5 所示。
在接下来的章节中，我们将解释 Workload 插件中可用的功能。我们从所有方法共享的功能开始，在“通用函数”部分，并在后续章节中，我们将描述特定于每种方法的其他功能。
常用函数
安装/卸载“通用对象”
“通用对象”的安装是通过位于“通用对象”部分（见图 5）中的“安装”按钮完成的。此安装执行“CreatePermissionAndAssembliesScript”脚本以启用与 CLR 存储过程一起工作的权限，并创建用于与 SQL 数据库中的 CLR 存储过程一起工作的程序集。它还执行“CreateTableScript”脚本来创建用于存储典型工作负载的所有表。最后，执行“CreateCLRObjectScript”脚本来创建 CLR 存储过程和函数。所有脚本都在一个事务中执行。如果在事务期间发生错误，所有更改都将通过回滚取消，并将发生的错误显示给用户。图 5 中“通用对象”部分的状态指示通用对象是否安装完成。必须安装通用对象才能运行特定方法（基于扩展事件或基于 DMV）。
卸载与安装类似。唯一的变化是正在执行的脚本的名称。在安装过程中，脚本的名称是“Create”，而在卸载过程中则调用带有“Delete”前缀的脚本。卸载按钮显示在图 5 中。
连接测试
使用此测试，您可以确定在 WorkLoad 插件窗口中为给定 SQL 数据库输入的登录数据是否正确。连接功能测试按钮显示在图 5 中。
检查早期安装的功能
WorkLoad 插件设置保存在配置文件中。因此，重新启动后，一切都将设置为您完成 WorkLoad 插件工作之前的状态。WorkLoad 插件启动后，可以通过配置文件确定当前安装了哪些安装。根据此，您可以看到安装状态，如图 5 所示。但是，有人可能手动更改 SQL 数据库，而 WorkLoad 插件并不知道这些更改。例如，更改意味着手动重命名作业、扩展事件、存储过程，也包括更改我们的存储过程等等。因此，插件将显示已安装扩展事件方法的信息，尽管实际上可能并非如此。描述的每个安装都有其自己的脚本存储在 WorkLoad 插件数据中。在此脚本中，使用 SQL 语句来检查该方法是否已安装。如果检查失败，则会发生带有代码的错误。此代码以及消息随后会显示给用户。在 WorkLoad 插件启动时执行所有方法的检查。
导出到文件
您可以将 SQL Server 典型工作负载从 SQL 数据库导出到 XML 文件。导出按钮显示在图 5 中。
XML 文件必须具有特定的结构才能正确存储 SQL Server 典型工作负载。XML 文件结构的外观可以参见 [列表 1]。要导出到 XML 文件，需要安装“通用对象”。
从文件导入
您可以从 XML 文件将 SQL Server 典型工作负载导入到 SQL 数据库中。导入的原理是读取包含 SQL Server 典型工作负载的 XML 文件并将其保存到 SQL 数据库中。导入按钮显示在图 5 中。在保存 SQL 语句之前，会检查 SQL 语句是否已存在于 SQL 数据库中。如果 SQL 语句已存在，则只插入该 SQL 语句的参数。但是，如果 SQL 语句不存在，则会插入新的 SQL 语句及其参数。XML 文档的结构必须遵循 [列表 5] 中所示的结构。如果结构不正确，将发生错误并向用户显示错误消息。
扩展事件功能
图 6 显示了 Workload 插件的详细信息，该插件特定于扩展事件方法。显然，有一个 combobox，我们可以在其中选择要配置和控制的特定方法（在本例中为扩展事件）。通过单击“安装方法”按钮来安装此方法。脚本“CreatePG _ExtendedEventsJob”、“CreateExtendedEventsScript”和“CreateExtendedEventMethodJobScript”在安装过程中作为一个事务执行。如果发生错误，将执行回滚并显示错误。可以在安装前选择扩展事件文件的路径。还可以指定作业名称以及扩展事件本身。安装完成后，您可以使用这些按钮运行作业并使用扩展事件捕获 SQL 语句。
通过单击“开始”按钮来开始/停止将典型工作负载捕获到文件中（参见图 6）。
这两个按钮用于能够使用扩展事件将 SQL 语句捕获到文件中，但不会立即执行作业分析，而是将其留待以后处理。
卸载是通过单击“卸载方法”按钮完成的（参见图 6）。
DMV 功能
DMV 方法的安装与之前的安装方式相同。唯一的区别是我们需要在组合框中选择“2. 方法（DVM）”选项（参见图 7）。可以选择作业名称，以及是否希望使用当前正在运行的 SQL 语句或 SQL Server 计划缓存作为 SQL 语句的主要来源。脚本“CreatePG _RunningQueriesJob”和“CreateRunningQueriesMethodJobScript”在安装过程中作为一个事务执行。方法安装完成后，我们只能启用/禁用此方法或卸载它。
测试
下面我们介绍一下进行的性能测试，以展示使用我们的监控方法可能会产生的 CPU 开销。显然，结果可能因数据库工作负载而异，但是，我们相信这些结果能很好地说明典型的监控开销。我们进行此测试是因为我们意识到性能在生产环境中很重要，并且我们想表明我们仔细调整了解决方案，并且我们的方法的开销并不显著。
测试是在一台笔记本电脑上进行的，该笔记本电脑配备 2.0 GHz Intel Core i3 处理器、集成显卡、8 GB RAM 和 1000 GB HDD。笔记本电脑上安装了 Microsoft Windows 10 操作系统。Microsoft SQL Server 2016 Developer Edition（版本 13.0.1601.5），其中包含 SQL Agent，用作数据库环境。最后，使用了 Microsoft SQL Server Management Studio 2012（版本 11.0.2100.60）来访问 SQL Server 中的数据库。Windows 性能监视器，它是 Windows 操作系统的一部分，用于监视系统资源。使用名为“% Processor Time”的计数器来捕获 CPU 使用率。
如果我们比较图 8 中显示的方法结果，我们可以观察到 CPU 利用率的差异并不显著。
结论
最后，我们想说，尽管基于扩展事件的方法与其他方法相比开销略高，但它在监控期间捕获了所有类型的 SQL 语句。另一方面，基于 DMV 的方法并不捕获所有 SQL 语句，但如果 SQL 语句通常运行时间较长，它可能很有用。此外，SQL 计划缓存方法通常不提供参数值。
这意味着每种方法都有其优点和缺点，并且每种方法都适用于不同的情况。这些方法的选择取决于具体的应用场景。所有描述方法的共同优点是安装和使用简单，因为 WorkLoad 插件可以进行设置，并且所有方法都有最低的内存空间要求。
作者
本文由 Bc. Tomáš Bauer 和教授 Ing. Radim Baca, Ph.D. 创作。