Détection de paramètre (ou usurpation) dans SQL Server

Question

Il y a un moment j'avais une question que j'ai couru beaucoup pour un de mes utilisateurs. Il était encore évolué et s'est tordu mais finalement, il stablised et a couru assez vite, nous avons donc créé une procédure stockée.

Pour l'instant, donc normal.

La procédure stockée, cependant, était le chien de ralentir. Pas de différence entre la requête et le proc, mais le changement de vitesse a été massive.

[Arrière-plan, nous sommes en cours d'exécution de SQL Server 2005.]

Une amicale de l'administrateur local (qui ne travaille plus ici) a pris un coup d'oeil à la procédure stockée et dit "paramètre de l'usurpation d'!" (Edit: bien qu'il semble qu'il est peut-être aussi connu comme 'le paramètre sniffing", ce qui pourrait expliquer le manque de résultats Google quand j'ai essayé de le chercher.)

Nous abstraite de certains de la procédure stockée à une seconde, enveloppé à l'appel de cette nouvelle intérieure proc dans le pré-existants extérieur, appelé de l'extérieur et, hop, il était aussi rapide que l'original de la requête.

Donc, ce qui donne? Quelqu'un peut m'expliquer paramètre de la mystification?

Bonus de crédit pour

• en soulignant comment l'éviter
• ce qui suggère comment reconnaître la cause possible
• discuter des stratégies alternatives, par exemple, les statistiques, les indices, les clés, pour atténuer la situation

Answer 1

FYI - vous besoin d'être conscient de quelque chose d'autre lorsque vous travaillez avec SQL 2005 et stockées procs avec des paramètres.

Le Serveur SQL compiler les procédures stockées du plan d'exécution avec le premier paramètre qui est utilisé. Donc, si vous exécutez ce:

usp_QueryMyDataByState 'Rhode Island'

Le plan d'exécution fonctionne le mieux avec un petit état des données. Mais si quelqu'un se retourne et s'exécute:

usp_QueryMyDataByState 'Texas'

Le plan d'exécution conçu pour Rhode-Island-taille des données peut ne pas être aussi efficace avec Texas de la taille des données. Cela peut produire des résultats surprenants lorsque le serveur est redémarré, parce que le nouveau plan d'exécution généré sera ciblée, quel que soit le paramètre est utilisé en premier - pas nécessairement le meilleur. Le plan ne sera pas recompilé jusqu'à ce qu'il y a une grande raison de le faire, comme si les statistiques sont reconstruits.

C'est là que les plans de requête, SQL Server 2008 offre un lot de nouvelles fonctionnalités qui aident les Administrateurs de bases de données broche une requête particulière de plan à long terme de n'importe quels paramètres appelés en premier.

Mon souci est que lorsque vous reconstruit votre procédure stockée, vous avez forcé le plan d'exécution de recompiler. Vous l'avez appelé avec votre favori paramètre, et puis bien sûr, il était rapide, mais le problème peut ne pas avoir été la procédure stockée. Il peut avoir été que la procédure stockée a été recompilée à un certain point avec une rare ensemble de paramètres et donc, de l'inefficacité d'un plan de requête. Vous pourriez ne pas avoir fixé de rien, et vous pourriez faire face au même problème la prochaine fois que le serveur redémarre ou le plan de requête obtient recompilé.

Answer 2

Oui, je pense que tu veux dire la détection des paramètres, qui est une technique que l'optimiseur SQL Server utilise pour essayer de comprendre les valeurs de paramètre/gammes de sorte qu'il peut choisir le meilleur plan d'exécution de votre requête. Dans certains cas, SQL Server a fait un mauvais travail à la détection des paramètres et ne pas choisir le meilleur plan d'exécution de la requête.

Je crois que cet article de blog http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx a une bonne explication.

Il semble que le DBA dans votre exemple a choisi l'option n ° 4 pour déplacer la requête à une autre procédure stockée à une procédure séparé contexte.

Vous pourriez avoir également utilisé le avec recompiler sur l'original de la procédure stockée ou utilisée de l' optimiser pour les option du paramètre.

Answer 3

Une façon simple de vitesse qui est à réaffecter les paramètres d'entrée pour les paramètres locaux dans le début de la procédure stockée, par exemple

CREATE PROCEDURE uspParameterSniffingAvoidance

DECLARE @SniffAvoidingLocalParameter int
SET @SniffAvoidingLocalParameter = @SniffedFormalParameter

--Le travail w/ @SniffAvoidingLocalParameter dans la procédure stockée corps
-- ...

Answer 4

La détection des paramètres est une technique SQL Server utilise pour optimiser le plan d'exécution de requête pour une procédure stockée. Lors du premier appel de la procédure stockée SQL Server regarde la donnée des valeurs de paramètre de votre appel et décide des indices à utiliser basée sur les valeurs de paramètre.

Ainsi, lorsque le premier appel contient pas très typiques des paramètres, SQL Server peut sélectionner et de mémoriser un sous-optimale du plan d'exécution en ce qui concerne les appels suivants de la procédure stockée.

Vous pouvez contourner ce problème en soit

• à l'aide de WITH RECOMPILE
• copie les valeurs de paramètres pour les variables locales à l'intérieur d'une procédure stockée et à l'aide de la population locale dans vos requêtes.

J'ai même entendu dire qu'il est préférable de ne pas utiliser des procédures stockées à tous, mais à nous envoyer vos demandes directement au serveur. Je suis récemment tombé sur le même problème lorsque je n'ai pas de réelle solution pour le moment. Pour certaines requêtes de la copie locale de vars permet de revenir à la bonne exécution du plan, pour certaines requêtes, les performances se dégradent avec local de vars.

J'ai encore à faire plus de recherches sur la façon dont SQL Server caches et réutilise (sous-optimal) les plans d'exécution.

Answer 5

Dans mon expérience, la meilleure solution pour la détection des paramètres Dynamiques SQL'. Deux choses importantes à noter est que 1. vous devez utiliser des paramètres dans votre dynamique de requêtes sql 2. vous devez utiliser sp_executesql (et pas sp_execute), ce qui économise le plan d'exécution pour chacune des valeurs de paramètre