Statistical Maintenance < Fourth >

SQL Server allows users to manually control statistical maintenance in a separate database. The configuration of the 4 main control copper statistics maintenance for SQL Server is as follows:

• New statistics on columns without indexes (auto-create statistics);
• Update existing statistics (Automatic Update statistics);
• The sampling degree used to collect statistics;
• Asynchronous update of existing statistics (automatic asynchronous Update statistics);

The above configuration can be controlled at the database level (all indexes and statistics on all tables) or based on a single index or statistic. The auto-Create statistics setting applies only to non-indexed columns because SQL Server always creates statistics for an index key when the index is created. The asynchronous version of the auto-update STATISTICS setting applies to the indexes and columns that are not indexed in the WHERE clause.

first, automatic maintenance

By default, SQL Server handles statistics automatically. Auto-Create statistics and auto-Update statistics settings are turned on by default, and these two features are called automatic statistics. Automatic asynchronous statistics is set to off by default.

　　1. Automatically create statistics

　　The auto-Create statistic attribute will automatically create statistics on the non-indexed columns referenced in the query WHERE clause .

For example, when the following select statement runs on a name column that does not have an index on the person table, the statistics for the Name column are created.

SELECT *  from WHERE = ' Zhang San '

Let's look at the profiler's trace output:

　　

Of course, the events in your Profiler choose Auto Stats.

　　In addition, when a nonclustered index is created, a statistic with the same name as the nonclustered index is created. When you delete a nonclustered index, that same-name statistic is also deleted.

　　2, Automatic UPDATE statistics

If the statistics are marked as obsolete, the Automatic Update Statistics feature automatically updates the existing statistics on the index and on the columns of the persistent table when the table is referenced in the query. The type of modification is an action statement, such as INSERT, UPDATE, DELETE. The threshold value that is modified depends on the rows in the table.

In SQL Server, the cardinality is the number of rows in the table. Use internal thresholds to reduce the frequency of automatic update statistics, and use column modifications instead of row modifications to further reduce the frequency.

Number of rows	Number of changes threshold value
0	> Insert
<500	>500 modification
>500	500+20% Cardinality Modification

　　3. Automatic Asynchronous Update STATISTICS

If automatic asynchronous updates are turned on, the basic statistical behavior in SQL Server does not change fundamentally. When a set of statistics is marked as obsolete and a query uses these statistics, the statistics update does not interrupt the query as usual. Instead, the query uses the old statistics to complete the execution. Once the query is complete, the statistics are updated asynchronously. This is appealing because when the statistics are updated, the query plan in the procedure cache is deleted and the queries that are run must be compiled. So instead of making the query wait for statistical updates and process recompilation, let the query complete. The next time you call the same query, it will have the updated statistics, just recompile it.

Although this feature can make recompilation faster, some queries may use the old execution plan because of the time difference. Be careful to test before turning on this feature.

Second, manual maintenance

The following conditions require automatic maintenance of the intervention statistics:

• SQL Server version upgrade, when upgrading from a previous version to SQL Server 2008, because the statistical maintenance of SQL Server2008 has been upgraded, the statistics for the entire database should be manually updated immediately after the upgrade, rather than waiting for SQL Server to use automatic statistics to update it.
• Perform a set of special SQL activities that will not be executed again: In this case, you must decide whether you are willing to spend the overhead of automating statistical maintenance to get a better execution plan that affects the performance of other SQL Server activities. Therefore, in general, do not need to care about such a one-time action.
• Automatic statistics maintenance encounters a problem when the only solution is to turn off automatic statistical maintenance: Even in these cases, you can turn off the attribute for some database tables that are experiencing difficulties, rather than the entire database.
• When analyzing query performance, you realize that some queries reference database objects that are missing statistics: This can be evaluated from the graphical and XML execution plans.
• When analyzing statistical validity, you realize that they are imprecise: this can be determined when a low execution execution plan is created from an index that should be better.

　　1, Management statistics settings

About the operation of the statistics to the following address: http://www.cnblogs.com/kissdodog/p/3413893.html

You can also use the WITH NORECOMPUTE option of the UPDATE STATISTICS command to disable this setting for all or individual indexes on one table in all the current databases. Sp_createstats stored procedures also have norecompute options. The NoRecompute option is a parameter when statistics are created or updated, which is specified when statistics are created, which is equivalent to specifying that the statistic cannot be updated.

Avoid disabling the automatic statistics feature unless you are testing to verify that this is a performance benefit. If the auto-Update STATISTICS feature is disabled, you should manually confirm and create the missing statistics on columns that are not indexed, and then keep the existing statistics up to date.

　　2. Generate statistics

To create statistics manually, use one of the following options:

• Create STATISTICS: Statistics can be created on one or more columns of a table or indexed view. Unlike the CREATE INDEX command, create statistics uses sampling by default.
• Sp_createstats: Use this stored procedure to create a single-column statistic for all eligible columns on all user tables of the current table. Computed columns, ntext, text, geometry, geograohy, or image data types except columns, sparse columns, and all columns other than the first column that already has a statistic or index.
• Update STATISTICS: You can use this option to update statistics for individual or all index keys and non-indexed columns for a table or an indexed view.
• Sp_updatestats: Use this stored procedure to update statistics for all user tables in the current database.

You may find that allowing automatic Update statistics is not appropriate for your system. Scheduling the database to update at idle time statistics is an acceptable way to handle this problem. UPDATE statistics is the preferred mechanism because it provides greater flexibility and control. Because of the type of data that is inserted, it is possible that the sampling method of cell phone statistics, which is used more quickly, cannot collect the correct data. In these cases, you can force fullscan (full scan) so that all data is used to update statistics, just as the statistics were created the first time. This can be a very expensive operation, so it's best to carefully select the index and timing of the processing.

　　3. Statistical Maintenance status

You can use the following methods to check the current settings for automatic statistics.

• Databasepropertyex
• Sp_autostats

　　(1), automatically create the status of statistics

You can run a query on the Sys.database system table to check the current settings for auto-create statistics, as follows:

SELECT is_auto_create_stats_on  from sys.databases WHERE [name] = ' Test '

A return value of 1 means enable and 0 means disable:

　　

Check for automatic update of statistics:

SELECT is_auto_update_stats_on    --Check for Automatic Updates  from sys.databasesWHERE [name] = ' Test '

You can also use the sp_autostats system stored procedure to check the status of this feature, applying the table name to this stored procedure will be in the output section of the global statistics setting, providing the current database automatically create statistical configuration values.

EXEC ' Test '    --Query the status of database open statistics

The return value on indicates enabled, and off indicates disabled.

　　(2), Automatic Update statistics status

Can be used to automatically create statistical similarity methods to check the current status of automatic Update statistics and automatic asynchronous update statistics.

Here's how to do this using the Databaseepropertyex function:

SELECT DATABASEPROPERTYEX ('Test','isautoupdatestatistics')

Statistical Maintenance < Fourth >