SQL Server statistics – modification counter

Statistics on indexes and colunns is very important for the query optimizer in SQL server. If the statistics are inaccurate, you’ll end up with query plans that doesn’t perform very well. The statistics are usually updated automatically or updated when indexes are rebuilt, BUT there are some cases where you need to update more often. Like when you have large tables and you insert a lot of records but less than 20% of the number of records in the table and the indexes are “incrementing”. There are some work arounds for this problem, read my other blog posts about statistics.

This blog post is about finding statistics that should have been updated, but haven’t due to some factors. In previous versions of SQL server, you could look at rowmodcntr to find out how many records that have been modified or added since last uppdate. This column is now deprecated, but in SQL Server 2008R2 SP2, we got a new DMV for statistics properties. This DMV gives you information about when the statistics was last updated, number of records, number of records sampled and modification counter etc. With this information you can analyze why statistics are out of date.

To help you a little bit, I give you a sample procedure that is stored in master and marked as a system stored procedure.

USE MASTER
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.routines r WHERE r.[ROUTINE_NAME]='sp_sqlservice_check_statistics' AND r.[ROUTINE_SCHEMA] ='dbo')
	DROP PROC dbo.sp_sqlservice_check_statistics;

GO
CREATE PROC dbo.sp_sqlservice_check_statistics @schemaName SYSNAME=NULL, @tableName SYSNAME=NULL, @modPercentLimit DECIMAL(8,4)=1.0
AS

SET NOCOUNT ON;

DECLARE @objectID INT = OBJECT_ID(@schemaName + '.' + @tableName);

SELECT 
	[rowmodcounter].[modPercent], 
	names.dbName,
	names.schemaName,
	names.tableName,
	names.statsName,
	[s].[auto_created], 
	[s].[no_recompute], 
	[sp].[last_updated],
	[sp].[rows],
	[sp].[rows_sampled],
	[sp].[steps],
	[sp].[unfiltered_rows],
	[sp].[modification_counter],
	sampleRate = (1.0*sp.rows_sampled/sp.rows)*100,
	'UPDATE STATISTICS ' + names.schemaName + '.' + names.tableName + '(' + names.statsName + ')'
FROM [sys].[stats] s
CROSS APPLY [sys].[dm_db_stats_properties]([s].[object_id],[s].[stats_id]) sp
INNER JOIN [sys].[tables] t
	ON [s].[object_id] = [t].[object_id] 
CROSS APPLY (
				SELECT (1.0*[sp].[modification_counter]/NULLIF([sp].[rows],0))*100
				) AS rowmodcounter(modPercent)
CROSS APPLY (SELECT 	
	dbName			= DB_NAME(),
	schemaName		= SCHEMA_NAME(t.schema_id),
	tableName		= t.[name], 
	statsName		= s.[name]
	) AS names
WHERE 
	(t.[object_id] = @objectID OR @objectID IS NULL) 
	--AND [t].[is_ms_shipped] =0
	AND OBJECTPROPERTY(s.[object_id],'IsMSShipped')=0 
	AND [rowmodcounter].[modPercent] >@modPercentLimit
ORDER BY [rowmodcounter].[modPercent] DESC;
GO

EXEC sp_ms_marksystemobject 'dbo.sp_sqlservice_check_statistics' -- mark the procedure as system procedure

EXEC Sp_MSForEachDB 'use ?;exec dbo.sp_sqlservice_check_statistics NULL,NULL, 0.01' -- check every database in the SQL server instance

Analyze the result to see how “old” your statistics are, how many records modified since last update, sampleRate, modPercent, rows and rows_sampled. If you experience performance issues and the execution plan uses indexes that have statistics that are old, have a low sampleRate, many rows or high modPercent, you should make sure the statistics are updated more frequent and/or with a higher sample rate. There are also some trace flags that you can use to lower the threashold for “auto update statistics” and to solve issues with statistics that are based on “incrementing” columns. Please read my other blogs about statistics and trace flags to learn how to use them.

If you need to know more about how to solve your performance issues, feel free to contact me or any of my collegues at SQL Service and we will help you the best we can, our SQL server speciallist have long experience in solving performance issues.

/Håkan Winther
Twitter: @h_winther