Creating and updating statistics

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:

• Manually create/update statistics

• Automatically create/update statistics

We will see these options in this recipe.

Getting ready

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...