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

Get Microsoft SQL Server 2012 Performance Tuning Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.