数据库同步工具
sqlserver,Mysql数据同步软件

捕获SQL Server查询调整的持续时间指标

购买事宜请联系QQ:1793040

问题

请描述并演示使用getdate函数收集查询运行时间的框架。另外,说明如何计算不同查询或具有不同索引的同一查询的运行时间差异的统计显着性。

DBA通常要承担提高查询性能的任务。一种特别直观的调优查询方法是使用getdate函数。此函数返回运行SQL Server的计算机上的系统时间。您可以在此处 和 此处了解有关该功能的更多信息。

getdate函数与datediff函数一起使用可确定查询运行多长时间。通过将查询与另一个实现进行比较,只需在运行要调整的查询之前和之后立即调用并保存getdate函数的输出即可。然后,使用datediff函数评估查询运行所需的时间。通过将结果与另一个T-SQL代码示例或具有不同键或索引集的同一代码示例进行比较来完成调整。

如果您寻求一种简单的常识性方法来比较两个不同的T-SQL示例的运行时间,那么这很可能是您急需阅读的技巧。该技巧提供了一个框架,该框架用于使用getdate函数返回与T-SQL查询关联的运行时间。更具体地说,您将学习如何

  • 使用查询语句的运行时间填充表
  • 评估哪些索引提供最短的平均运行时间
  • 通过两种不同查询实现的统计测试运行时间进行对比

使用getdate函数获取查询运行时的框架

以下是由伪代码和注释组成的简短脚本,用于导出测试查询的一组运行时间。该脚本显示了一个框架,该框架可导出一组运行时间以多次调用测试查询。

  • 该代码以use语句开头,以指定默认的数据库上下文。
  • 接下来,dbcc dropcleanbuffers和checkpoint语句将保存数据缓冲区页,并清除在先前时间评估期间开发的所有未提交数据的数据缓冲区。这些语句使您可以使用冷数据缓冲区测试查询,而不必关闭并重新启动服务器。
  • while循环多次运行测试查询并执行其他有用的操作。
    • @ t0和@ t1的声明语句定义和填充与测试查询关联的开始和结束时间的局部变量。
    • 测试查询可以是可以修改数据的select语句或T-SQL语句。
    • 插入/选择语句从while循环中向表变量填充连续的查询运行时间。
  • 在while循环完成之后,其他代码将表变量的内容复制到一个更持久的数据容器中。
  • 该框架以代码结尾,以从while循环内部计算测试查询的运行时间的均值和方差。

-使用getdate函数记录的框架 
-并为测试查询保存多个运行时间 
-作为运行时间的平均值和方差
 
 指定默认数据库上下文的
 use 语句go 
 
-清理然后清除脏数据缓冲区
dbcc dropcleanbuffers ; 
检查点;
 
-使SQL Server准备就绪的T-SQL代码
-运行测试查询
 
-运行的while循环的声明 
-多次测试查询
 
-while循环可多次运行测试查询
 
而条件
    开始
      声明 @ T0 日期时间 =  GETDATE ()
      
      -测试查询
 
      声明 @ t1 datetime  =  getdate ()
 
      -插入/选择语句以保存在表变量中 
      -紧接(@ t0)之前和之后(@ t1)的时间 
      -运行测试查询以及之间的区别 
      -之前和之后
 
      -在while条件下增加循环计数器
 
   结束
 
-将表变量内容复制到临时或 
-永久表
 
-计算并显示查询运行时间的均值和方差
			

本技巧的测试数据库表

本技巧说明了如何为连接SQL Server数据库中两个表的查询收集一组运行时间。针对方案评估不同的键和索引,以评估它们对查询性能的影响。场景的测试查询中的一个表(yahoo_prices_valid_vols_only)是来自数据仓库的事实表,该表具有超过1400万行。第二个表(ExchangeSymbols)的行数少于一万。

