SQL服务器统计技巧

迪鲁宾
392 阅读8分钟

在这篇文章中，我们将探讨SQL Server统计的一些内部工作原理。

什么是Cardinality Estimator（CE）？

Cardinality Estimator（CE）是SQL Server查询优化器的核心子单元，其职责是预测一个查询将返回多少行。这种预测的准确性直接影响到生成的查询计划的效率。因此，这种对查询计划的影响使CE对查询的性能具有决定性的作用。

SQL服务器的统计资料和Cardinality估算器

SQL Server的统计数据存储了列的数据分布，它们使用直方图来组织数据的分布。Cardinality估计器使用这些直方图来获得所需的分析数据，然后运行一个数学算法来估计一个查询可以返回多少行。为了更清楚地弄清楚cardinality估计器是如何进行这些预测的，我们将举出一些例子。然而，首先我们需要一个样本表，因此我们将通过以下查询创建一个样本表。这个表是Adventureworks数据库中SalesOrderHeader表的一个堆拷贝。

SELECT * FROM TempSalesOrder WHERE Freight=89.4568

谓词的含义是什么？

谓词定义了一个条件表达式，应用于表的行，以过滤查询的结果集。

单一谓词的例子。

在这第一个例子中，我们想只返回Freight列等于指定值的特定行。在执行查询之前，我们将启用实际的执行计划。

SELECT *  FROM TempSalesOrder WHERE Freight =89.4568

A basic query plan of a query

从查询的执行计划中可以看出，所有执行的估计行数被CE估计为1551行，这个估计是用一个特定的统计数字计算的。在执行计划的OptimizerStatsUsage属性下可以看到所使用的统计信息细节。

SQL Server query plan OptimizaerStatsUsage attribute

通过 dbcc show_statistics 命令，我们可以获得有关统计密度及其柱状图的详细信息。

Histogram of a SQL Server statistics

直方图数据EQ_ROWS列显示了与谓词有相同值的行的数量。直方图的第178行与谓词行匹配，估计的行数计算等于1551。

双重谓词的例子。

在这第二个例子中，我们将在查询中应用两个谓词。在这种情况下，cardinality估计器使用一个复杂的公式来计算估计行数。首先，我们将执行下面的查询并检查出估计的行数。

SELECT *  FROM TempSalesOrder WHERE Freight =89.4568

AND CurrencyRateId IS NULL

What is the meaning of Estimated Number of Rows for all Execution in SQL Server 我们可以看到，优化器计算出的估计行数是1154,49，这个估计是用以下公式计算出来的。

估计行数=（P0*P1^(1/2)*P2^(1/4)*P3^(1/8)）*卡

P()表达式表示谓词的选择性，Card表达式表示表格中的总行数。选择性的计算方法是用表的行数除以估计的行数。作为第一步，我们将使用柱状图来计算选择性。

DBCC SHOW_STATISTICS ('TempSalesOrder','_WA_Sys_00000016_027D5126')

Usage details of the DBCC SHOW_STATISTICS command

我们可以看到，估计的行数是1551，而表的总数量是315645。我们应用以下公式来计算第一个谓词的选择性。

P()= 估计的行数/表的总行数

P0= 1551 / 31565

P0= 0,049137

DBCC SHOW_STATISTICS ('TempSalesOrder','_WA_Sys_00000013_027D5126')

我们使用同样的公式来计算第二个谓词的选择性。

P1= 17489 / 31565

P1= 0,55406

**合并的选择性。**P0*P1=0,036575

**估计的行数=**31565*1154493

估计的行数**=**1154,493

正如我们所看到的，我们使用SQL Server的统计数据达到了估计的行数。然而，我们需要考虑到这个公式中的一点，在开始计算之前，我们需要将谓词的选择性排序为升序，然后应用这个公式。

P0<P1<P2<P3

多个谓词的例子。

在最后一个例子中，我们将在样本查询中添加三个谓词，我们将使用query_optimizer_estimate_cardinality事件。该事件可用于诊断cardinality估计问题。通过下面的查询，我们将创建一个事件会话并启动它。

