Asynchronous statistics update

It allows the Optimizer to run a query and then use the updated the statistics. Confused Yet, so now in English. When the Asynchronous setting is set the query will run like it is until all statistics its uses are up-to-date, then it will run with the new numbers.

It does not have to wait for all the new numbers to be updated to run. Remember every environment is different be sure to test this before implementing into production. A simple change from synchronous to asynchronous can make a difference. It is definitely something to add to your performance tuning tool belt. Thank you. These statistics are created on the key columns of the index. If the index is a filtered index, the Query Optimizer creates filtered statistics on the same subset of rows specified for the filtered index.

Instead, the Query Optimizer uses the default sampling algorithm to generate statistics. After upgrading a database with partitioned indexes, you may notice a difference in the histogram data for these indexes. This change in behavior may not affect query performance. These additional statistics can capture statistical correlations that the Query Optimizer does not account for when it creates statistics for indexes or single columns.

Your application might have additional statistical correlations in the table data that, if calculated into a statistics object, could enable the Query Optimizer to improve query plans. For example, filtered statistics on a subset of data rows or multicolumn statistics on query predicate columns might improve the query plan.

When a query predicate contains multiple columns that have cross-column relationships and dependencies, statistics on the multiple columns might improve the query plan. Statistics on multiple columns contain cross-column correlation statistics, called densities , that are not available in single-column statistics.

Densities can improve cardinality estimates when query results depend on data relationships among multiple columns. If the columns are already in the same index, the multicolumn statistics object already exists and it is not necessary to create it manually. It requires more system resources to maintain an index than a statistics object.

If the application does not require the multicolumn index, you can economize on system resources by creating the statistics object without creating the index. When creating multicolumn statistics, the order of the columns in the statistics object definition affects the effectiveness of densities for making cardinality estimates. The statistics object stores densities for each prefix of key columns in the statistics object definition.

For more information about densities, see Density section in this page. To create densities that are useful for cardinality estimates, the columns in the query predicate must match one of the prefixes of columns in the statistics object definition.

For example, the following example creates a multicolumn statistics object on the columns LastName , MiddleName , and FirstName. The density is not available for LastName, FirstName. If the query uses LastName and FirstName without using MiddleName , the density is not available for cardinality estimates.

When the Query Optimizer creates statistics for single columns and indexes, it creates the statistics for the values in all rows. When queries select from a subset of rows, and that subset of rows has a unique data distribution, filtered statistics can improve query plans.

For example, using AdventureWorks, each product in the Production. Product table belongs to one of four categories in the Production. Each of the categories has a different data distribution for weight: bike weights range from Using Bikes as an example, filtered statistics on all bike weights will provide more accurate statistics to the Query Optimizer and can improve the query plan quality compared with full-table statistics or nonexistent statistics on the Weight column.

The bike weight column is a good candidate for filtered statistics but not necessarily a good candidate for a filtered index if the number of weight lookups is relatively small.

The performance gain for lookups that a filtered index provides might not outweigh the additional maintenance and storage cost for adding a filtered index to the database. The following statement creates the BikeWeights filtered statistics on all of the subcategories for Bikes. The filtered predicate expression defines bikes by enumerating all of the bike subcategories with the comparison Production. The predicate cannot use the Bikes category name because it is stored in the Production.

ProductCategory table, and all columns in the filter expression must be in the same table. The Query Optimizer can use the BikeWeights filtered statistics to improve the query plan for the following query that selects all of the bikes that weigh more than If an error or other event prevents the Query Optimizer from creating statistics, the Query Optimizer creates the query plan without using statistics.

The Query Optimizer marks the statistics as missing and attempts to regenerate the statistics the next time the query is executed. Missing statistics are indicated as warnings table name in red text when the execution plan of a query is graphically displayed using SQL Server Management Studio. When statistics on a read-only database or read-only snapshot are missing or stale, the Database Engine creates and maintains temporary statistics in tempdb.

