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
Best Answer
Disabling Query Optimizer Hot Fixes resolves the problem 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.
Which then leads to this article: sql-server-query-optimizer-hotfix-trace-flag-4199-servicing-model
They suggest option three which is what I am now using.