6

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_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]
FROM
[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? – Sir Swears-a-lot Aug 1 '16 at 1:08
  • Hello @Peter, yes they are enabled on all of our databases. – Craig Efrein Aug 1 '16 at 7:46
  • is there a compelling reason to update stats on your system tables? – swasheck Aug 2 '16 at 3:34
  • 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? – Craig Efrein Aug 2 '16 at 8:50
2

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

SELECT *
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.):

DECLARE @TSql NVARCHAR(MAX) = ''
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.
PRINT @TSql

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

  • 1
    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. – Solomon Rutzky Aug 7 '16 at 1:35
3

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.

  • 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. – Craig Efrein Aug 1 '16 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 '16 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. – Solomon Rutzky Aug 7 '16 at 1:40
0

By default auto_update_statistics is disabled. A quick search returns plenty of links about it.

According to Brent Ozar not everyone can agree if turning it on or off is a good idea.

https://www.brentozar.com/blitz/auto-update-stats-async-enabled/

If out if date stats is causing you problems maybe you should consider turning this feature on.
To do this:

  1. Right click on the database in question,
  2. select Properties> Options>
  3. set Auto Update Statistics = True.
  4. click Ok.

If you don't want to do this, then you will have to find a way to do this periodically as you suggest. You could write a script to up date all statistics, or as you suggested use Ola Hallengren's maintenance scripts to update all stats.

  • I don't think you understood the question. OP is asking if there's a way to keep system table statistics up to date. – Randolph West Aug 1 '16 at 0:45
  • I have reworded my answer to clarify how to turn Auto Update Statistics on. – Sir Swears-a-lot Aug 1 '16 at 1:02
  • I think the issue here is that auto-update does not, in fact, keep system table statistics up to date, so OP was asking if there was a way to do it in an automated way as part of the maintenance scripts. – Randolph West Aug 1 '16 at 1:14
  • 1
    Fair enough I have asked OP to clarify if he does or does not have feature on or off. ( I was working on the assumption it does). – Sir Swears-a-lot Aug 1 '16 at 1:18
  • @Peter: you are confusing two different options: AUTO_UPDATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNC. The former defaults to ON while the latter defaults to OFF. The linked post is about the latter option, which you can see in the URL itself as it includes the "async" part. However, you are referring to the former option, which defaults to ON so is probably already set to ON. – Solomon Rutzky Aug 2 '16 at 7:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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