帮酷LOGO
  • 显示原文与译文双语对照的内容

问题描述

本工作的主要目的是提取生产SQL数据库中的典型工作负载。 根据典型工作负载,我们理解一组参数化的SQL语句( JDDJDMSELECT 或者函数调用),这些参数在监视期间在数据库中处理。 我们希望收集这样一组SQL语句和几个参数值示例,然后在监视过程中发生。 换句话说,如果在参数值中调用了with次的同一个SQL语句,我们将在典型的工作负载存储中存储一次。 典型工作负载可以用于单元测试目的,SQL命令性能调整和生产中使用的不同SQL语句范围。

在 SQL Server 中有一些工具可以收集特定时期的sql语句。 这些工具的问题是以这种方式记录的数据量可能不大,而且记录的数据需要进一步处理。 此外,使用这些工具可能并不容易。 本文描述了解决上述问题的工具。 工具可以自动收集执行SQL语句的信息,并在自定义SQL数据库中进行分析和存储所需的信息,避免不必要的或者重复的SQL语句。

要求

  • managementstudio 2012或者更低版本
  • 使用系统管理员角色登录到数据库

解决方案预览

图 1显示了解决方案的预览。 在本文的后面,我们将提出两种解决方案:

为了方便工作,在create中添加了 WorkLoad,这是在中添加的 #WorkLoad 中描述的。

解决问题的方法

用于 SQL Server Management Studio的工作负载( 2012或者更低)

我们已经创建了 SQL Server managementstudio,其中允许简单设置以下章节中提出的方法。 在中添加的工作负载添加在 #WorkLoad 中更详细地描述。

公用对象

我们对所有方法共享的解决方案的所有部分都使用 NAME"公用对象"。 这包括CLR存储过程 PG_WorkLoadPG_DeleteFile 和 CLR FN_IsFileExists

PG_WorkLoad 存储过程,处理在 #Processing SQL语句中描述的捕获SQL语句。 输入参数是需要处理的SQL语句文本。 处理SQL语句后,处理后的SQL语句存储在 #Workload 存储库中描述的典型工作负载表中。

SQL语句标准化

MODULE的主要功能是执行SQL语句规范化。 SQL语句的规范化是将SQL语句转换为标准化表单的过程,只有两个SQL语句相同。 这将消除由相同语义的不同语法生成的sql语句之间的差异。 这个 MODULE 是作为一个名为" PG_WorkLoad"的CLR存储过程创建的。 创建 MODULE 时,我使用了" gudusoft.gsqlparser.dll"库的试用版,该版本可以从官方网站下载。 这个版本的库有它的局限性。 如果任何人需要使用库的所有特性,你可以在 http://www.sqlparser.com/ 购买许可证。

典型的工作负载存储( )

在图 2中,我们可以以看到用作工作负载存储的表。 但是,在监控过程中,这些表被用作主存储单元,我们可以将数据导出/导入到XML文件中。 " TB_Query"表用于存储唯一的SQL语句及它的信息,例如SQL语句。数量。SQL语句的类型。参数和创建日期。 " TB_Param"表表示sql语句参数和信息,如SQL语句中的参数和顺序的值。 最后一个表" TB_QueryParam"表示带有创建日期日期的SQL语句的单独处理。 可以在一个处理步骤中获取每个SQL语句的参数。

可以从/导入表中存储的典型工作负载/导入到XML文件中。 在下面的清单中,你可以看到XML格式。

<?xmlversion="1.0"encoding="utf-8"?><workload><sqlcount="1"id="1"paramcount="1"text="SELECT * FROM AUKCE T0 WHERE T0.ID = @VAR1"type="dql"><values><p>10</p></values></sql><sqlcount="1"id="2"paramcount="0"text="SELECT @@TRANCOUNT"type="dql"><sqlcount="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语句。 执行SQL语句后,扩展事件将有关该语句的信息存储到文件中。
  • PG_ExtendedEventsJob - 过程分析从扩展事件文件读取的sql语句,必要时将它的存储在表中。
  • 作业 - 作业定期运行存储过程 PG_ExtendedEventsJob 。 在安装过程中,你可以选择自己的作业 NAME 。 创建的作业设置为每分钟运行一次。
  • SQL计划缓存 SQL计划缓存 store SQL数据库中SQL语句进程的查询计划的信息。 我们使用它来检测没有新的SQL命令被处理的情况。 在这种情况下,扩展事件文件分析是不必要的。
  • TB_SQLHash - 从SQL计划缓存中获取的SQL语句的哈希值的表。
  • Query1.xelQuery2.xel Extended扩展事件将原始SQL命令写入该文件。