以下屏幕快照显示了数据库,表以及基本配置中每个表中选定元素的对象资源管理器视图。

  • 项目1表示默认数据库名称(for_csv_from_python)。 
  • 第2项显示dbo模式中ExchangeSymbols表中的列名。ExchangeSymbols表在下面的默认配置中没有键也没有索引。
  • 第3项显示dbo模式中yahoo_prices_valid_vols_only表中的列名。该表在其默认配置中还具有三个键和一个索引。
  • 第4项显示yahoo_prices_valid_vols_only表中键的名称。
    • pk_symbol_date是主键的名称。按符号和日期列值,行是唯一的。
    • fk_date是一个外键,指向数据仓库内datedimension表中的行。
    • fk_symbol是一个外键,指向数据仓库内symboldimension表中的行。

交换符号

下一个屏幕快照显示了对yahoo_prices_valid_vols_only和ExchangeSymbols表的几个查询。

  • yahoo_prices_valid_vols_only事实表具有14620885行。
  • 事实表包含8089个不同的符号。一个符号可以在其股票交易的一组交易日期出现在多行上。
  • ExchangeSymbols表具有8856行。ExchangeSymbols表中的每个不同符号都有一行。结果,ExchangeSymbols表包含一些未出现在yahoo_prices_valid_vols_only事实表中的符号。这部分是由于两个表来自两个不同的组织的结果,而这两个组织并不总是遵循相同的命名规则来表示证券。
  • yahoo_prices_valid_vols_only事实表与ExchangeSymbols表的联接包含14620885行。

交换符号

使用三个不同的索引设置为一个查询计时

作为SQL Server DBA或开发人员,您可能会遇到以下情况:可能将多个不同索引集中的任何一个分配给查询语句中的一个或多个表。然后,有人问您能否确定哪组索引运行最快?在运行查询之前和之后比较由getdate函数返回的值的框架是回答此类问题的好工具。

本节演示如何使用getdate函数框架来评估具有三种不同键/索引配置的单个查询语句的均值和方差。第一种配置是针对for_csv_from_python数据库中yahoo_prices_valid_vols_only和ExchangeSymbols表的对象资源管理器中的上一个截屏的。

  • yahoo_prices_valid_vols_only表具有三个键。
    • 基于符号值内的日期值的名为pk_symbol_date的集群主键。
    • 指向symboldimension表中的symbol列的外键。
    • 指向datedimension表中date列的外键。
  • ExhangeSymbols表在其基本配置中没有键或索引,但是它确实包含一列符号值-其中许多与symboldimension表中的值重叠。

