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

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

USE [?]; 

-- update statistics for all tables, system tables are ignored

Update 08/02

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

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_padded] AS [IsPadded],
[si].[is_disabled] AS [IsDisabled],
[si].[allow_page_locks] AS [DoAllowPageLocks],
[si].[allow_row_locks] AS [DoAllowRowLocks],
[sit].[cells_per_object] AS [CellsPerObject],
[sit].[bounding_box_xmin] AS [XMin],
[sit].[bounding_box_xmax] AS [XMax],
[sit].[bounding_box_ymin] AS [YMin],
[sit].[bounding_box_ymax] AS [YMax],
[sit].[level_1_grid] AS [Level1Grid],
[sit].[level_2_grid] AS [Level2Grid],
[sit].[level_3_grid] AS [Level3Grid],
[sit].[level_4_grid] AS [Level4Grid],
[sit].[tessellation_scheme] AS [TessellationScheme],
[s].[no_recompute] AS [NoRecomputeStatistics],
[p].[data_compression] AS [DataCompressionId],
CONVERT(bit, CASE WHEN [ti].[data_space_id] = [ds].[data_space_id] THEN 1 ELSE 0 END)
AS [EqualsParentDataSpace]
[sys].[spatial_indexes] AS [si] WITH (NOLOCK)
INNER JOIN [sys].[objects] AS [o] WITH (NOLOCK) ON [si].[object_id] = [o].[object_id]
INNER JOIN [sys].[spatial_index_tessellations] [sit] WITH (NOLOCK) ON [si].[object_id] = [sit].[object_id] AND [si].[index_id] = [sit].[index_id]
INNER JOIN [sys].[data_spaces] AS [ds] WITH (NOLOCK) ON [ds].[data_space_id] = [si].[data_space_id] 
INNER JOIN [sys].[index_columns] AS [ic] WITH (NOLOCK) ON [si].[object_id] = [ic].[object_id] AND [si].[index_id] = [ic].[index_id]
INNER JOIN [sys].[columns] AS [c] WITH (NOLOCK) ON [si].[object_id] = [c].[object_id] AND [ic].[column_id] = [c].[column_id]
INNER JOIN [sys].[objects] AS [o2] WITH (NOLOCK) ON [o2].[parent_object_id] = [si].[object_id]
INNER JOIN [sys].[stats] AS [s] WITH (NOLOCK) ON [o2].[object_id] = [s].[object_id] AND [s].[name] = [si].[name]
INNER JOIN [sys].[partitions] AS [p] WITH (NOLOCK) ON [p].[object_id] = [o2].[object_id] AND [p].[partition_number] = 1
LEFT JOIN [sys].[indexes] AS [ti] WITH (NOLOCK) ON [o].[object_id] = [ti].[object_id]
LEFT JOIN [sys].[tables] AS [t] WITH (NOLOCK) ON [t].[object_id] = [si].[object_id]
WHERE [si].[is_hypothetical] = 0
AND [ti].[index_id] < 2
AND OBJECTPROPERTY([o].[object_id], N'IsSystemTable') = 0
AND ([t].[is_filetable] = 0 OR [t].[is_filetable] IS NULL)
AND ([o].[is_ms_shipped] = 0 AND NOT EXISTS (SELECT *
FROM [sys].[extended_properties]
WHERE [major_id] = [o].[object_id]
AND [minor_id] = 0
AND [class] = 1
AND [name] = N'microsoft_database_tools_support'
) AS [_results] 

If I don't update system table statistics for the database, then this SELECT can and will timeout when deploying via sqlpackage.exe

  • Do you have Auto Update Statistics enabled? Aug 1, 2016 at 1:08
  • Hello @Peter, yes they are enabled on all of our databases. Aug 1, 2016 at 7:46
  • is there a compelling reason to update stats on your system tables?
    – swasheck
    Aug 2, 2016 at 3:34
  • 1
    Hello @swasheck, yes there is. In the question, the reason to update system table statistics was due to a timeout error with the SELECT coming from the sqlpackage.exe binary. Without update statistics for the system tables in the execution plan, it takes 2 to 10 minutes for that SELECT to complete. Perhaps I should add the SELECT? Aug 2, 2016 at 8:50

2 Answers 2


You can "see" the system tables without using DAC connection:

FROM sys.objects o
WHERE o.type = 'S'
ORDER BY o.name

From there, you could build a TSQL string of commands and execute it. (I hardcoded sys for the schema. Presumably this will never change.):

SELECT @TSql = @TSql +  'UPDATE STATISTICS sys.' + o.name + ' WITH FULLSCAN;' + CHAR(13) + CHAR(10)
FROM sys.objects o
WHERE o.type = 'S'
ORDER BY o.name

--Verify/test commands.

--Uncomment and re-run when ready to proceed.
--EXEC sp_executesql @TSql

This could easily be put into a job step for a SQL Agent job. I tested this on SQL Server 2012 w/ SP3. I've also run similar code in SQL 2008 R2 and SQL 2014. I don't know for sure if it works in SQL 2016--please let us know if it does not.

Other Notes
The UPDATE STATISTICS commands worked when I was logged in as a [sysadmin]. It also worked if the login was not a member of [sysadmin], but was the database owner. Membership in [db_owner] alone was not sufficient. (Again, this was on SQL 2012 w/ SP3. YMMV.)

  • 2
    As far as I can tell (meaning, I also tested on 2012 as I don't yet have 2016 installed) this answer is entirely correct. I verified the Updated stats date via both DBCC SHOW_STATISTICS and STATS_DATE. Aug 7, 2016 at 1:35

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


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.


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.

  • Hello Kin, I updated my answer. I read through your answer again and noticed some steps I hadn't considered such as recompile and refreshing views. Do these still apply in 2016? I did also try the ALL option for UpdateStatistics and the @MSShippedObjects='Y' which doesn't seem to help at all. Aug 1, 2016 at 7:44
  • 1
    @CraigEfrein the reason you dont see any difference is because the tables you have mentioned are system base tables which are accessible only using DAC connection.
    – Kin Shah
    Aug 1, 2016 at 20:06
  • 1
    @CraigEfrein and Kin: the reason that setting @MSShippedObjects='Y', @UpdateStatistics = 'ALL' did not help is simply due to Ola's script filtering out system tables via WHERE objects.[type] IN('U','V'). So you could alter Ola's script to include S as well, but I don't recommend that. DMason's answer would work. Aug 7, 2016 at 1:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.