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
