3

We are using sqlpackage.exe to deploy scripts onto a SQL Server 2016 SP1 instance. Running sqlpackage.exe executes this SELECT

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] 

In a previous question I was able to resolve this problem by running a UPDATE STATISTICS WITH FULLSCAN on the system tables of the target database. Since our SP1 upgrade though, updating stats is no longer resolving the problem. The above SELECT takes on average 3 minutes to execute. sqlpackage will only wait 30 seconds before timing out.

Adding STATISTICS IO to the query, I get this output

  • Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  • Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  • Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  • Table 'sysobjvalues'. Scan count 19452, logical reads 86055648, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  • Table 'sysschobjs'. Scan count 1, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Notice the extremely high logical reads value on sysobjvalues.

Now if I turn on the old cardinality estimator with OPTION(QUERYTRACEON 9481), I get this output.

  • Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  • Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  • Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  • Table 'sysidxstats'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  • Table 'sysschobjs'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  • Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  • Table 'sysiscols'. Scan count 1, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Since I don't have access to the sqlpackage code, I can't add this option to it. I can't turn on the legacy cardinality estimator for the entire database because that will cause issues elsewhere.

  • What other options are there?
  • Plan Guide?
  • Can an option be added to sqlpackage to force it to use querytraceon 9481?

Thanks, Craig

2 Answers 2

2

Disabling Query Optimizer Hot Fixes resolves the problem here.

enter image description here

This means I'm using the 2016 cardinality estimator minus any improvements to the Query Optimizer brought on by other service packs and hotfixes?

I never quite understood what happens when you disable hotfixes in the 130 compatibility level.

Here's the explanation from the KB. Just adds to the confusion in my opinion.

Controls query optimizer changes released in SQL Server Cumulative Updates and Service Packs. Starting with SQL Server 2016, trace flag 4199 changes that are made to previous releases of SQL Server will become enabled under database compatibility level 130 without trace flag 4199 enabled. For more information, see this Microsoft Support article.

Which then leads to this article: sql-server-query-optimizer-hotfix-trace-flag-4199-servicing-model

enter image description here

They suggest option three which is what I am now using.

0

The /p:CommandTimeout=(INT32 '60') sqlpackage parameter should let you increase the timeout. If it doesn't work then it may be buggy like the timeout override method for dacfx.

Your Answer

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

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