O'Reilly logo

Microsoft SQL Server 2012 Performance Tuning Cookbook by Bihag Thaker, Ritesh Shah

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required