这是一个脚本,使用getdate框架返回查询的三十个运行时间的平均值和方差,以计算yahoo_prices_valid_vols_only和ExchangeSymbols表的内部联接中的行数。

  • 该脚本以一对注释行开头,以指示要为其评估运行时间的索引配置。
  • 接下来,一条use语句指定默认的数据库上下文。
  • 然后,一对语句(dbcc dropcleanbuffers和checkpoint)将所有脏数据缓冲区页复制到存储中并清空数据缓冲区页。
  • 下一个语句块重新初始化yahoo_prices_valid_vols_only表的pk_symbol_date和fk_symbol键。
  • 接下来,一对声明语句指定一些标量和表局部变量。
    • 标量局部变量(@ cnt,@ i和@itrs)通过while循环语句跟踪迭代。
      • @cnt被分配了多次运行的测试查询语句中的行数。
      • @itrs分配了一个值三十,它指定测试查询将运行三十次。
      • @i计算循环中的连续遍历。局部变量被初始化为零。对于循环中的每个连续遍历,其值增加一。
    • 第二条声明语句用于名为@query_run_results的本地表变量。该表变量有四列。
      • row_cnt,用于分配测试查询中的计数函数值。count函数从yahoo_prices_valid_vols_only和ExchangeSymbols表的内部联接返回行数。
      • start_time,用于在执行测试查询(@ t0)之前分配getdate函数的输出。
      • end_time,用于在执行测试查询(@ t1)之后分配getdate函数的输出。
      • diff_in_ms,用于分配datediff函数的输出,该函数计算@ t0和@ t1之间的毫秒差。
  • 然后,出现while循环代码。
    • 继续执行循环的条件是@i必须小于@itrs。
    • begin…end块标记了循环中要重复的代码的开始和结束。
      • 第一条语句将getdate函数值分配给@ t0。
      • 下一条语句是测试查询。请注意,选择列表将计数函数的值分配给@cnt局部变量。
      • 第三条语句将getdate函数值分配给@ t1。
      • 一条插入语句和一条选择语句后,会将@ cnt,@ t0,@ t1和datediff函数的当前值复制到@query_run_results表变量的新行中,该值用于将@ t0和@ t1之间的毫秒数差异。
      • begin…end块中的最后一条语句将@i的值加1。
  • while循环之后的第一段代码删除名为#pk_symbol_date_and_fk_symbol_date_time_runs的表的任何先前版本。然后,它在select语句中使用in子句将@query_run_results表变量中的行值复制到#pk_symbol_date_and_fk_symbol_date_time_runs中。通过指定目标表以将@query_run_results表变量的内容作为本地临时表,全局临时表或常规SQL Server表接收,可以控制表变量中运行时值的持久性和范围。
  • 以下脚本中的最后代码块计算#pk_symbol_date_and_fk_symbol_date_time_runs中运行时间的均值和方差。

-要评估的方案
-yahoo_prices_valid_vols_only的pk_symbol_date和fk_symbol
 
-设置默认数据库上下文
使用 for_csv_from_python
 go
 
-清理然后清除脏数据缓冲区
dbcc dropcleanbuffers ; 
检查点;
 
-重新初始化测试方案的密钥
-有条件地删除默认主键
如果(选择(OBJECT_ID ('pk_symbol_date' ))) 是 不是 空
   ALTER  表 DBO 。yahoo_prices_valid_vols_only 放置 约束 pk_symbol_date ;
 
-添加主键的新副本
更改 表 dbo 。yahoo_prices_valid_vols_only
    添加 约束 pk_symbol_date 主 键 聚类( Symbol , Date );
 
-有条件地删除名为fk_symbol的默认外键约束
如果(选择(OBJECT_ID ('fk_symbol' ))) 是 不是 空
   ALTER  表 DBO 。yahoo_prices_valid_vols_only 放置 约束 fk_symbol ;
 
-添加名为fk_symbol的默认外键约束的新副本
更改 表 dbo 。yahoo_prices_valid_vols_only
    添加 约束 fk_symbol 外 键(符号)
      引用 dbo 。在更新级联上删除级联上的 symboldimension ( Symbol ); 
        
        
 
-while循环的声明 
-并存储while循环的结果
声明 @cnt INT 
, @i 整数 = 0
 , @itrs 整数 = 30
 
声明 @query_run_results 表( row_count int , start_time datetime , end_time datetime , diff_in_ms int )
 
-while循环可多次运行查询
而 @i < @itrs
 
   开始
 
      -@ t0是查询运行之前的日期时间
      声明 @ t0 datetime  =  getdate ()
 
      -查询时间到这里
      从 [dbo]中选择 @cnt =  count (*) 
      。[yahoo_prices_valid_vols_only]
       内加入 [DBO] 。[
       yahoo_prices_valid_vols_only] 上的 [ExchangeSymbols] 。符号= [ExchangeSymbols] 。符号 
 
      -@ t1是查询运行之前的日期时间
      声明 @ t1 datetime  =  getdate ()
 
      -存储while循环迭代的结果
      插入 到 @query_run_results
       选择 
          @cnt 
         , @ t0 
          , @ t1 
          ,datediff ( ms , @ t0 , @ t1 )
 
      -递增@i以完成当前迭代
      设置 @i = @i + 1
 
   结束
 
