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.

--identify last stats update date
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;

One can often simply update stats on the underlying system tables to improve catalog view query performance. Below is a script for SQL Server 2017+ and Azure SQL Database that generates and executes DDL to update stats on all non-empty system tables.

--system table stats update for 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))
         ,';')
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;
GO

Similarly, the script below will update system table stats for SQL Server 2016 and earlier versions. The only difference is the XML path technique for aggregate string concatenation because STRING_AGG() isn’t available in older SQL Server versions.

--generate script SQL 2016 and earlier using XML PATH
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;
GO

On a side note, I’ll mention the “SELECT @SQL = @SQL + …” technique is sometimes used for set-based aggregate string concatenation instead of XML PATH or STRING_AGG(). However, that should be avoided because it is nether supported nor reliable. A quote from Microsoft in response to a bug report on the now retired Connect feedback site: “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.