Update SQL Server statistics using a database maintenance plan

Database backups, integrity checks, and performance optimizations are core regular tasks of DBAs. The client data is very important for a DBA to manage the database backup and make sure the integrity of the backups. So if something goes wrong with a production database, it can be recovered with minimum downtime. The database integrity checks are also important because, in the case of database corruption, it can be corrected with minimum downtime and data loss. Managing database performance is also important. Managing database performance is a combination of multiple tasks.

1. Identify the list of resource-intensive queries and help developers to re-writing them.
2. Create and manage indexes on the table. Also, perform index defragmentation to make sure they remain in good shape.
3. Finally, managing statistics of tables.

In my previous article, I covered the topic of Auto create statistics and Auto Update Statistics and how they can help to improve performance. In this article, I am going to explain how to create and schedule the maintenance plan to update the statistics.

First, let me explain what SQL Server statistics are and how it may help to increase the performance of the SQL server.

SQL Server Statistics and their importance

Statistics are metadata used by SQL Server query optimizer, which helps to determine the best way to retrieve the data. The optimizer uses statistics to understand the data, its distribution, and the number of rows a given query is likely to return from the available statistics. Based on this information, it decides the optimal data access path. It also determines whether to do a table scan or an index seek, use nested loop join or a hash join, etc.

If statistics are out of date, or if they are unavailable, the optimizer may choose the poor execution plan, which reduces the query performance significantly. SQL Server can automatically maintain statistics and refresh them based on its tracking of data modifications.

Statistics can be created and updated automatically by enabling “Auto Create Statistics” and “Auto Update Statistics.” However, for some tables, such as those subject to significant changes in data distribution, it’s possible that SQL Server automatic statistics update will be insufficient to maintain consistently high levels of query performance.

Before I explained the different approaches to update the statistics, let me explain the different ways to review the statistics created on any table.

How to review the statistics

We can view column statistics and index statistics

1. Using SQL Server Management Studio.
2. Using System stored procedures and system catalogs and dynamic management views

View Statistics using SQL Server Management Studio

For example, I want to see the statistics created on the [HumanResources].[Employee] table created in the AdventureWorks2017 database. To do that, launch SQL Server Management Studio. Then expand the AdventureWorks2017 database, expand the [HumanResources].[Employee] table. See the following image:

Using System stored procedures and dynamic management views

If you’re using an older version of SQL Server, you can use the sp_helpstats system procedure to review the statistics of the tables. sp_helpstats will show the statistics, created by SQL Server or by a user. It will not show the statistics created by Index. To demonstrate that, I have created a statistics named User_Statistics_BirthDate on the [HumanResources].[Employee] table.

Following is the example:

USE ADVENTUREWORKS2017 
GO
EXEC SP_HELPSTATS 
  'HUMANRESOURCES.EMPLOYEE'

Following is the output.

You can review statistics by querying the sys.stats system catalog. It provides information about the statistics created by SQL Server, Created by Indexes and created by a user.

Execute the following query:

SELECT NAME         AS 'STATISTICS NAME', 
       AUTO_CREATED AS 'CREATED AUTOMATICALLY', 
       USER_CREATED AS 'CREATED BY USER' 
FROM   SYS.STATS 
WHERE  OBJECT_ID = OBJECT_ID('HUMANRESOURCES.EMPLOYEE')

Following is the output:

Now, let’s join this table with other system catalogs to get detailed information about the statistics. To do that, execute the following query:

SELECT [SCHEMAS].[NAME] + '.' + [OBJECTS].[NAME] AS [TABLE_NAME], 
       [INDEXES].[INDEX_ID]                      AS [INDEX ID], 
       [STATS].[NAME]                            AS [STATISTIC], 
       STUFF((SELECT ', ' + [COLUMNS].[NAME] 
              FROM   [SYS].[STATS_COLUMNS] [STATS_COLUMN] 
                     JOIN [SYS].[COLUMNS] [COLUMNS] 
                       ON [COLUMNS].[COLUMN_ID] = [STATS_COLUMN].[COLUMN_ID] 
                          AND [COLUMNS].[OBJECT_ID] = [STATS_COLUMN].[OBJECT_ID] 
              WHERE  [STATS_COLUMN].[OBJECT_ID] = [STATS].[OBJECT_ID] 
                     AND [STATS_COLUMN].[STATS_ID] = [STATS].[STATS_ID] 
              ORDER  BY [STATS_COLUMN].[STATS_COLUMN_ID] 
              FOR XML PATH('')), 1, 2, '')       AS [COLUMNS_IN_STATISTIC] 