-将运行时间从表变量转移到表以进行测试
-通过表变量为运行时间创建表
开始 尝试
   删除 表 #pk_symbol_date_and_fk_symbol_date_time_runs
 结束 尝试
开始 捕获
   打印 '#pk_symbol_date_and_fk_symbol_date_time_runs'  + 
         '不可删除。
结束 抓
 
选择 
    row_count 
   , START_TIME
    , END_TIME
    , diff_in_ms
 到 #pk_symbol_date_and_fk_symbol_date_time_runs
 从 @query_run_results
 
-计算多次运行的均值和方差
选择 
    平均( diff_in_ms ) [平均用于与pk_symbol_date和fk_symbol毫秒持续时间]
    ,VAR ( diff_in_ms ) [方差用于与pk_symbol_date和fk_symbol毫秒持续时间]
 从 #pk_symbol_date_and_fk_symbol_date_time_runs			

您可以轻松地修改前面的脚本来收集键和/或索引的其他配置的运行时间。本技巧介绍了另外两种情况:

  • 从默认配置中删除pk_symbol_date密钥的密钥
  • 另一个在从yahoo_prices_valid_vols_only表中删除其他键之后,将非集群列存储索引添加到ExchangeSymbols和yahoo_prices_valid_vols_only表中

每个方案的代码在很大程度上相似。因此,本技巧仅显示每种情况下的不同部分。本技巧的下载包括一个脚本文件,该脚本文件用于三个接一个的脚本。

这是从基本方案中删除pk_symbol_date键的方案的不同部分。省略号(…)标记了省略的相同代码块,这些代码块在各种情况下都是相同的。

  • 顶部的注释表示该脚本适用于删除名为pk_symbol_date的主键的方案。
  • 在注释之后,用省略号表示用于指定默认数据库上下文的use语句。
  • 接下来,有条件地还原pk_symbol_date主键,并删除并还原fk_symbol外键。
  • 在为场景重新配置键之后,用省略号表示要连续运行30次测试查询的代码。
  • 然后,下面显示的代码将计算并保存在省略的代码中的运行时间复制到#remove_pk_symbol_date_primay_key_date_time_runs。
  • 该脚本通过在#remove_pk_symbol_date_primay_key_date_time_runs中计算并显示查询运行时间的均值和方差来结束。

-要评估的方案
-删除yahoo_prices_valid_vols_only的pk_symbol_date主键

-重新初始化测试方案的密钥
-有条件地删除默认主键
如果(选择(OBJECT_ID ('pk_symbol_date' ))) 是 不是 空
   ALTER  表 DBO 。yahoo_prices_valid_vols_only 放置 约束 pk_symbol_date ;
 
-有条件地删除名为fk_symbol的默认外键约束
如果(选择(OBJECT_ID ('fk_symbol' ))) 是 不是 空
   ALTER  表 DBO 。yahoo_prices_valid_vols_only 放置 约束 fk_symbol ;
 
-添加名为fk_symbol的默认外键约束的新副本
更改 表 dbo 。yahoo_prices_valid_vols_only
    添加 约束 fk_symbol 外 键(符号)
      引用 dbo 。在更新级联上删除级联上的 symboldimension ( Symbol ); 
        
        
 
-将运行时间从表变量转移到表以进行测试
-通过表变量为运行时间创建表
开始 尝试
   删除 表 #remove_pk_symbol_date_primay_key_date_time_runs
 结束 尝试
开始 捕获
   打印 '#remove_pk_symbol_date_primay_key_date_time_runs'  + 
         '不可删除。
结束 抓
 
选择 
    row_count 
   , START_TIME
    , END_TIME
    , diff_in_ms
 到 #remove_pk_symbol_date_primay_key_date_time_runs
 从 @query_run_results
 
