Microsoft SQL Server 2016 (SP3-GDR) - slow sp_pkeys system procedure

Question

Hello,

I have problem with sp_pkeys system procedure on Microsoft SQL Server 2016 (SP3-GDR) (KB5021129) - 13.0.6430.49.4. From few weeks time of execution is near 2 sec. Occurs on every table, every databases on this server.

Are there known solutions for this problem?

Thanks!

Answer 1

I would guess that statistics on the system tables are out of date.

First run:

sp_helptext 'sp_pkeys'

This gives you the source code the procedure. But the procedure does not access the system table directly, but uses the catalog views. For each view run sp_helptext to get the tables. Then for each table run:

UPDATE STATISTICS sys.sysschobjs WITH FULLSCAN

While you cannot query the system tables directly, you can actually run UPDATE STATISTICS on them.

Answer 2

I suspect system table statistics are stale like Erland mentioned. Here's a script to update all system table stats for SQL Server 2016:

--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

For later SQL versions, including Azure SQL database, one can use STRING_AGG instead of the XML ugliness for aggregate string concatenation:

--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