FROM   [SYS].[STATS] [STATS] 
       JOIN [SYS].[OBJECTS] AS [OBJECTS] 
         ON [STATS].[OBJECT_ID] = [OBJECTS].[OBJECT_ID] 
       JOIN [SYS].[SCHEMAS] AS [SCHEMAS] 
         ON [OBJECTS].[SCHEMA_ID] = [SCHEMAS].[SCHEMA_ID] 
       LEFT OUTER JOIN [SYS].[INDEXES] AS [INDEXES] 
                    ON [OBJECTS].[OBJECT_ID] = [INDEXES].[OBJECT_ID] 
                       AND [STATS].[NAME] = [INDEXES].[NAME] 
WHERE  [OBJECTS].[OBJECT_ID] = OBJECT_ID(N'HUMANRESOURCES.EMPLOYEE') 
ORDER  BY [STATS].[USER_CREATED] 

GO

The query above populates the following details

1. The table on which statistics are created.
2. Index ID.
3. Name of Statistics.
4. Columns included in statistics.

Following is the output:

In the next section, I will explain different ways to update the statistics.

Different approaches to statistics update

We can update the statistics in the following ways:

1. Create a SQL Server maintenance plan.
2. Create Using the custom script.
3. Manually execute the update statistics command on an individual table.

Firstly, I will explain how we can create a maintenance plan to update the statistics.

Create SQL Server Maintenance Plan to Update the Statistics

Now, in this demo, we will create an update statistics maintenance plan to update the statistics. The maintenance plan will perform the following tasks:

First Create a maintenance plan. To do that, open SQL Server Management Studio. Expand SQL server instance >>Management folder >> Under Management, right-click MaintenancePplans, and select New Maintenance Plan. See the following image:

The New MaintenancePlan dialog box opens. In the box, provide a name of the maintenance plan, and click OK. See the following image:

Maintenance plan designer opens. In maintenance plan designer toolbox, drag and drop “Update Statistics Task” in the designer window. See the following image:

Now double-click Update Statistics Task. The Update Statistics Task dialog box opens. In the dialog box, there are options which can be used to customize the maintenance plan. See the following image:

We can customize the following options on using Update statistics Maintenance plan.

1. Update statistics of all objects of a specific database. See the following image:
   
2. Specific objects of selected databases. You can update statistics of All Tables and Views / Specific tables and views. See the following image:
   
   If we choose Tables or Views than SQL will fill the name of views or tables in the selection dialog box.  See the following image:
   
3. The third option is Update. We can update all statistics of tables/views, or we can choose to update only column statistics (statistics created on nonindexed columns), or we can choose Index statistics only (statistics created by indexes). See the following image:
   
4. We can also select the scan type of any statistics. We can choose Full Scan or Sample by a specified percentage or specified rows. See the following image:
   

Now, as I mentioned, we will create a maintenance task which will update statistics of all tables within the AdventureWorks2017 database with a full scan. So, choose options accordingly. Once the maintenance task is configured, it looks like the following:

Once the maintenance task is configured properly, close the update statistics dialog. After configuration, the maintenance plan looks like the following:

Once Maintenance plan is created, let us schedule the maintenance plan. To do that, click the calendar icon opposite to the Description column. See the following image:

Once you click the calendar button, a dialog box to configure the job schedule opens. See the following image:

As I mentioned, our update statistics maintenance plan will execute on every Sunday at 4:00 AM. So we will configure the job schedule accordingly. Job scheduling is straightforward. Once you configure the schedule, the dialog box looks like the following:

Once an execution schedule is configured, the entire maintenance plan looks like the following image. Save the maintenance plan and close the window.

Now, let us run this maintenance plan by executing the SQL Job created by the maintenance plan. To open SQL Jobs, expand SQL Server Agent and expand Jobs. You can see the SQL Job created by SQL maintenance plan. Now to execute the job, right click Update Statistics Weekly.Weekly.Subplan_1 and click Start Job at Step. See the following image.

Once the job is completed, you can see the following Job execution successful dialog box.

Summary

In this article, I have covered:

1. A detailed explanation of SQL Server Statistics and its importance.
2. Different options to update the statistics.
3. A working example of creating a SQL Maintenance plan to update the statistics.

In my next article, I will explain various T-SQL commands to update the statistics. Moreover, I will explain a T-SQL Script which will update the statistics based on the volume of data changes that occurred after insert/update/ delete occurred on the table.