动态管理视图基于( DMV )的方法

基于扩展事件的方法相比,这种解决方案更简单。更少,因为我们避免读取和分析大量的SQL语句。 另一方面,缺陷是捕获相同数量的不同SQL语句所需的时间长度。 可以将两个不同的DMVs用作SQL语句源:

  • sys.dm_exec_requests - 这是一个视图,其中存储了当前经过处理的SQL语句。
  • SQL计划缓存 ( 见前一章)

管道的选择是由 @FromCache 变量的值驱动的,我们可以以在后面的添加中轻松选择源管理器。 图 4描述了基于DMV的方法中使用的逻辑。

  • PG_RunningQueriesJob - 该过程收集来自DMV的SQL语句。 我们使用当前处理的SQL语句的管理单元( sys.dm_exec_requests ) 或者对应于SQL计划缓存的管理单元。
  • Job - 作业定期运行存储过程" PG_ExtendedEventsJob"。 在安装过程中,你可以选择自己的作业 NAME 。 每 10秒调用一次作业,如果我们使用 sys.dm_exec_requests 和SQL计划缓存,每 5分钟都会调用一次。
  • tmpStatementTab - 临时表,用于存储从DMV获取的SQL语句。
基于当前处理的sql语句的方法( sys 。dm_exec_requests )

在本例中,我们描述了读取 sys.dm_exec_requests的" PG_RunningQueriesJob"过程。 DMV的sql语句存储在存储过程中创建的" tmpStatementTab"临时表中。 有一个很小的机会,我们在一次点击新的。 由于这一事实,我们重复了DVM多次。 这将增加我们找到新SQL语句的概率,并且我们会更快地收敛到基于扩展事件的方法的结果。 重复次数设置为 30 。 如果成功率仍然很低,用户可以手动增加重复次数。 然后,过程将在临时使用" PG_WorkLoad"clr过程分析每个SQL语句,在工作负载存储中存储新的SQL 。

基于 SQL Server 计划缓存的方法

如果我们从 SQL Server 计划缓存读取sql语句,PG_RunningQueriesJob"过程有非常相似的逻辑。 主要区别是不需要重复计划缓存中的读取。 因此,过程只填充临时表,使用" PG_WorkLoad"clr过程处理分析,然后存储新的sql语句。 另一个区别是 SQL Server 计划缓存通常存储SQL命令参数化,因此,在工作负载存储中,我们没有SQL语句值。

在安装过程中添加了工作负载。

为简化安装过程中添加的过程,创建了安装应用程序中的工作量。 但是,有必要在不安装工作负载的情况下执行创建和操作基于事件的方法和管理员的方法。 脚本位于文件夹 installer/WorkLoadAddin/data 中。

安装程序是一个带有GUI的WinForms应用程序。 安装有以下步骤:

步骤 1

用户必须允许用户帐户控制( UAC ) 作为第一步,否则安装应用程序无法继续。 因为我们需要写入需要管理员权限的" Program Files"文件夹,所以UAC是必需的。 然后用户看到第一个窗口,如果他想继续,那么他需要点击"下一步"。

步骤 2

你可以选择安装目录,以及启动 Management Studio时运行的SSMS目录。 默认情况下,安装目录设置为" C:Program 文件( x86 ) WorkLoadAddin,因为这里路径对于任何 Windows 安装都是隐式的。

这里路径的默认设置为" C:ProgramDataMicrosoftMSEnvSharedAddins"。 以下是 SQL Server Management Studio所使用的所有 Add-Ins的所有XML文件。 单击"下一步"继续执行步骤 3 。

步骤 3