Scripts for the temporary statistics can be created and reproduced on a read-write database. Only SQL Server can create and update temporary statistics. However, you can delete temporary statistics and monitor statistics properties using the same tools that you use for permanent statistics:. Because temporary statistics are stored in tempdb , a restart of the SQL Server service causes all temporary statistics to disappear.

The Query Optimizer determines when statistics might be out-of-date and then updates them when they are needed for a query plan. Updating statistics ensures that queries compile with up-to-date statistics.

However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries.

The specific tradeoffs depend on your application. To determine when statistics were last updated, use the sys. It persists the updated statistics using the low-priority queue. It allows requests to compile queries with current statistics. Suppose the background request cannot acquire the lock. It gets a timeout that results in asynchronous statistics task abortion.

In this case, statistics need to be manually updated, or it needs to wait for another update statistics trigger. In this article, we explored SQL Server statistics configuration for automatic stats update asynchronously. The default configuration for async stats is disabled.

Therefore, each environment requirement is different; therefore, we cannot have general guidance for asynchronous statistics updates. However, it is not required in most OLTP environments. You must not enable it until you have special requirements. Always test the configuration on a lower environment, understand the impact and plan it on the production database.

Author Recent Posts. Rajendra Gupta. This improves concurrency for workloads with frequent query plan re compilations. By default, the SQL Server database engine updates statistics automatically as needed to reflect changes in the underlying data, and help the query optimizer generate more optimal plans. When a query plan is compiled, if existing statistics are considered out-of-date stale , new statistics are collected and written to the database metadata.

By default, this happens synchronously with query execution, therefore the time to collect and write new statistics is added to the execution time of the query being compiled. While up-to-date statistics often improve query plan quality, the extra time added to some query executions due to statistics update may be undesirable, particularly in transactional workloads with short queries, where updating statistics may take longer than query execution itself.

For this reason, the SQL Server database engine also supports an option to update statistics asynchronously. While asynchronous statistics update eliminates occasional spikes in query duration due to statistics update, it can sometimes cause a different, concurrency-related, problem, particularly for workloads where query plans are frequently compiled or recompiled.

The mechanism in play here is non-trivial, and deserves a closer look. When a query plan is compiled, the process places schema stability Sch-S locks on all statistics metadata objects used by the query optimizer, to ensure that they are not modified during compilation.

If, during compilation, the same or a different process finds that one of these statistics is stale, it will trigger an asynchronous statistics update, using a separate background process.

That process will scan data to build new statistics, and will then attempt to acquire a schema modification Sch-M lock on the statistics metadata object to update it in the database.

But this background process can get blocked by a query that is already holding the Sch-S lock on the same statistics metadata object, because its plan is being compiled at that time.

At this point, new queries being compiled may also need to use the same statistics and attempt to place Sch-S locks on the same object, but they would be blocked behind the background process waiting for the Sch-M lock. A blocking chain would form as the result, limiting application concurrency, as shown in Figure 1.

Figure 1. An illustration of a blocking chain forming when asynchronous statistics update is waiting for a Sch-M lock at normal priority. The impact of this blocking chain could be severe, especially with frequent re compilations and statistics updates. For some workloads we have seen, blocking would not resolve for several minutes.

This modifies the behavior of the background update statistics process to wait for the Sch-M lock at low priority. Low priority here means that this request will not acquire the Sch-M lock on the statistics metadata object until all other processes holding incompatible locks on the same object release their locks. Thus, the background process will not update statistics until all running queries finish compilation and release their Sch-S locks see Figure 2. The low priority mechanism used here is the same as what is used in several other DDL commands in SQL Server, such as online index rebuild and partition switching.

The example in the Appendix demonstrates this mechanism in action. Figure 2. An illustration of the absence of query blocking when asynchronous statistics update is waiting for Sch-M lock at low priority. But what if multiple concurrent query compilations continue for a long time?