How to update statistics for a database's system tables

We recently upgraded many of our instances to 2016. As a result, a SELECT statement from sqlpackage.exe is timing out on some instances.

After some testing, I found that by updating the statistics on the database's system tables showing up in the execution plan, the SELECT stopped timing out.

update statistics sys.[sysclsobjs] with fullscan
update statistics sys.[syscolpars] with fullscan
update statistics sys.[sysidxstats] with fullscan
update statistics sys.[sysiscols] with fullscan
update statistics sys.[sysobjvalues] with fullscan

Is there anyway either through the standard maintenance packages, Ola Hallengren's scripts or some other process to only update system table statistics?

Update 08/01

Here are the steps I took after upgrade

About the 4199 traceflag KB974006

-- for the instance
/* 
Turn on traceflag 4199 (my understanding of this traceflag is that it disables
optimizer hotfixes in 2016
*/

-- disable automatic numa
sp_configure 'automatic soft-NUMA disabled', 1
GO

-- For each database
-- Turn on Query optimizer hotfixes
-- Turn off Legacy cardinality estimation

exec sp_MSforeachDB 'ALTER DATABASE [?] SET COMPATIBILITY_LEVEL = 130;
USE [?]; 
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;

-- update statistics for all tables, system tables are ignored
EXEC sp_MSforeachtable ''UPDATE STATISTICS [?] WITH FULLSCAN''
'

Update 08/02

Here is the SELECT from sqlpackage.exe causing timeouts for TFS

```
SELECT * FROM (
SELECT
SCHEMA_NAME([o].[schema_id]) AS [SchemaName],
[si].[object_id] AS [ColumnSourceId],
[o].[name] AS [ColumnSourceName],
[o].[type] AS [ColumnSourceType],
[ic].[column_id] AS [ColumnId],
[c].[name] AS [ColumnName],
[si].[index_id] AS [IndexId],
[si].[name] AS [IndexName],
[ds].[type] AS [DataspaceType],
[ds].[data_space_id] AS [DataspaceId],
[ds].[name] AS [DataspaceName],
[si].[fill_factor] AS [FillFactor],
[si].[is

Ola's index maintenance script has parameter SYSTEM_DATABASES --> All system databases (master, msdb, and model) for UpdateStatistics --> ALL - Update index and column statistics. that takes care of updating stats for system databases as well.

Also, refer to Statistics on system tables and query performance. I have seen it very rare to update stats on system tables unless you have very large amount of objects in them (I still use Ola's script for weekly index maint on system tables).

As a side note: since you upgraded to SQL Server 2016, are you using the new CE ? What is the compatibility mode ?, were the post upgrade steps done ?.

EDIT:

Automatic soft NUMA is beneficial - 30% gain in query performance was obtained by using Soft NUMA & proper MAX DOP setting.

A small note : Please dont use undocumented sp_MSforeachDB. May be a dynamic sql is a much better option.

e.g.

select 'ALTER DATABASE '+quotename(name)+' SET COMPATIBILITY_LEVEL = 130;'
     from sys.databases where database_id > 4 and state_desc = 'ONLINE'

If you are consistently seeing the behaviour of those system base tables getting out dated, then you should file a connect bug for it.

StackExchange Database Administrators Q#145308, answer score: 3