WorkLoad工作负载add的所有文件都将复制到" Program Files"中的" WorkLoadAddin"文件夹中。 XML文件复制到" C:ProgramDataMicrosoftMSEnvSharedAddins",最后,在配置文件中添加的工作负载将被复制到路径" C:UsersUserNameAppDataLocalWorkLoadAddin"。 复制完成且没有错误时,将在安装完成时添加工作负载。

卸载

步骤 1

在默认情况下,卸载程序存储在" C:Program 文件( x86 ) 中的安装路径中,或者在安装了工作负载的地方安装。

卸载启动后,将再次向用户显示 UAC,并且用户必须确认它是否继续。 确认UAC时,会出现一个窗口,其中选择卸载应用程序的选项。 点击"下一步"按钮将显示用户是否真的想要卸载。

步骤 2

在这里步骤中,将卸载工作负载添加。 卸载意味着从包含 SQL Server 管理Studio的所有 Add-Ins的文件夹中删除工作负载。 接下来,删除工作负载的配置设置,。 最后,删除" Program Files"中的文件夹。 删除上所有文件和文件夹后,卸载完成。

在 SQL Server Management Studio中的uninstall中添加工作负载之后,需要重新启动 SQL Server Management Studio 。 如果不重新启动它,并在"工具"选项卡中单击"工作负荷"将显示一条错误消息,指示不再存在这里类。

使用工作负载添加

可以在"工作负载在设置中添加"NAME 下安装后在"工具"菜单中找到工作负载添加。 在窗口中添加的工作负载可以在图中看到。 5.

让我们在下面的部分中解释工作负载中可用的函数。 我们从被称为公共函数的所有方法共享的函数开始,我们描述了每个方法的功能。

通用功能

安装/卸载"公用对象"

"公用对象"的安装是使用"安装"按钮来完成的,位于"公用对象"部分( 参见图中的图片) 。 5 ) 。这里安装执行" CreatePermissionAndAssembliesScript "脚本可以以启用与CLR存储过程的权限,并创建程序集以使用SQL数据库中CLR存储过程。 它还执行" CreateTableScript"脚本来创建存储典型工作负载的所有表。 最后,执行" CreateCLRObjectScript"脚本来创建CLR存储过程和函数。 所有脚本都在一个事务中执行。 如果事务期间发生错误,则所有更改将通过回滚取消整个事务,而发生的错误将显示给用户。 图1 中显示的公共对象部分中的状态。 5指示公共对象的安装是否已经完成或者未完成。 必须安装公共对象才能运行特定的方法( 基于扩展事件或者基于dmv的扩展事件) 。

卸载与安装类似。 唯一的变化是正在执行的脚本的名称。 在安装期间,脚本被称为" Create",在卸载过程中调用带有前缀" Delete"的脚本。 卸载按钮如图no所示。 5.

连接测试

如果你使用这里测试,你可以确定在给定的SQL数据库中输入的工作负载中输入的登录数据是否。 连接功能测试的按钮如图no所示。 5.

检查早期安装的功能。

在设置中添加的工作负载将保存在配置文件中。 因这里,在重新启动之后,在完成工作负载的工作之前,所有的事情都被设置。 工作负载增加后,可以确定配置文件当前安装了哪些安装。 根据这个,你可以看到安装状态,如图no所示。 5.但是,有人可以以手动更改SQL数据库,而工作负载添加的工作负载不明确。 例如,更改意味着手动重命名作业。扩展事件。存储过程和存储过程中的NAME 。 因这里,添加后会显示基于扩展事件的方法的信息,但是它可以能不是 true 。 所描述的每个安装都有自己的脚本存储在工作负载中。 在这个脚本中,sql语句用来检查该方法是否已经安装。 如果检查失败,则会出现代码出错的错误。 然后将这里代码与消息一起显示给用户。 启动工作负载添加时,将执行所有方法检查。

导出到文件

你可以从SQL数据库导出 SQL Server 典型工作负载到XML文件。 导出按钮如图no所示。 5.

XML文件必须具有特定的结构才能正确存储 SQL Server 典型工作负载。 XML中可以看到XML文件结构的外观。 要导出到XML文件,必须安装"公用对象"。

从文件中导入

