Intereting Posts

Conception de database de facture Le projet Cube ne fonctionne pas en raison des permissions Comment puis-je combiner 4 colonnes de données dans une seule colonne? SQL Server: Puis-je utiliser EXEC pour exécuter une application externe? Le client SQL semble utiliser un port random pour se connecter CONTIENT dans la search en text intégral ne returnne pas tous les résultats ElasticSearch n'a pas extrait toute la table SQL Sélectionnez récursif dans une requête SQL CTE problème avec DENSE_RANK () sans duplication Est-ce que index_id = 0 dans sys.indexes fait reference à la table elle-même? Base de données locale sans server SQL Stockage de certaines données SQL Server hors connection Utilisez SqlDataReader et le tableau de strings La condition d'index filtré est ignorée par l'optimiseur

Optimisation des plans d'exécution pour les requêtes T-SQL paramétrées contenant des fonctions de window

EDIT: J'ai mis à jour l'exemple de code et fourni des implémentations complètes de table et de vue pour reference, mais la question essentielle rest inchangée.

J'ai une vue assez complexe dans une database que j'essaie d'interroger. Lorsque je tente d'extraire un set de lignes de la vue en codant en dur la clause WHERE à des valeurs de key étrangère spécifiques, la vue s'exécute très rapidement avec un plan d'exécution optimal (les index sont utilisés correctement, etc.)

SELECT * FROM dbo.ViewOnBaseTable WHERE ForeignKeyCol = 20

Cependant, lorsque j'essaie d'append des parameters à la requête, mon plan d'exécution tombe soudainement en panne. Lorsque je lance la requête ci-dessous, j'obtiens des scans d'index au lieu de chercher partout et les performances de la requête sont très médiocres.

 DECLARE @ForeignKeyCol int = 20 SELECT * FROM dbo.ViewOnBaseTable WHERE ForeignKeyCol = @ForeignKeyCol

J'utilise SQL Server 2008 R2. Qu'est-ce qui donne ici? En quoi consiste l'utilisation de parameters provoquant un plan sous-optimal? Toute aide serait grandement appréciée.

Pour reference, voici les définitions d'objects pour lesquelles j'obtiens l'erreur.

 CREATE TABLE [dbo].[BaseTable] ( [PrimaryKeyCol] [uniqueidentifier] PRIMARY KEY, [ForeignKeyCol] [int] NULL, [DataCol] [binary](1000) NOT NULL ) CREATE NONCLUSTERED INDEX [IX_BaseTable_ForeignKeyCol] ON [dbo].[BaseTable] ( [ForeignKeyCol] ASC ) CREATE VIEW [dbo].[ViewOnBaseTable] AS SELECT PrimaryKeyCol, ForeignKeyCol, DENSE_RANK() OVER (PARTITION BY ForeignKeyCol ORDER BY PrimaryKeyCol) AS ForeignKeyRank, DataCol FROM dbo.BaseTable

Je suis certain que la fonction de window est le problème, mais je filter ma requête par une seule valeur que la fonction de window partitionne, donc je m'attendrais à ce que l'optimiseur filter d'abord, puis exécute la fonction de window. Il le fait dans l'exemple codé en dur mais pas dans l'exemple paramétré. Voici les deux plans de requête. Le plan supérieur est bon et le plan inférieur est mauvais.

Plans d'exécution de requête

Lorsque vous utilisez OPTION (RECOMPILE) assurez-vous de regarder le plan de post-exécution ('réel') plutôt que le plan de pré-exécution ('estimé'). Certaines optimizations ne sont appliquées que lorsque l'exécution a lieu:

 DECLARE @ForeignKeyCol int = 20; SELECT ForeignKeyCol, ForeignKeyRank FROM dbo.ViewOnBaseTable WHERE ForeignKeyCol = @ForeignKeyCol OPTION (RECOMPILE);

Plan de pré-exécution:

Plan de pré-exécution

Plan de post-exécution:

Plan de post-exécution

Testé sur SQL Server 2012 version 11.0.3339 et SQL Server 2008 R2 version 10.50.4270

Context et limites

