Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the knowledge points of Sqlserver about the automatic creation and automatic updating of statistical information". In the daily operation, I believe that many people have doubts about the Sqlserver knowledge points about the automatic creation of automatic update of statistical information. The editor consulted all kinds of materials and sorted out simple and useful operation methods. I hope it will be helpful for you to answer the question of "what are the knowledge points for automatic creation of automatic updating of statistical information by Sqlserver"! Next, please follow the editor to study!

SSMS right-click the database-several options related to statistics will appear under the Properties--Options--Automatic, which correspond to the is_auto_create_stats_on,is_auto_create_stats_incremental_on,is_auto_update_stats_on,is_auto_update_stats_async_on fields of the sys.databases view.

AUTO_CREATE_STATISTICS

When the automatically create statistics option AUTO_CREATE_STATISTICS is ON, the query optimizer creates statistics on separate columns in the query predicate as needed to improve the cardinality estimate of the query plan. These single-column statistics are created on columns in existing statistics objects that do not have a histogram. The AUTO_CREATE_STATISTICS option does not determine whether statistics are created for the index. This option also does not generate filter statistics. It is strictly applied to the single-column statistics of the whole table.

When the query optimizer creates statistics by using the AUTO_CREATE_STATISTICS option, the statistics name starts with _ WA. You can use the following query to determine whether the query optimizer created statistics for query predicate columns.

SELECT OBJECT_NAME (s.object_id) AS object_name

COL_NAME (sc.object_id, sc.column_id) AS column_name

S.name AS statistics_name

FROM sys.stats AS s

INNER JOIN sys.stats_columns AS sc

ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id

WHERE s.name like'_ WA%'

ORDER BY s.name

AUTO_UPDATE_STATISTICS

When the automatic update statistics option AUTO_UPDATE_STATISTICS is ON, the query optimizer determines when the statistics may expire, and the query optimizer determines when the statistics may expire by calculating the number of data modifications since the last statistics update and comparing the number of changes to a certain threshold. The threshold is based on the number of rows in the table or indexed view.

SQL Server 2014 (12.x), SQL Server uses the threshold based on the percentage of changed rows. This is independent of the number of rows in the table. The threshold is:

If the table cardinality is 500 or less when evaluating time statistics, it is updated every time 500 changes are reached.

If the table cardinality is greater than 500 when evaluating time statistics, it is updated every time it reaches 20% of the number of 500 + modifications.

Starting with SQL Server 2016 (13.x), if the database compatibility level is 130, SQL Server updates the threshold with decreasing dynamic statistics, which is adjusted according to the number of rows in the table. It is calculated as the square root of the product of 1000 and the current table cardinality. For example, if the table contains 2 million rows, it is calculated as sqrt (1000 * 2000000) = 44721.359. After making this change, statistics for large tables will be updated more frequently. However, if the compatibility level of the database is lower than 130, the SQL Server 2014 (12.x) threshold applies.

AUTO_UPDATE_STATISTICS_ASYNC

The asynchronous statistics update option AUTO_UPDATE_STATISTICS_ASYNC determines whether the query optimizer uses synchronous or asynchronous statistics updates. By default, the asynchronous statistics update option is OFF status, and the query optimizer updates statistics synchronously.

At this point, the study of "what are the knowledge points of Sqlserver about the automatic creation of automatic updating of statistical information" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.