你可以从XML文件导入 SQL Server 典型工作负载到SQL数据库。 Import使用 SQL Server 文件读取XML文件的原理,并将它的保存到SQL数据库中。 导入按钮如图no所示。 5.在保存SQL语句之前,检查SQL语句是否已经存在于SQL数据库中。 如果SQL语句已经存在,则只插入这里SQL语句的参数。 但是,如果SQL语句不存在,则会插入一个新的SQL语句及其参数。 XML文档结构必须遵循[ 清单中所描述的结构。 5 ] 如果结构不正确,将发生错误,并显示错误消息给用户。

扩展事件函数

图 6显示了在扩展事件方法中添加的工作负载的细节。 显然,我们选择了 combobox,其中我们选择了要配置和控制( 这里案例中的扩展事件)的特定方法。 我们通过点击"安装方法"按钮来安装这个方法。 脚本" CreatePG _ExtendedEventsJob"。" CreateExtendedEventsScript"和" CreateExtendedEventMethodJobScript"在安装过程中作为一个事务执行。 如果发生错误,则执行回滚并显示错误。 在安装之前,可以选择扩展事件文件的路径。 还可以指定作业的NAME 以及扩展事件本身。 安装后,就不能再更改路径或者扩展事件 NAME 。 安装完成后,可以使用按钮运行作业,也可以使用扩展事件捕获SQL语句。

启用/禁用作业是通过单击按钮"启用"( 请参见图 no 。 6 ) 。

通过单击按钮"开始"来启动/停止典型工作负载( 参见图 no 。 6 ) 。

这两个按钮可以以使用扩展事件将SQL语句捕获到文件中,但是不要立即执行作业分析。

通过单击"卸载方法"按钮完成卸载( 参见图 no 。 6 ) 。

DMV功能

按照与以前安装相同的方式执行DMV方法的安装。 唯一的区别是我们需要选择"2"。 方法( DVM )"组合框中的选项( 请参见图 no 。 7 ) 可以以选择作业 NAME,也可以以将当前正在运行的SQL语句或者 SQL Server 计划缓存作为主来源。 脚本的" CreatePG _RunningQueriesJob"和" CreateRunningQueriesMethodJobScript "在安装过程中作为一个事务执行。 安装方法后,只能启用/禁用这里方法或者卸载它。

测试

让我们描述一个性能测试,它是为了显示在有人使用我们的监视方法时可能出现的CPU开销。 显然,结果可以能不同于数据库工作负载,但是我们相信这些结果给出了一个典型的监视开销。 我们做了这个测试,因为我们知道性能是很重要的,我们想要认真调整我们的解决方案。

通过使用integrated处理器和集成显卡。8内存和 1硬盘,在笔记本上执行了测试。 Microsoft Windows 10操作系统安装在笔记本上。 包含SQL代理的is SQL Server 2016开发版( 版本 13.0.160 1.5 ) 用作数据库环境。 最后,使用 SQL Server Management Studio 2012 ( 版本 11.0.210 0.60 ) 在 SQL Server 中访问数据库。 Windows 性能监视器是 Windows 操作系统的一部分,用于监视系统资源。 计数器被称为"% 处理器时间",用于捕获CPU使用情况。

控制台应用程序被用来使工作负载,它向SQL数据库发送 11个不同的SQL 。 每个方法测试执行 5次,每个测量持续 10分钟。 每次测量时每秒发送 76,4个请求。 从每个运行中获得平均值,并计算每个方法的总平均值,如图no所示。 8.

如果我们比较图1 中所示的方法的结果。 8,我们可以观察到,CPU利用率的差异并不那么明显。

结束语

最后,我们想说,即使基于扩展事件的方法比它的他方法稍高,它在监视时捕获所有类型的SQL语句。 另一方面,基于管理的方法并没有捕获所有的方法,但是如果SQL语句通常更长时间运行。 此外,SQL计划缓存方法通常不提供参数值。

这些方法都有各自的优点和缺点,每种方法都适用于另一种情形。 这些方法的选择取决于使用的具体情况。 所有描述方法的主要优点是一个简单的安装和使用,因为有一个工作负载添加。

作者

这篇文章是由Bc创建的。 Radim Baca,Ph.D.




Copyright © 2011 HelpLib All rights reserved.    知识分享协议 京ICP备05059198号-3  |  如果智培  |  酷兔英语