Lorsque les fonctions de fenêtrage ont été ajoutées dans SQL Server 2005, l'optimiseur n'avait aucun moyen de pousser les sélections après ces nouvelles projections de séquence. Pour résoudre certains scénarios courants qui ont causé des problèmes de performances, SQL Server 2008 a ajouté une nouvelle règle de simplification, SelOnSeqPrj , qui permet de pousser les sélections appropriées là où la valeur est constante. Cette constante peut être un littéral dans le text de la requête, ou la valeur reniflée d'un paramètre obtenu via OPTION (RECOMPILE) . Il n'y a pas de problème particulier avec les NULLs bien que la requête puisse avoir ANSI_NULLS OFF pour voir ceci. Pour autant que je sache, l'application de la simplification à des valeurs constantes est seulement une limitation de mise en œuvre; il n'y a pas de raison particulière de ne pas pouvoir l'étendre pour travailler avec des variables. Mon souvenir est que la règle SelOnSeqPrj a résolu les problèmes de performance les plus courants.

Paramétrage

La règle SelOnSeqPrj n'est pas appliquée lorsqu'une requête est paramétrée avec succès . Il n'existe aucun moyen fiable de déterminer si une requête a été paramétrée automatiquement dans SSMS, cela indique seulement que l'auto-param a été tenté . Pour être clair, la présence de placeholders comme [@0] montre seulement que l'auto-paramésortingsation a été tentée. Un moyen fiable de savoir si un plan préparé a été mis en cache pour une réutilisation consiste à inspecter le cache de plan, où le «handle de plan paramétré» fournit le lien entre les plans ad hoc et les plans préparés.

Par exemple, la requête suivante semble être paramétrée automatiquement dans SSMS:

 SELECT * FROM dbo.ViewOnBaseTable WHERE ForeignKeyCol = 20;

Mais le cache du plan montre le contraire:

 WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' ) SELECT parameterized_plan_handle = deqp.query_plan.value('(//StmtSimple)[1]/@ParameterizedPlanHandle', 'nvarchar(64)'), parameterized_text = deqp.query_plan.value('(//StmtSimple)[1]/@ParameterizedText', 'nvarchar(max)'), decp.cacheobjtype, decp.objtype, decp.plan_handle FROM sys.dm_exec_cached_plans AS decp CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqp WHERE dest.[text] LIKE N'%ViewOnBaseTable%' AND dest.[text] NOT LIKE N'%dm_exec_cached_plans%';

Entrée de cache du plan Adhoc

Si l'option de database pour le paramétrage forcé est activée, nous obtenons un résultat paramétré, où l'optimization n'est pas appliquée:

 ALTER DATABASE Sandpit SET PARAMETERIZATION FORCED; DBCC FREEPROCCACHE; SELECT * FROM dbo.ViewOnBaseTable WHERE ForeignKeyCol = 20;

Plan de paramétrage forcé

La requête de cache de plan affiche maintenant un plan mis en cache paramétré, lié par le handle de plan paramétré:

Cache de plan paramétré

solution de contournement

Dans la mesure du possible, ma preference est de réécrire la vue en tant que fonction de table en ligne, où la position de la sélection peut être rendue plus explicite (si nécessaire):

 CREATE FUNCTION dbo.ParameterizedViewOnBaseTable (@ForeignKeyCol integer) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT bt.PrimaryKeyCol, bt.ForeignKeyCol, ForeignKeyRank = DENSE_RANK() OVER ( PARTITION BY bt.ForeignKeyCol ORDER BY bt.PrimaryKeyCol), bt.DataCol FROM dbo.BaseTable AS bt WHERE bt.ForeignKeyCol = @ForeignKeyCol;

La requête devient:

 DECLARE @ForeignKeyCol integer = 20; SELECT pvobt.* FROM dbo.ParameterizedViewOnBaseTable(@ForeignKeyCol) AS pvobt;

Avec le plan d'exécution:

Plan de fonction

Vous pouvez toujours utiliser la méthode CROSS APPLY.

 ALTER VIEW [dbo].[ViewOnBaseTable] AS SELECT PrimaryKeyCol, ForeignKeyCol, ForeignKeyRank, DataCol FROM ( SELECT DISTINCT ForeignKeyCol FROM dbo.BaseTable ) AS Src CROSS APPLY ( SELECT PrimaryKeyCol, DENSE_RANK() OVER (ORDER BY PrimaryKeyCol) AS ForeignKeyRank, DataCol FROM dbo.BaseTable AS B WHERE B.ForeignKeyCol = Src.ForeignKeyCol ) AS X

Je pense que dans ce cas particulier, cela peut être dû au fait que les types de données entre vos parameters et votre table ne correspondent pas exactement, ainsi SQL Server doit effectuer une conversion implicite qui n'est pas une opération sargable.

Vérifiez vos types de données de table et définissez vos parameters du même type. Ou faites le casting vous-même en dehors de la requête.