-计算多次运行的均值和方差
从 
     #remove_pk_symbol_date_primay_key_date_time_runs中选择avg ( diff_in_ms ) [带有删除pk_symbol_date primay键的以毫秒为单位的持续时间的平均值]
    ,var ( diff_in_ms ) [带有删除pk_symbol_date primay键的以毫秒为单位的持续时间的平均值]
 from #remove_pk_symbol_date_primay_key_date_time_runs			

这是针对使用ExchangeSymbols和yahoo_prices_valid_vols_only表的列存储索引而不是基本配置键中的索引的方案的不同代码块。省略号(…)标记了省略的相同代码块,这些代码块在各种情况下都是相同的。最值得注意的区别是用于添加非群集列存储索引的代码,该代码包含在本节中,其中包含有关为测试方案重新初始化键的标题注释。

  • 为ExchangeSymbols和yahoo_prices_valid_vols_only表添加了基于符号列值的非群集列存储索引。
  • 此外,pk_symbol_date和fk_symbol键都从yahoo_prices_valid_vols_only表中删除。

您还应该注意,测试方案的连续运行时间保存在名为#nonclustered_columnstore_indexes_date_time_runs的表中。除了这些更改之外,添加列存储索引的修改与删除主键的修改非常相似。

-要评估的方案
-非聚集列存储索引(ExchangeSymbols表)
-非集群列存储索引(yahoo_prices_valid_vols_only表)
 
-重新初始化测试方案的密钥
 
-有条件地删除名为fk_symbol的主键约束
如果(选择(OBJECT_ID ('pk_symbol_date' ))) 是 不是 空
   ALTER  表 DBO 。yahoo_prices_valid_vols_only 放置 约束 pk_symbol_date ;
 
-有条件地删除名为fk_symbol的外键约束
如果(选择(OBJECT_ID ('fk_symbol' ))) 是 不是 空
   ALTER  表 DBO 。yahoo_prices_valid_vols_only 放置 约束 fk_symbol ;
 
-创建第一个非聚集列存储索引
开始 尝试
在 [dbo] 上   创建 非集群列 存储 索引 [ix_ncl_cs_ExchangeSymbols_Symbol 
    ] 。[ExchangeSymbols] ( [Symbol] )与(drop_existing = off ,compression_delay = 0 )结束尝试开始在 [dbo] 上捕获   创建非聚集列存储索引 [ix_ncl_cs_ExchangeSymbols_Symbol
    ] 。[ExchangeSymbols] ( [Symbol] )与( 
       
 
 
    
   drop_existing  =  on , compression_delay  = 0 )
结束 捕获
 
-创建第二个非聚集列存储索引
开始 尝试
在 [dbo] 上   创建 非集群列 存储 索引 [ix_ncl_cs_yahoo_prices_valid_vols_only_Symbol_date 
    ] 。[yahoo_prices_valid_vols_only] ( [符号] , [日期] )与(DROP_EXISTING = 关,compression_delay = 0 )端尝试开始捕获   创建非聚集列存储索引 [ix_ncl_cs_yahoo_prices_valid_vols_only_Symbol_date] 
    上 [DBO] 。 
       
 
 
   [yahoo_prices_valid_vols_only] ( [Symbol] , [Date] )具有(drop_existing = on ,compression_delay = 0 )结束捕获 
       
 
 
-将运行时间从表变量转移到表以进行测试
 
-通过表变量为运行时间创建表
开始 尝试
   删除 表 #nonclustered_columnstore_indexes_date_time_runs
 结束 尝试
开始 捕获
   打印 '#nonclustered_columnstore_indexes_date_time_runs'  + 
         '不可删除。
结束 抓
 
选择 
    row_count 
   , START_TIME
    , END_TIME
    , diff_in_ms
 到 #nonclustered_columnstore_indexes_date_time_runs
 从 @query_run_results
 
-计算多次运行的均值和方差
选择 
    平均( diff_in_ms ) [平均用于与nonclustered_columnstore_indexes毫秒持续时间]
    ,VAR ( diff_in_ms ) [方差的持续时间在毫秒nonclustered_columnstore_indexes]
 从 #nonclustered_columnstore_indexes_date_time_runs
 
