Managing Statistics

Introduction

The Managing Statistics module provides you with the instructions and devices to develop your hands-on skills in the following topic.

• Working with statistics

Lab time: It will take approximately 45 minutes to complete this lab.

Exam Objectives

Two exam objectives are covered in this lab.

• Collect data about the outdated statistics into a table
• Using SQL Server Auto Update and Auto Create Statistics Options

Exercise 1 - Working with Statistics

Statistics in SQL Server are objects that store statistical data related to the column values of tables or views. The query optimizer uses these objects to create effective, efficient, and high-quality query plans to improve the overall query performance of the database. There are three options that you can set at the database level to determine the creation and update of statistics. These are as follows:

AUTO_UPDATE_STATISTICS: If this option is set to ON, the query optimizer determines the out-of-date statistics on database objects. The out-of-date statistics are updated when the query uses them. AUTO_CREATE_STATISTICS: If this option is set to ON, the query optimizer creates statistics on individual columns of the database objects. The individual column statistics name starts with _WA. AUTO_UPDATE_STATISTICS_ASYNCHRONOUSLY: The query optimizer can use synchronous or asynchronous statistics updates. As the query optimizer follows the synchronous pattern of statistical update by default, the AUTO_UPDATE_STATISTICS_ASYNCHRONOUSLY option is set to OFF by default. If this option is set to ON, the compilation of the queries, happen even with the out-of-date statistics.

In this exercise, you will perform the following tasks:

• Collect data about the out-of-date statistics into a table.
• Set Auto Create and Auto Update Statistics options.
• Update statistics with FULLSCAN and NORECOMPUTE.