Instruction sqlpackage.exe SELECT provoquant des lectures massives


2

Nous utilisons sqlpackage.exe pour déployer des scripts sur une instance SQL Server 2016 SP1.Exécuter sqlpackage.exe exécute ce 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] 

Dans un précédent question j'ai pu résoudre ce problème en exécutant UPDATE STATISTICS WITH FULLSCAN sur les tables système de la base de données cible.Depuis la mise à niveau de notre SP1, la mise à jour des statistiques ne résout plus le problème.Le SELECT ci-dessus prend en moyenne 3 minutes à exécuter.sqlpackage n'attendra que 30 secondes avant l'expiration du délai.

Ajout de STATISTICS IO à la requête, je reçois cette sortie

  • Table 'Table de travail'.Nombre de balayages 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques lob 0, lectures physiques lob 0, lectures lobread-ahead 0.

  • Table 'Table de travail'.Nombre de balayages 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques lob 0, lectures physiques lob 0, lectures lobread-ahead 0.

  • Table 'Table de travail'.Nombre de balayages 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques lob 0, lectures physiques lob 0, lectures lobread-ahead 0.

  • Tableau 'sysobjvalues'.Nombre de numérisations 19452, lectures logiques 86055648, lectures physiques 0, lectures anticipées 0, lectures logiques lobées 0, lectures lobphysiques 0, lectures anticipées lobées 0.

  • Table 'sysschobjs'.Nombre de balayages 1, lectures logiques 155, lectures physiques 0, lectures anticipées 0, lectures logiques lob 0, lectures physiques lob 0, lectures lobread-ahead 0.

Notez la valeur extrêmement élevée des lectures logiques sur les valeurs sysobj.

Maintenant, si j’allume l’ancien estimateur de cardinalité avecOPTION (QUERYTRACEON 9481), Je reçois cette sortie.

  • Table 'Table de travail'.Nombre de balayages 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques lob 0, lectures physiques lob 0, lectures lobread-ahead 0.
  • Table 'Table de travail'.Nombre de balayages 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques lob 0, lectures physiques lob 0, lectures lobread-ahead 0.
  • Table 'Table de travail'.Nombre de balayages 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques lob 0, lectures physiques lob 0, lectures lobread-ahead 0.
  • Table 'sysidxstats'.Nombre de balayages 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques lob 0, lectures physiques lob 0, lectures lobread-ahead 0.
  • Table 'sysschobjs'.Nombre de balayages 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques lob 0, lectures physiques lob 0, lectures lobread-ahead 0.
  • Tableau 'Fichier de travail'.Nombre de balayages 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques lob 0, lectures physiques lob 0, lectures lobread-ahead 0.
  • Tableau 'sysiscols'.Nombre de balayages 1, lectures logiques 29, lectures physiques 0, lectures anticipées 0, lectures logiques lob 0, lectures physiques lob 0, lectures lobread-ahead 0.

Étant donné que je n'ai pas accès au code sqlpackage, je ne peux pas lui ajouter cette option.Je ne peux pas activer l'estimateur de cardinalité hérité pour l'ensemble de la base de données, car cela poserait des problèmes ailleurs.

  • Quelles sont les autres options?
  • Guide de plan?
  • Une option peut-elle être ajoutée à sqlpackage pour l'obliger à utiliser querytraceon9481?

Merci Craig

0

Le paramètre/p: CommandTimeout = (INT32 '60') sqlpackage devrait vous permettre d’augmenter le délai.Si cela ne fonctionne pas, il se peut que ce soit un problème comme la méthode de dépassement du délai d’expiration pour dacfx.


2

La désactivation des correctifs rapides de Query Optimizer résout le problème ici.

enter image description here

Cela signifie que j'utilise l'estimateur de cardinalité de 2016 moins les améliorations apportées à l'optimiseur de requêtes apportées par d'autres service packs et correctifs?

Je n'ai jamais très bien compris ce qui se passe lorsque vous désactivez les correctifs dans le niveau de compatibilité 130.

Voici l'explication de la KB .Ajoute juste à la confusion à mon avis.

Contrôle les modifications de l'optimiseur de requête publiées dans les mises à jour cumulatives et les Service Packs de SQL Server.À partir de SQL Server 2016, les modifications de l'indicateur de trace 4199 apportées aux versions précédentes de SQL Server seront activées sous le niveau de compatibilité de base de données 130 sans que l'indicateur de trace 4199 ne soit activé.Pour plus d'informations, consultez cet article du support Microsoft.

Ce qui conduit ensuite à cet article: sql-server-query-optimizer-hotfix-trace-flag-4199-servicing-model

enter image description here

Ils suggèrent la troisième option, celle que j'utilise maintenant.