How to Update SQL Server Statistics for Big Tables

In my previous article, I have briefly covered database statistics, its importance, and why statistics should be updated. Moreover, I have demonstrated a step by step process to create a SQL Server maintenance plan to update statistics. In this article, the following issues will be explained: 1. How to update statistics using T-SQL Command. 2. How to identify the frequently updated tables using T-SQL and also how to update the statistics of tables with frequently inserted/ updated / deleted data .

Updating statistics using T-SQL

You can update statistics using the T-SQL script. If you want to update statistics using T-SQL or SQL Server management studio, you need ALTER database permission on the database. See the T-SQL code example to update the statistics of a specific table:

Let us consider the example of updating the statistics of the OrderLines table of the WideWorldImporters database. The following script will do that.

If you want to update the statistics of a specific index, you can use the following script:

In case you want to update the statistics of the IX_Sales_OrderLines_Perf_20160301_02 index of the OrderLines table, you can execute the following script:

You can also update the statistics of the entire database. If you have a very small database with few tables and little amount of data, then you can update the statistics of all tables within a database. See the following script:

Updating statistics for tables with frequently inserted / updated / deleted data

On large databases, scheduling the statistics job becomes complicated, especially when you have only a few hours to perform index maintenance, update statistics, and fulfill other maintenance tasks. By a large database I mean a database that contains thousands of tables and each table contains thousands of rows. For example, we have a database named X. It has hundreds of tables, and each table has millions of rows. And only a few tables get updated frequently. Other tables are rarely changed and have very few transactions performed on them. As I mentioned before, to keep the database performance up to the mark, table statistics must be up to date. So we create an SQL maintenance plan to update the statistics of all tables within the X database. When SQL server updates the statistics of a table, it utilizes a significant amount of resources which can lead to a performance problem. So, it takes a long time to update statistics of hundreds of big tables and while statistics are being updated, the performance of the database reduces significantly. In such circumstances, it is always advisable to update the statistics only for the tables that are updated frequently. You can keep track of changes in data volume or number of rows over the time by using the following dynamic management views: 1. sys.partitions provides information about the total number of rows in a table. 2. sys.dm_db_partition_stats provides information about row counts and page counts, per partition. 3. sys.dm_db_index_physical_stats provides information about the number of rows and pages, plus information about index fragmentation and more. The details about data volume are important, but they do not make the picture of database activity complete. For example, a staging table that has nearly the same number of records can be deleted from the table or inserted in a table every day. Due to that, a snapshot of the number of rows would suggest that the table is static. It might be possible that the records added and deleted have very different values that change the data distribution heavily. In this case, automatically updating statistics in SQL Server makes statistics meaningless. Therefore, tracking the number of modifications to a table is very useful. This can be done in the following ways: 1. rowmodctr column in sys.sysindexes 2. modified_count column in sys.system_internals_partition_columns 3. modification_counter column in sys.dm_db_stats_properties Thus, as I explained earlier, if you have limited time for database maintenance, it is always advisable to update the statistics only for the tables with a higher frequency of data change (insert / update / delete). To do that efficiently, I have created a script that updates the statistics for the “active” tables. The script performs the following tasks: • Declares the required parameters • Creates a temporary table named #tempstatistics to store the table name, schema name, and database name • Creates another table named #tempdatabase to store the database name. First, execute the following script to create two tables:

Next, write a while loop to create a dynamic SQL query that iterates through all the databases and inserts a list of tables that have a modification counter greater than 200 into the #tempstatistics table. To obtain information about data changes, I use sys.dm_db_stats_properties. Study the following code example:

Now, create the second loop within the first loop. It will generate a dynamic SQL Query that updates the statistics with full scan. See the code example below:

Once the script execution completed, it will drop all temporary tables.

The entire script will appear as follows:

You can also automate this script by creating an SQL Server Agent job which will execute it at a scheduled time. A step by step instruction of automating this job is given below.

Creating an SQL Job

First, let’s create an SQL Job to automate the process. To do that, open SSMS, connect to the desired server and expand SQL Server Agent, right-click on Jobs and select New Job. In the New Job dialog box, type the desired name in the Name field. Now, click Steps menu option on the left panel of the New Job dialog box, then click New in the Steps window. In the New Job Step dialog box, that opens, provide the desired name in the Step name field. Next, select Transact-SQL script (T-SQL) in the Type drop-down box. Then, select DBATools in the Database drop-down box and write the following query in the command text box:

To configure the schedule of the job, click Schedules menu option in the New Job dialog box. The New Job Schedule dialog box opens. In the Name field, provide the desired schedule name. In our example, we want this job to be executed every night at 1 AM, hence in the Occurs drop-down box in the Frequency section, select Daily. In the Occurs once at field in the Daily frequency section, enter 01:00:00. Click OK to close the New Job Schedule window and then click OK again in the New Job dialog box to close it. Now let us test this job. Under SQL Server Agent, right-click Update_Statistics_Daily. In case the job has been executed successfully, you will see the following window.

Summary

In this article, the following issues have been covered: 1. How to update the statistics of tables using T-SQL Script. 2. How to obtain information about changes in data volume and frequency of data changes. 3. How to create the script that updates statistics on active tables. 4. How to create an SQL Server Agent Job to execute the script at the scheduled time.

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.
Nisarg Upadhyay