-诊断选择语句以查看连续的运行时间
-从#non_clustered_columnstore_index_time_runs中选择*
 
-删除非聚集索引
 [dbo] 上的下降索引 [ix_ncl_cs_ExchangeSymbols_Symbol ] 。[ExchangeSymbols]
 在 [dbo] 上的下降索引 [ix_ncl_cs_yahoo_prices_valid_vols_only_Symbol_date ] 。[yahoo_prices_valid_vols_only]
 转到 			

这是针对所有三种情况连续运行脚本的结果集。结果的第一行,第二行和第三行分别是基本索引配置,具有删除的主键的配置和具有非聚集列存储索引的配置。

这些结果很容易说明,与基本配置或删除了pk_symbol_date键的基本配置相比,列存储索引具有非常优越的性能(平均运行时间短得多)。

持续时间的平均值

评估两种均值之间差异的统计显着性

由于本技巧文章的代码示例同时计算了一个方案的运行时间平均值和方差,因此您可以评估一对方案之间的平均运行时间差异的统计显着性。Excel工作表的以下屏幕截图显示了如何使用基于非聚集列存储索引(ncs索引)与删除了pk_symbol_date键(wo_pk)的基本配置的三十次运行时间的均值和方差。

  • 单元格B2和C2中的数量分别是基于非聚集列存储索引和已删除pk_symbol_date键的基本配置键的运行时间平均值。
  • 单元格B4和C4中的数量分别是样本时间的方差,它们是基于非聚集列存储索引和已删除pk_symbol_date键的基本配置键的运行时间。Excel将小数点后的方差显示为六到四位,但是单元格中的实际数量与示例代码中的结果集完全匹配。
  • 均方误差(MSE)在单元格D4中。该数量等于(B4 + C4)/ 2。如您所见,它只是样本方差的平均值。
  • 单元格B6中的数量是均值之差的标准误差;有时称为合并标准偏差。您可以使用以下表达式进行计算:sqrt((2 * D4)/ B10)其中B10是每组(30)中的样本大小。
  • 在单元格B8中计算的组均值之差的t值等于D2 / B6。
  • 您可以将计算出的t值与选定的临界t值进行比较。关键值取决于概率水平,两组之间的样本大小之和减去2。运行时间值的每组样本大小为30,即B10中的数量。关键t值可从Internet以及专业的统计软件包中广泛获得。例如,使用此 链接来验证此技巧中报告的临界t值。
  • 以下电子表格显示了单元格B13和B14中单尾测试的关键t值。因为在单元格B8中计算出的t值超过了在单元格B14中的临界t值,所以均值之间的差异在统计学意义上显着超过0.01的显着性水平。

方差

下一步

T-SQL脚本和工作表分析位于本 技巧的下载文件中。下载文件包括:

  • 使用getdate函数返回与T-SQL查询关联的运行时间的框架
  • 用于在本技巧中讨论的三种情况下连续运行框架的T-SQL代码
  • 一个Excel工作簿文件,其中演示了如何使用两个方案的均值和方差来评估方案之间差异的统计显着性;但是,使用工作簿表达式可以确定任意两种情况之间均值差异的统计显着性

试用此技巧的代码示例。如果要使用本技巧中用于演示的数据来测试代码,则还需要运行本文中的脚本( 使用SQL Server为股票市场收集时间序列数据)   和本文的 时间序列数据事实和维度表中的脚本。 服务器。

当然,测试结果的最佳方法是使用自己的查询来查询组织内可用的数据。这种方法将使您能够为组织中的数据选择性能最佳的查询。

未经允许不得转载:数据库同步软件|Mysql数据同步软件|sqlserver数据库同步工具|异构同步 » 捕获SQL Server查询调整的持续时间指标

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

syncnavigator 8.6.2 企业版

联系我们联系我们