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
You can also retrieve this information from the dynamic management view sys.dm_db_stats_properties and system catalog view sys.stats.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT name, last_updated, rows, rows_sampled, CASE AUTO_CREATED WHEN 0 THEN 'Index Statistics' WHEN 1 THEN 'Auto Created Statistics' END AS 'Auto Created Statistics', USER_CREATED AS 'User Created Statistics' FROM sys.stats AS stat CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp WHERE stat.object_id = OBJECT_ID('[Sales].[Customer]') ORDER BY AUTO_CREATED DESC; |
In the query output, we can clearly differentiate index and auto created statistics.
We can further join the above query with sys.stats_columns and sys.columns to know the column for which stats is created.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT stat.name, last_updated, rows, rows_sampled, CASE AUTO_CREATED WHEN 0 THEN 'Index Statistics' WHEN 1 THEN 'Auto Created Statistics' END AS 'Auto Created Statistics', -- AUTO_CREATED AS 'Auto Created Statistisc', USER_CREATED AS 'User Created Statistics', c.name ColumnName FROM sys.stats AS stat CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp INNER JOIN sys.stats_columns sc ON stat.object_id = sc.object_id AND stat.stats_id = sc.stats_id INNER JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id WHERE stat.object_id = OBJECT_ID('[Sales].[Customer]') ORDER BY AUTO_CREATED DESC; |
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.
|
1 2 |
EXEC sp_helpstats '[Sales].[Customer]'; |
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.
Give a name to the user statistic and add the statistics columns using the Add button. Here, we added both [AccountNumber ]and [ModifiedTable] columns.
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.
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.
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.
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
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
You can view the summary of the schedule as shown below
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
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
Click Next and define Update Statistics task options
Databases: Select the databases for which you want SQL Server Update Statistics
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.
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
Click Next and review the maintenance plan configuration we completed so far. You can go back and change the configuration if required.
Click on Finish, and it shows the progress, status of each task.
You can see the configured maintenance plan and SQL Server Agent job in the following screenshot.
We can either execute the SQL Server agent job or execute the maintenance plan directly.
It starts the SQL Server Update Statistics and shows success only completed.
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.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023