CREATE EVENT SESSION [CaptureCEActivities] ON SERVER 
  ADD EVENT sqlserver.query_optimizer_estimate_cardinality(
      ACTION(sqlserver.sql_text)
      WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TempSalesOrder%')))
  ADD TARGET package0.event_file(SET filename=N'C:\temp1.xel',metadatafile=N'c:\temp1.xel1.xem')
  WITH (STARTUP_STATE=OFF)
  GO
  ALTER EVENT SESSION CaptureCEActivities ON SERVER   STATE = START

作为第二步，我们将执行以下查询。

  SELECT * FROM TempSalesOrder WHERE Freight =89.4568 
  AND CurrencyRateId IS NULL AND TerritoryID =4
  OPTION (RECOMPILE)

事件会话捕获了我们的查询，并返回关于CE内部的详细信息。当我们点击CalculatorList字段时，我们可以发现每个谓词的选择性。

首先，我们将对谓词的选择性进行排序，然后应用公式。

P0=0,049

P1=0,197

P2=0,554

估计的行数=0 ,049 * SQRT(0,197) * SQRT(SQRT(0,554))* 31565

估计的行数= 592,26

可以看出，我们发现的结果与查询返回的估计行数几乎相同。

SQL Server统计和Legacy Cardinality估算器

在软件中，开发遗留代码术语描述了从旧版本的软件中继承的代码。

微软在SQL Server 2014版本中对cardinality估算器的算法进行了彻底的改变，但是遗留的cardinality估算器仍然在SQL Server中启用。

我们可以在查询中使用FORCE_LEGACY_CARDINALITY_ESTIMATION提示来启用这个传统的cardinality估计器。当我们想在数据库层面上启用遗留的cardinality时，我们可以在我们的数据库中应用以下变化。

  ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

作为一个替代方法，我们可以在数据库范围内 的配置中启用遗留的cardinality**。**

然而，如果我们将这一改变应用于任何数据库，缓存的查询计划将被删除，如果我们没有严重的理由使用遗留估计器，使用FORCE_LEGACY_CARDINALITY_ESTIMATION提示将在查询层面更合理。使用遗留的cardinality估计器将改变估计的行数，因为遗留的估计行计算算法。我们将使用FORCE_LEGACY_CARDINALITY_ESTIMATION查询提示执行以下查询。

 SELECT *  FROM TempSalesOrder WHERE Freight =89.4568 
  AND CurrencyRateId IS NULL
  OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'),RECOMPILE)

我们可以看到，当我们使用传统的cardinality估计器时，估计的行数会发生变化。为了计算查询使用遗留的cardinality估计器时估计的行数，公式是。

估计的行数= P0*P1*P2*...*Pn*Card

估计行数 = 0,049137 * 0,55406 * 31565

估计的行数 = 859,3523

我们发现的结果与查询返回的估计行数完全匹配。

并行更新SQL Server的统计数据

从SQL Server 2016 SP2开始，当我们手动创建或更新统计数据时，可以使用MAXDOP提示。然而，当自动创建或更新统计操作时，SQL Server是如何表现的？找出这个问题的最佳选择是监控SQL Server自动创建SQL Server统计信息时在幕后进行的活动。为了演示这种情况，我们需要在样本表中删除所有自动创建的统计信息。

DROP STATISTICS dbo.TempSalesOrder._WA_Sys_0000000D_149C0161
GO
DROP STATISTICS dbo.TempSalesOrder._WA_Sys_00000013_149C0161
GO
DROP STATISTICS dbo.TempSalesOrder._WA_Sys_00000016_149C0161
GO

我们将创建一个事件会话来捕获SQL服务器所执行的活动。

CREATE EVENT SESSION [MonitorAutoCreatedStatistics] ON SERVER 
  ADD EVENT sqlserver.auto_stats(
      ACTION(sqlserver.sql_text)
      WHERE ([sqlserver].[session_id]=(125))),
  ADD EVENT sqlserver.object_created(
      ACTION(sqlserver.sql_text)
      WHERE ([sqlserver].[session_id]=(125))),
  ADD EVENT sqlserver.sp_statement_completed(
      ACTION(sqlserver.sql_text)
      WHERE ([sqlserver].[session_id]=(125))),
  ADD EVENT sqlserver.sql_statement_completed(
      ACTION(sqlserver.sql_text)
      WHERE ([sqlserver].[session_id]=(125)))
  WITH (STARTUP_STATE=OFF)
  GO
  
  ALTER EVENT SESSION MonitorAutoCreatedStatistics ON SERVER   STATE = START

在这最后一步中，我们执行我们的样本查询。在执行这个查询的过程中，SQL Server将创建三个单独的统计。

  SELECT * FROM TempSalesOrder WHERE Freight =89.4568 
  AND CurrencyRateId IS NULL AND TerritoryID =4
  OPTION (RECOMPILE)

执行查询后，让我们解释一下扩展的事件数据。

object_created事件显示我们已经创建了三个不同的SQL Server统计数据，这些统计数据是为查询中的每个谓词创建的。auto_stats事件表明这些统计数据在创建后被优化器使用。

StatMan是一个内部聚合函数，用于统计操作。我们可以在捕获的事件数据中看到这个函数的用法。

我们可以看到在使用Statman函数的查询末尾有MAXDOP提示，这让我们看到SQL Server的统计生成过程是以并行方式进行的。简而言之，我们可以说，自动创建和更新SQL Server统计信息的操作是可以并行进行的。

总结

对于查询优化器生成更有效的查询计划来说，SQL Server统计数据是极其关键的对象。而且，cardinality估计器是优化器的核心功能，所以理解cardinality估计器和统计数据的协同作用对更清楚地解释查询计划非常有帮助。

avatar
文章
阅读
粉丝