Statistics is an integral part of performance as it helps the SQL Server optimizer choose the proper operation to be performed while executing the SELECT
statement. There are two main ways to create and update statistics:
We will see these options in this recipe.
Before we move further to generate statistics, let us see some commands to view the current settings of statistics for database and table.
The following script will let you know whether the Auto_Create_Statistics
option is enabled for databases or not:
SELECT CASE WHEN DATABASEPROPERTYEX('Master','IsAutoCreateStatistics')=1 THEN 'Yes' ELSE 'No' END as 'IsAutoCreateStatisticsOn?', CASE WHEN DATABASEPROPERTYEX('Master','IsAutoUpdateStatistics')=1 THEN 'Yes' ELSE 'No' END as 'IsAutoUpdateStatisticsOn?', CASE WHEN DATABASEPROPERTYEX('Master','is_auto_update_stats_async_on')=1 THEN 'Yes' ELSE 'No' END as 'isAutoUpdateStatsAsyncOn...