SQL Server System Table Statistics Update

I’ve seen a few questions in the SQL Server forums recently reporting slow performance of DMV queries, such as those in queries executed by SQL Server Data Tools. This can result in query timeouts and is particularly an issue with databases that contain many objects and/or columns. Like many query performance problems, the root cause may be stale statistics. The last statistics update date on system table indexes can be gleaned by this query.

SELECT
	  QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + N'.' + QUOTENAME(OBJECT_NAME(i.object_id)) + N'.' + QUOTENAME(name) AS index_name
	, STATS_DATE(i.object_id, i.index_id) AS stats_date
		FROM sys.indexes AS i
		JOIN sys.partitions AS p ON
			p.object_id = i.object_id
			AND p.index_id = i.index_id
		WHERE 
			OBJECTPROPERTYEX(i.object_id, 'IsSystemTable') = 1
			AND i.index_id > 0
			AND p.rows > 0;

The solution in many cases to simply update statistics on the underlying system tables indexes used by the problem DMV queries. This can be done selectively by identifying the system table indexes referenced in execution plan seek and scan operators of the problem query execution plan and then executing UPDATE STATISTICS on each index. However, the task is somewhat tedious.

Alternatively, one can simply update stats on all the system tables. Below is a script that generates and executes DDL to update stats on all non-empty system tables, making quick work of this.

--use XML PATH pre-SQL 2017
DECLARE @SQL nvarchar(MAX) =
	(
		SELECT 
			  N'UPDATE STATISTICS ' 
			  + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) 
			  + N'.' 
			  + QUOTENAME(OBJECT_NAME(i.object_id)) 
			  + N';'
		FROM sys.indexes AS i
		JOIN sys.partitions AS p ON
			p.object_id = i.object_id
			AND p.index_id = i.index_id
		WHERE 
			OBJECTPROPERTYEX(i.object_id, 'IsSystemTable') = 1
			AND i.index_id > 0
			AND p.rows > 0
		FOR XML PATH(''), TYPE).value('.','nvarchar(MAX)'
	);
EXEC sp_executesql @SQL;

Note that this XML path method I used here is the only supported T-SQL method for set-based aggregate string concatenation before SQL 2017. I often see people perform set-based string concatenation using a method like “@SQL = @SQL + …” but that method is not reliable according to Microsoft: “Even without ORDER BY, we do not guarantee that @var = @var + will produce the concatenated value for any statement that affects multiple rows. The right-hand side of the expression can be evaluated either once or multiple times during query execution and the behavior as I said is plan dependent.

SQL Server 2017 and Azure SQL Database include a STRING_AGG function, which is a bit easier to use than XML technique and may perform better too.

--use STRING_AGG() in SQL 2017 and Azure SQL Database
DECLARE @SQL nvarchar(MAX) =
	(
		SELECT
			  STRING_AGG(
				  N'UPDATE STATISTICS ' 
				+ QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id))
				+ N'.'
				+ QUOTENAME(OBJECT_NAME(i.object_id))
				+ N';'
			,'')
		FROM sys.indexes AS i
		JOIN sys.partitions AS p ON
			p.object_id = i.object_id
			AND p.index_id = i.index_id
		WHERE 
			OBJECTPROPERTYEX(i.object_id, 'IsSystemTable') = 1
			AND i.index_id > 0
			AND p.rows > 0
	);
EXEC sp_executesql @SQL;