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.
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?', ...