SQLServer统计信息以及如何在SQL中执⾏更新统计信息This article gives a walk-through of SQL Server Statistics and different methods to perform SQL Server Update Statistics.
本⽂介绍了SQL Server统计信息以及执⾏SQL Server更新统计信息的不同⽅法。
介绍 (Introduction)月鱼
SQL Server statistics are esntial for the query optimizer to prepare an optimized and cost-effective execution plan. The statistics provide distribution of column values to the query optimizer, and it helps SQL Server to estimate the number of rows (also known as cardinality). The query optimizer should be updated regularly. Improper statistics might mislead query optimizer to choo costly operators such as index scan over index ek and it might cau high CPU, memory and IO issues in SQL Server. We might also face blocking, deadlocks that eventually caus trouble to the underlying queries, resources.
SQL Server统计信息对于查询优化器准备优化的，具有成本效益的执⾏计划⾄关重要。 这些统计信息将列值分配给查询优化器，并帮助SQL Server估计⾏数（也称为基数）。 查询优化器应定期更新。 统计信息不正确可能会误导查询优化器选择昂贵的运算符，例如对索引查找进⾏索引扫描，并且可能导致SQL Server中的CPU，内存和IO问题过多。 我们还可能会遇到阻塞和死锁，这些死锁最终会给基础查
询和资源带来⿇烦。
查看SQL Server统计信息的选项 (Options to view SQL Server statistics)
We can view SQL Server statistics for an existing object using both SSMS and the T-SQL method.
我们可以使⽤SSMS和T-SQL⽅法查看现有对象SQL Server统计信息。
洗手日
SSMS查看SQL Server统计信息 (SSMS to view SQL Server Statistics)
Connect to a SQL Server instance in SSMS and expand the particular databa. Expand the object ( for example, HumanResources.Employee), and we can view all available statistics under the STATISTICS tab.
连接到SSMS中SQL Server实例并扩展特定的数据库。 展开对象（例如，HumanResources.Employee），我们可以在STATISTICS选项卡下查看所有可⽤的统计信息。
We can get details about any particular statistics as well. Right-click on the statistics and go to properties.
我们还可以获取有关任何特定统计信息的详细信息。 右键单击统计信息，然后转到属性。
It opens the statistics properties and shows the statistics columns and last update date for the particular statistics.
它打开统计信息属性，并显⽰统计信息列和特定统计信息的最后更新⽇期。
Click on the Details, and it shows the distribution of values and the frequency of each distinct value occurrence (histogram) for the specified object.
单击详细信息，它将显⽰指定对象的值分布以及每个不同值出现的频率（直⽅图）。
T-SQL查看SQL Server统计信息 (T-SQL to view SQL Server Statistics)
We can u DMV sys.dm_db_stats_properties to view the properties of statistics for a specified object in the current databa.
我们可以使⽤DMV sys.dm_db_stats_properties来查看当前数据库中指定对象的统计信息属性。
Execute the following query to check the statistics for HumanResources.Employee table.
执⾏以下查询以检查HumanResources.Employee表的统计信息。
SELECT sp.stats_id,
name,
filter_definition,
last_updated,
rows,
rows_sampled,
steps,
unfiltered_rows,
modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = OBJECT_ID('HumanResources.Employee');
Stats_ID: It is the unique ID of the statistics object Stats_ID：这是统计对象的唯⼀ID
Name: It is the statistics name 名称 ：这是统计名称
Last_updated: It is the date and time of the last statistics update Last_updated：这是上次统计信息更新的⽇期和时间Rows: It shows the total number of rows at the time of the last statistics update ⾏数：显⽰上次统计信息更新时的总⾏数
Rows_sampled: It gives the total number of sample rows for the statistics Rows_sampled：给出统计数据的样本⾏总数
Unfiltered_rows: In the screenshot, you can e both rows_sampled and unfiltered_rows value the same becau we did not u any filter in the statistics Unfiltered_rows：在屏幕截图中，您可以看到rows_sampled和unfiltered_rows值相同，因为我们没有在统计信息中使⽤任何过滤器
Modification_counter: It is a vital column to look. We get the total number of modifications since the last statistics update Modification_counter：这是⼀本⾄关重要的专栏。 ⾃上次统计信息更新以来，我们获得的修改总数
执⾏SQL Server更新统计信息的不同⽅法 (The different methods to perform SQL Server update Statistics)
SQL Server provides different methods at the databa level to update SQL Server Statistics.
SQL Server在数据库级别提供了不同的⽅法来更新SQL Server统计信息。美的意境
Right-click on the databa and go to properties. In the databa properties, we can view statistics options under the Automatic tab.
小猪呼噜噜右键单击数据库，然后转到属性。 在数据库属性中，我们可以在“⾃动”选项卡下查看统计信息选项。
接待英语⾃动创建统计 (Auto Create Statistics)
SQL Server automatically creates statistics on the individual columns for the query predicate to improve the cardinality estimate and prepare a cost-effective execution plan.
奶牛的英语SQL Server在查询谓词的各个列上⾃动创建统计信息，以改善基数估计并准备经济⾼效的执⾏计划。
Auto Create Statistics name starts with _WA
⾃动创建统计信息名称以_WA开头
U the following query to identify statistics auto-created by SQL Server.
使⽤以下查询标识由SQL Server⾃动创建的统计信息。
SELECT sp.stats_id,
name,
filter_definition,
last_updated,
rows,
rows_sampled,
steps,
unfiltered_rows,
modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = OBJECT_ID('HumanResources.Employee')
and name like '_WA%';
Auto-created SQL Server statistics are single column statistics
⾃动创建SQL Server统计信息是单列统计信息
SQL Server creates auto statistics for the columns that do not have a histogram for the existing statistics object
SQL Server为没有现有统计对象的直⽅图的列创建⾃动统计信息
⾃动创建增量统计 (Auto Create Incremental Statistics)
Starting from SQL Server 2014, we have a new option Auto-Create Incremental Statistics. SQL Server requires scanning the entire table for SQL Server update statistics, and it caus issues for th
e large tables. It is also valid for a table with partitioning as well. We can u this feature to update only the partition that we require to update. By default, Auto Create Incremental is disabled for individual databas. You can go through to get more knowledge on incremental statistics.
从SQL Server 2014开始，我们有⼀个新选项“ ⾃动创建增量统计信息”。 SQL Server要求扫描整个表以获取SQL Server更新统计信息，这会导致⼤型表出现问题。 对于具有分区的表也有效。 我们可以使⽤此功能仅更新我们需要更新的分区。 默认情况下，单个数据库禁⽤“⾃动创建增量”。 您可以阅读以获取有关增量统计的更多知识。
⾃动更新统计
四季悦温泉(Auto Update Statistics
)
We regularly perform DML operations such as inrt, update, delete and such operations change the data distribution or histogram value. Due to the operations, statistics might be out of date, and it might cau issues with the query optimizer efficiency. By default, the SQL Server databa has an option Auto Update Statistics true.
我们会定期执⾏DML操作，例如插⼊，更新，删除，此类操作会更改数据分布或直⽅图值。 由于这些操作，统计信息可能已过时，并且可能导致查询优化器效率出现问题。 默认情况下，SQL Server数据库的选项“⾃动更新统计信息为true”。
With this Auto Update Statistics option, query optimizer updates the SQL Server update statistics when the statistics are out of date. SQL Server us the following method to update statistics automatically.
使⽤此“⾃动更新统计信息”选项，当统计信息过时时，查询优化器将更新SQL Server更新统计信息。 SQL Server使⽤以下⽅法⾃动更新统计信息。
SQL Server 2014 or before
Number of rows at the time of statistics creation Auto Update Statistics
<=500Update statistics for every 500 modifications
>500Update statistics for every 500 + 20 percent modifications
SQL Server 2014或更早版本
创建统计信息时的⾏数⾃动更新统计
<= 500每500项修改更新统计信息
> 500每500 + 20％的修改更新统计信息
For the large tables, we require to update 20% of a row to auto-update the statistics. For example, a table with 1 million rows requires 20,000 rows updates. It might not be suitable for the query optimizer to generate an efficient execution plan. SQL Server 2016 onwards, it us dynamic statistics update threshold, and it adjusts automatically according to the number of rows in the table.
对于⼤表，我们需要更新⼀⾏的20％以⾃动更新统计信息。 例如，具有100万⾏的表需要20,000⾏更新。 查询优化器可能不适合⽣成有效的执⾏计划。 从SQL Server 2016起，它使⽤动态统计信息更新阈值，并根据表中的⾏数⾃动进⾏调整。
Threshold = √((1000)*Current table cardinality)百度创始人
阈值=√（（1000）*当前表基数）
For example, a table with one million rows we can u the formula to calculate the number of updates after which SQL Server will automatically update statistics.
例如，对于具有⼀百万⾏的表，我们可以使⽤公式来计算更新数量，之后SQL Server将⾃动更新统计信息。
Threshold = √(1000*1000000) = 31622
阈值=√（1000 * 1000000）= 31622
SQL Server updates the statistics after the approx. 31622 modifications in the object.
SQL Server在⼤约之后更新统计信息。 对象的31622修改。
Note: the databa compatibility level should be 130 or above to u this dynamic threshold statistics calculations.注意：数据库兼容性级别应为130或更⾼，才能使⽤此动态阈值统计信息计算。
异步⾃动更新统计信息 (Auto Update Statistics Asynchronously)
SQL Server us synchronous mode to update the statistics. If query optimizer finds out of date statistics, it updates the SQL Server Statistics first and then prepares the execution plan according to the recently updated statistics.
SQL Server使⽤同步模式来更新统计信息。 如果查询优化器发现过时的统计信息，它将⾸先更新SQL Server统计信息，然后根据最近更新的统计信息准备执⾏计划。
If we enable the Auto Update Statistics Asynchronously, SQL Server does not wait to update the statistics. Instead, it executes the query with existing statistics and initiates update statistics requests’ in parallel. The next executed query takes the benefit of the updated statistics. Since SQL Server does not wait for the updated statistics, we also call it Asynchronous mode statistics.
如果我们异步启⽤⾃动更新统计信息，则SQL Server不会等待更新统计信息。 ⽽是使⽤现有统计信息执⾏查询，并并⾏发起更新统计信息请求。 下⼀个执⾏的查询将利⽤更新的统计信息。 由于SQL Server不等待更新的统计信息，因此我们也将其称为异步模式统计信息。
⼿动更新统计信息 (Manually Update Statistics)
In the previous ction, we learned that SQL Server automatically updates the out-of-date statistics. We can also manually update the statistics for improving the query execution plan and performance on a requirement basis. We can u the UPDATE STATISTICS or Sp_Update stored procedure to update SQL Server statistics.
在上⼀节中，我们了解到SQL Server⾃动更新了过时的统计信息。 我们还可以⼿动更新统计信息，以根据需要改进查询执⾏计划和性能。我们可以使⽤UPDATE STATISTICS或Sp_Update存储过程来更新SQL Server统计信息。
Let’s u the UPDATE STATISTICS command and its various options to update SQL Server statistics.
让我们使⽤UPDATE STATISTICS命令及其各种选项来更新SQL Server统计信息。
