Optimizing Statistics

Introduction

The Optimizing Statistics module provides you with the instructions and devices to develop your hands on skills in the following topics.

• Obtaining the date of the latest statistics update
• Updating the table statistics manually
• Using FULLSCAN and NORECOMPUTE to update statistics

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

Exam Objectives

Four exam objectives are covered in this lab.

• Demonstrate how to obtain the date of the recent update of statistics on a table
• Demonstrate how to update statistics for all indexes on a table
• Demonstrate how to update statistics for an index on a table
• Demonstrate how to update statistics using FULLSCAN and NORECOMPUTE

Exercise 1 - Obtaining the Date of the Latest Statistics Update

In a SQL Server database, statistics provide information about how a data is distributed in the database objects, such as tables and indexes. When SQL Server receives a new query, the query optimizer uses statistics to create the best possible query plan to resolve that query and retrieve information, thereby, providing enhanced query performance.

If the statistic values are outdated in the table, then the query optimizer cannot create an ideal query plan to resolve the query, thereby affecting the query performance. Therefore, before running a query, it is important to know when the statistics was last updated on a table.

You can enable the AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS settings by default to ensure that the database engine automatically creates and updates statistics, respectively.

In this exercise, you will learn to obtain the date of the recent update of statistics on a table.

Exercise 2 - Updating the Table Statistics Manually

When there is a change in the data in a database, the statistics gets outdated. If a query is executed on outdated statistics, there will be inconsistencies between the Actual Number of Rows and the Estimated Number of Rows. Therefore, the outdated statistics affects the performance of the query optimizer by not executing the query properly. In smaller database tables, setting the option to automatically update the statistics can be beneficial. However, when working with large database tables, you need to update the statistics manually for better query performance. The statistics can be manually updated by:

1. Using Transact-SQL statements
2. Using Maintenance Plan wizard

In this exercise, you will learn to update statistics in a database manually.

Exercise 3 - Using FULLSCAN and NORECOMPUTE to Update Statistics

There are two ways to obtain statistics for the data of a table:

1. Using a sample of data
2. Performing a full scan

When a sample of the table data is used, you can obtain an estimate of statistics of the distributed data. However, performing a full scan allows you to determine the actual statistics of the distributed data and, therefore, is more accurate. It is not frequently done because it takes more time to execute and obtain the results. Sampled statistics is used in disk-based tables, by default. In contrast, FULLSCAN statistics is mandatory in memory-optimized tables.

Running FULLSCAN with UPDATE STATISTICS periodically enhances the performance of the query optimizer to obtain accurate statistics of data, irrespective of the size of the table. When using UPDATE STATISTICS to update the statistics manually, it is essential to stop the automatic update of the indexes or tables in the database. The NORECOMPUTE function with UPDATE STATISTICS command is used to stop the automatic re-computing of the table indexes.