Rajendra Gupta
View Summary

SQL Server Update Statistics using database maintenance plans

April 13, 2020 by

This article explores SQL Server Update Statistics using the database maintenance plan.

Introduction

SQL Server query optimizer uses statistics to build an optimized execution plan. These statistics contain the histogram that has information about data distribution, number of rows, data density. SQL Server automatically creates and updates the statistics based on a predefined threshold. The out of date statistics results in bad execution plans impacting query performance and higher resource utilization. In the article, SQL Server Statistics and how to perform Update Statistics in SQL, we explored the following topics.

  • Auto Create Statistics
  • Auto Create Incremental Statistics
  • Auto-update statistics
  • Auto Update Statistics Asynchronously
  • Manually Update Statistics

In this article, we will explore SQL Server Maintenance plan options in detail for SQL Server Update Statistics.

Overview of Statistics in SQL Server

Let’s have a quick overview of viewing statistics. Expand a table, and you see different folders for Indexes and statistics. In the following screenshot, we see two types of statistics.

  • Index statistics: For each index, we have a corresponding statistic. Its name is also similar to the index name
  • Auto Created statistics: SQL Server automatically creates the statistics based on the columns in query predicate such as where clause. These statistics always starts with _WA. It is a two-letter code for Washington in the US

SQL Server Update Statistics

You can also retrieve this information from the dynamic management view sys.dm_db_stats_properties and system catalog view sys.stats.

In the query output, we can clearly differentiate index and auto created statistics.

Check statistics for a table

We can further join the above query with sys.stats_columns and sys.columns to know the column for which stats is created.

Check columns in a statistics

You can also use the sp_helpstats system procedure to check the statistics column. It is an old command and shows statistics created by Index or user.

Column statistics

Create a new user statistic

Let’s create a user statistic on Customer table on [AccountNumber ]and [ModifiedTable] columns. To create, right-click on Statistics and New Statistics.

Create a new user statistic

Give a name to the user statistic and add the statistics columns using the Add button. Here, we added both [AccountNumber ]and [ModifiedTable] columns.

Specify statistics column

In the case of multiple columns, we can also change the column order using the “Move Up” and “Move Down” buttons. Click Ok, and it creates the user statistics for you.

Let’s execute the previous query and check the statistics. Now we can see user statistics as well. Here, you see total rows and rows sampled counts are the same that shows a full scan is performed for these statistics.

Index statistics

Apart from the auto-update statistics, we should also update them regularly to avoid any performance issues due to out of date statistics. We can use either SQL Scripts to Management plan to update them regularly based on your requirements.

Create a maintenance plan for SQL Server Update Statistics

Let’s explore how to create a maintenance plan for SQL Server update statistics along with various configurations.

Connect to SQL instance in SSMS, expand Management and right-click on Maintenance Plans.

Maintenance Plans

Launch Maintenance Plan Wizard. In the launch page, it gives a brief introduction of routine database administration tasks available with a maintenance plan. If you want to skip this introductory page, we can put a check on – Do not show this starting page again.

Maintenance Plans wizard

Click Next and select maintenance plan properties. On this page, provide the following information.

  • Name: Give a name for this maintenance plan. You should give a proper name to identify it quickly in case you have multiple maintenance plans
  • Description: It is an optional field. You can add a brief description of the maintenance plan for reference to other DBA’s
  • Run as: It is the service account under the context of which SQL Server runs the maintenance plan. By default, it is the SQL Server Agent Service account. You can also create a proxy account for executing an agent job. For this article, let’s stick with the default agent service account

    Plan properties

  • Schedule: We might combine multiple tasks in a single maintenance plan. In this case, we might not want a single schedule for all maintenance. For example, we do not want the full database backup job to start at the same time as the index maintenance task. In this case, you can select separate schedules for each task

    In this article, we focus on SQL Server Update Statistics task so we can go with option – Single schedule for the entire plan

    Currently, we see Schedule as Not Scheduled (On-Demand). Click on Change and select a job schedule as per your requirement

    Create a Schedule

    You can view the summary of the schedule as shown below

    View schedule details

    In the next step, select the maintenance task. Once you select this, you get a brief description that allows the query optimizer to make better judgements about data access strategies

    select the maintenance task

    In the next step, we can select the maintenance task order. We have only one task in this maintenance plan so Move Up and Move Down options are disabled

    select the maintenance task order

    Click Next and define Update Statistics task options

    Define update statistics task

  • Databases: Select the databases for which you want SQL Server Update Statistics

    Select databases

    You can select from the following options:

    • All databases
    • System databases
    • All user databases(excluding system databases)
    • Specific databases

    If you select specific databases, it provides you options to further drill down and specify tables to update stats. We specified all databases, so it does not enable the object selection window

  • Update: Here, we can select update stats operations from the following values

    • All existing statistics
    • Column statistics only
    • Index statistics only

    Usually, we update all existing statistics using the database maintenance task

  • Scan type: It is a vital configuration option. Here, we select either a full scan or sample percentage for SQL Server Update Statistics. We should perform full scan update regularly so that the query optimizer has accurate information about data distribution and prepare an optimized execution plan. It is a similar option of adding a FULL SCAN clause in the UPDATE STATISTICS command

Here is my configuration for this article.

Scan type

On the next page, select the maintenance plan report delivery option. You should select at least one option to analyze maintenance plan logs, especially in case of a failure. If you select an email report, it shows the database email operator configured in the SQL instance. If you do not have any mail profile, you should configure it first using a reference article How to configure Database Mail in SQL Server.

  • Write a report to a text file
  • Email report

Select Report options

Click Next and review the maintenance plan configuration we completed so far. You can go back and change the configuration if required.

View Summary

Click on Finish, and it shows the progress, status of each task.

Check the maintenance plan progress

You can see the configured maintenance plan and SQL Server Agent job in the following screenshot.

View jobs

We can either execute the SQL Server agent job or execute the maintenance plan directly.

Execute a maintenance plan

It starts the SQL Server Update Statistics and shows success only completed.

Successful execution

Let’s go to the maintenance plan log folder specified during configuration. Here, you see a text file as shown below.

Open this log file, and you see individual queries for performing SQL Server update statistics on the individual tables. In case the maintenance plan fails, you can come to this log file and view what went wrong and fix the issue before the job reruns.

View log file

Conclusion

In this article, we explored the process to perform SQL Server Update statistics using a database maintenance plan. You should create this maintenance plan as per your requirement and keep statistics updated and avoid any performance issues due to out of date statistics.

Rajendra Gupta
Indexes, Maintenance, Performance

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

4,863 Views