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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
CREATE TABLE employees ( id INT PRIMARY KEY CLUSTERED, firstname VARCHAR(150), lastname VARCHAR(150), phonenumber VARCHAR(20), emailaddress VARCHAR(500), city VARCHAR(50), state VARCHAR(50), postalcode VARCHAR(10), country VARCHAR(50) ) GO INSERT INTO employees SELECT businessentityid, firstname, lastname jobtitle, phonenumber, emailaddress, city, stateprovincename, postalcode, countryregionname FROM humanresources.vemployee WHERE firstname IS NOT NULL AND lastname IS NOT NULL GO |
Run the following query to get the value of the row modification counter.
|
1 |
select id, name as [IndexName] ,rowmodctr [Rows Modification] ,rowcnt [Number of Rows] from sys.sysindexes where ID =OBJECT_ID('employees') |
Let us update some records by executing the following query:
|
1 |
update employees set city='New York' where city='Bothell' |
Execute the below query to get row modification counter
|
1 |
select id, name as [IndexName] ,rowmodctr [Rows Modification] ,rowcnt [Number of Rows] from sys.sysindexes where ID =OBJECT_ID('employees') |
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.
|
1 2 3 4 5 |
DELETE FROM employees WHERE city = 'Bothell' go SELECT id, NAME AS [IndexName], rowmodctr [Rows Modification], rowcnt [Number of Rows] FROM sys.sysindexes WHERE id = Object_id('employees') GO |
In the T-SQL script, we will use the following DMV and DMF.
- sys.sysindexes: This DMV is used to get the number of modifications (UPDATE/DELETE) that occurred on a table
- sys.objects: This DMV is used to get the table name
- sys.schema: This DMV is used to get the schema name
- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT SCH.NAME [SCHEMA_NAME], OBJ.NAME [TABLE_NAME], MAX(IK.ROWCNT) [ROW_COUNT], MAX(IX.ROWMODCTR) [ROWS_MODIFIED], MAX(SP.LAST_UPDATED) [LAST_UODATED] FROM SYS.OBJECTS AS OBJ INNER JOIN SYS.SCHEMAS SCH ON OBJ.[SCHEMA_ID] = SCH.[SCHEMA_ID] INNER JOIN SYS.STATS AS STAT ON STAT.[OBJECT_ID] = OBJ.[OBJECT_ID] CROSS APPLY SYS.DM_DB_STATS_PROPERTIES(STAT.[OBJECT_ID], STAT.STATS_ID) AS SP LEFT OUTER JOIN SYS.SYSINDEXES AS IX ON STAT.[OBJECT_ID] = IX.ID AND STAT.[NAME] = IX.NAME LEFT OUTER JOIN SYS.SYSINDEXES AS IK ON STAT.[OBJECT_ID] = IK.ID AND IK.INDID = 1 WHERE MODIFICATION_COUNTER > 10 AND OBJ.[OBJECT_ID] > 100 GROUP BY SCH.NAME, OBJ.NAME |
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.
- #Databases: This temp table holds the list of the databases you want to perform statistics maintenance
- #TablesUpdated: This temp table holds the list of the tables whose statistics should be updated
- ##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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
CREATE TABLE #databases ( id INT, NAME VARCHAR(max) ) CREATE TABLE #tablesupdated ( id INT IDENTITY(1, 1), dbname VARCHAR(250), schemaname VARCHAR(30), tablename VARCHAR(250), totalrows INT, rowmodified INT, statsupdatedate DATETIME ) CREATE TABLE #StatisticsReport ( id INT IDENTITY(1, 1), dbname VARCHAR(250), schemaname VARCHAR(30), tablename VARCHAR(250), totalrows INT, rowmodified INT, statsupdatedate DATETIME ) INSERT INTO #databases (NAME) SELECT NAME FROM sys.databases WHERE database_id > 5 DECLARE @SQLQuery NVARCHAR(max) DECLARE @i INT =0 DECLARE @DBName VARCHAR(max) DECLARE @DBCount INT SET @DBCount = (SELECT Count(1) FROM #databases) |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
declare @DBCount int set @DBCount = (select count(1) from #databases) While @DBCount>@i Begin set @DBName = (select top 1 name from #databases) set @SQLQuery = 'SELECT ''' + @DBName + ''', sch.NAME [Schema_Name], obj.NAME [Table_Name], Max(ik.rowcnt) [Row_Count], Max(ix.rowmodctr) [Rows_Modified], Max(sp.last_updated) [Last_Uodated] FROM '+@DBName+'.sys.objects AS obj INNER JOIN '+@DBName+'.sys.schemas sch ON obj.[schema_id] = sch.[schema_id] INNER JOIN '+@DBName+'.sys.stats AS stat ON stat.[object_id] = obj.[object_id] CROSS apply '+@DBName+'.sys.Dm_db_stats_properties(stat.[object_id], stat.stats_id) AS sp LEFT OUTER JOIN '+@DBName+'.sys.sysindexes AS ix ON stat.[object_id] = ix.id AND stat.[name] = ix.NAME LEFT OUTER JOIN '+@DBName+'.sys.sysindexes AS ik ON stat.[object_id] = ik.id AND ik.indid = 1 WHERE modification_counter > 10 AND obj.[object_id] > 100 GROUP BY sch.NAME, obj.NAME' print @SQLQuery insert into #TablesUpdated (DBName,Schemaname,tablename,totalrows,rowmodified,statsupdatedate) exec sp_executesql @SQLQuery delete from #databases where name=@DBName set @I=@i+1 End |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
declare @j int =0 declare @DB_Name varchar(max) declare @DBCount int declare @tableCount int declare @UpdateStatisticsQuery nvarchar(max) set @TableCount=(select count(1) from #TablesUpdated) declare @Schema_Name varchar(250) declare @Table_Name varchar(250) insert into #StatisticsReport (DBName,Schemaname,tablename,totalrows,rowmodified) select DBName,Schemaname,tablename,totalrows,rowmodified from #TablesUpdated While (@TableCount>@j) Begin select top 1 @DB_Name = DBName, @Schema_Name=Schemaname,@Table_Name=tablename from #TablesUpdated set @UpdateStatisticsQuery = 'Update Statistics [' +@DB_Name +'].[' +@Schema_Name +'].[' +@Table_Name+ '] With FULLSCAN' exec sp_executesql @UpdateStatisticsQuery delete from #TablesUpdated where DBName=@DB_Name and Schemaname=@Schema_Name and tablename=@Table_Name set @j =@j +1 End |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
DECLARE @lsMessage VARCHAR(2000) DECLARE @SubjectText VARCHAR(2000) DECLARE @Filename VARCHAR(2000) SET @SubjectText = 'Statistics Update Report - ' + @@SERVERNAME SET @Filename = 'statistics_update_report_' + @@SERVERNAME + '_' + CONVERT(VARCHAR, GETDATE(), 112) + '.csv' SET @lsMessage = '<p style="font-family:Arial; font-size:10pt">Statistics have been updated. Please review the report for a list of databases and tables affected. ' + '</p>' EXEC msdb.dbo.sp_send_dbmail @recipients='nisargupadhyay87@outlook.com', @body=@lsMessage, @subject=@SubjectText, @importance='high', @body_format='HTML', --@exclude_query_output =1, @profile_name='OutlookMail' ,@query = 'SELECT DBName,Schemaname,tablename,totalrows,rowmodified FROM ##StatisticsReport' , @attach_query_result_as_file = 1, @query_attachment_filename=@Filename, @query_result_separator=' ' |
Following is the entire script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 |
create procedure sp_daily_stats_update as Begin set nocount on create table #databases (id int , name varchar(max)) create table #TablesUpdated (id int identity(1,1),DBName varchar(250), Schemaname varchar(30), tablename varchar(250),totalrows int, rowmodified int,statsupdatedate datetime ) create table ##StatisticsReport (id int identity(1,1),DBName varchar(250), Schemaname varchar(30), tablename varchar(250),totalrows int, rowmodified int) insert into #databases(name) select name from sys.databases where database_id>5 declare @SQLQuery nvarchar(max) declare @UpdateStatisticsQuery nvarchar(max) declare @i int =0 declare @DBName varchar(max) declare @DBCount int set @DBCount = (select count(1) from #databases) While @DBCount>@i Begin set @DBName = (select top 1 name from #databases) set @SQLQuery = 'SELECT ''' + @DBName + ''', sch.NAME [Schema_Name], obj.NAME [Table_Name], Max(ik.rowcnt) [Row_Count], Max(ix.rowmodctr) [Rows_Modified], Max(sp.last_updated) [Last_Uodated] FROM '+@DBName+'.sys.objects AS obj INNER JOIN '+@DBName+'.sys.schemas sch ON obj.[schema_id] = sch.[schema_id] INNER JOIN '+@DBName+'.sys.stats AS stat ON stat.[object_id] = obj.[object_id] CROSS apply '+@DBName+'.sys.Dm_db_stats_properties(stat.[object_id], stat.stats_id) AS sp LEFT OUTER JOIN '+@DBName+'.sys.sysindexes AS ix ON stat.[object_id] = ix.id AND stat.[name] = ix.NAME LEFT OUTER JOIN '+@DBName+'.sys.sysindexes AS ik ON stat.[object_id] = ik.id AND ik.indid = 1 WHERE modification_counter > 10 AND obj.[object_id] > 100 GROUP BY sch.NAME, obj.NAME' insert into #TablesUpdated (DBName,Schemaname,tablename,totalrows,rowmodified,statsupdatedate) exec sp_executesql @SQLQuery delete from #databases where name=@DBName set @I=@i+1 End select * from #TablesUpdated insert into ##StatisticsReport (DBName,Schemaname,tablename,totalrows,rowmodified) select DBName,Schemaname,tablename,totalrows,rowmodified from #TablesUpdated declare @j int =0 declare @DB_Name varchar(max) declare @tableCount int set @TableCount=(select count(1) from #TablesUpdated) declare @Schema_Name varchar(250) declare @Table_Name varchar(250) While (@TableCount>@j) Begin select top 1 @DB_Name = DBName, @Schema_Name=Schemaname,@Table_Name=tablename from #TablesUpdated set @UpdateStatisticsQuery = 'Update Statistics [' +@DB_Name +'].[' +@Schema_Name +'].[' +@Table_Name+ '] With FULLSCAN;' exec sp_executesql @UpdateStatisticsQuery delete from #TablesUpdated where DBName=@DB_Name and Schemaname=@Schema_Name and tablename=@Table_Name set @j =@j +1 End DECLARE @lsMessage VARCHAR(2000) DECLARE @SubjectText VARCHAR(2000) DECLARE @Filename VARCHAR(2000) SET @SubjectText = 'Statistics Update Report - ' + @@SERVERNAME SET @Filename = 'statistics_update_report_' + @@SERVERNAME + '_' + CONVERT(VARCHAR, GETDATE(), 112) + '.csv' SET @lsMessage = '<p style="font-family:Arial; font-size:10pt">Statistics have been updated. Please review the report for a list of databases and tables affected. ' + '</p>' EXEC msdb.dbo.sp_send_dbmail @recipients='nisargupadhyay87@outlook.com', @body=@lsMessage, @subject=@SubjectText, @importance='high', @body_format='HTML', @profile_name='OutlookMail' ,@query = 'SELECT DBName,Schemaname,tablename,totalrows,rowmodified FROM ##StatisticsReport' , @attach_query_result_as_file = 1, @query_attachment_filename=@Filename, @query_result_separator=' ' drop table #databases drop table #TablesUpdated drop table ##StatisticsReport End |
Test the script
Let us test the script by executing it.
|
1 |
Exec sp_daily_stats_update |
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:
Excel File:
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.
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
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
- Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022