Nisarg Upadhyay
Screenshot of email

T-SQL scripts to update statistics based on the row modification

December 24, 2020 by

In this article, we are going to learn how we can develop a T-SQL script to update statistics of tables based on the row modification. It is always a challenging task to perform maintenance on a table that has billions of rows. During the early years of my professional career, I was assigned a project and one of our clients had performance issues. The initial analysis showed that due to frequent data modifications, a specific table’s statistics become outdated.

We already set up a weekly statistics maintenance job. Still, it was not sufficient as the data modification was frequent, so we decided to run statistics maintenance daily. The best way to achieve that was to update the tables’ statistics based on the row modification counter. First, let us understand how the row modification counter works.

The row modification counter tracks the number of updates and deletes that occurred on a table. When we update or delete a record from the table, the counter updates its values with the number of rows affected by the query. Let me explain this with a small example. I have created a table named employees in AdventureWorks2017 and insert some records in it by executing the following query:

Run the following query to get the value of the row modification counter.

T-SQL Query to get row modification counter

Let us update some records by executing the following query:

Execute the below query to get row modification counter

Row modifications

As you can see, a new statistic _WA_Sys_00000006_06ED0088 has been created on the employees table, and the row modification counter for _WA_Sys_00000006_06ED0088 statistics has been changed. Let’s run the delete query and verify the row modification counter.

T-SQL Delete query

In the T-SQL script, we will use the following DMV and DMF.

  1. sys.sysindexes: This DMV is used to get the number of modifications (UPDATE/DELETE) that occurred on a table
  2. sys.objects: This DMV is used to get the table name
  3. sys.schema: This DMV is used to get the schema name
  4. sys.dm_db_stats_properties: This DMF is used to get the last statistics update date

The query populates the table name, schema name, modified rows, and last statistics update date.

T-SQL Query to get data change

Now, let us understand the stored procedure for statistics maintenance.

Section 1: Create temp table and declare required variables

In the first section, the script creates two temp tables.

  1. #Databases: This temp table holds the list of the databases you want to perform statistics maintenance
  2. #TablesUpdated: This temp table holds the list of the tables whose statistics should be updated
  3. ##StatisticsReport: This temp table holds the list of the tables whose statistics should be updated. The purpose of the report is to email the details of the tables with row changes

We have declared the following variable:

  • @SQLQuery: This variable holds a dynamic SQL query which iterates through all the databases stored in the #Database table and inserts the list of the tables that are updated by UPDATE or DELETE statements and insert them in a temp table named #TablesUpdated

Following is the T-SQL script:

Section 2: Crete dynamic SQL Query to identify the tables

In this section, the script executes a while loop that execute against all the databases and populates the list of the tables that has been changed by UPDATE and DELETE statement and insert the list of tables in #TablesUpdated. Below is the code:

Section 3: Create dynamic SQL Query to update the statistics

In this section, the script creates a dynamic SQL query that prepares an UPDATE Statistics command. Before executing the WHILE loop, it exports the list of the tables to the #StatisticsReport table. To do that, the script executes a WHILE loop to iterate through the table name that are in #TablesUpdated table. We have stored database name, schema name and table name in #TablesUpdated tables hence the format of the update statistics command is Update statistics [Database Name].[SchemaName].[TableName]. Following is the code:

Section 4: Email the statistics update report

In this section of the code, the script exports the list of the tables from the #StatisticsReport table to an excel file and email it to the desired recipients. To send the email, we will use the sp_send_dbmail stored procedure of the MSDB database. Using the sp_send_dbmail stored procedure, we can export the temporary table’s output in an excel file and send it as an attachment. The script uses the following arguments of the sp_send_dbmail:

  • @query: Specify the T-SQL query that you want to execute. The query’s output can be included in the email, or we can export it to excel and attach it to the email
  • @attach_query_result_as_file: This parameter specifies the method of exporting the query output. The valid values can be any of the following:
    • Zero (0) : The query result will be included within the body of the email
    • One (1) : The query result will be attached to the email
  • @query_attachment_filename: This parameter specifies the fully qualified name of the file in which you want to export the query result’s output
  • @query_result_separator: This parameter specifies the character that is used to separate the columns. The default value is space (‘ ‘)

Following is the code:

Following is the entire script:

Test the script

Let us test the script by executing it.

If the script executes successfully, we will receive an email with the excel files with details of the tables. Following is the snapshot of the email and excel report.

Email:

Screenshot of email

Excel File:

T-SQL Query output

If the report is not in the desired format, you can tweak it by changing the @query_result_result_separator variable.

Summary

In this article, I have explained the T-SQL script used to update statistics based on the data modifications that occurred on a table. If you plan to schedule some statistics maintenance jobs for the large tables, this script becomes very handy.

Nisarg Upadhyay
DBAtools, Jobs, T-SQL, Utilities

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 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 B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

1,635 Views