OPTION (RECOMPILE) est toujours plus rapide; pourquoi?
j'ai rencontré une situation étrange où ajouter OPTION (RECOMPILE) à ma requête la fait tourner en une demi-seconde, tout en omettant elle fait que la requête prend bien plus de cinq minutes.
C'est le cas lorsque la requête est exécutée depuis L'Analyseur de requête ou depuis mon programme C# via SqlCommand.ExecuteReader() . Appeler (ou ne pas appeler) DBCC FREEPROCCACHE ou DBCC dropcleanbuffers ne fait aucune différence; les résultats de la requête sont toujours retournés instantanément avec OPTION (RECOMPILE) et plus de cinq quelques minutes sans. La requête est toujours appelée avec les mêmes paramètres [pour l'amour de ce test].
J'utilise SQL Server 2008.
je suis assez à l'aise avec L'écriture SQL mais n'ai jamais utilisé une commande OPTION dans une requête avant et n'était pas familier avec l'ensemble du concept de plan caches jusqu'à la numérisation des messages sur ce forum. D'après ce que j'ai compris dans les billets, OPTION (RECOMPILE) est une opération coûteuse. Il crée apparemment un nouveau recherche de stratégie pour la requête. Alors pourquoi est-ce que les requêtes suivantes qui omettent le OPTION (RECOMPILE) sont si lentes? Les requêtes suivantes ne devraient-elles pas utiliser la stratégie de recherche calculée lors de l'appel précédent qui incluait l'indice de recompilation?
est-il très inhabituel d'avoir une requête qui nécessite un indice de recompilation sur chaque appel?
Désolé pour la question mais je ne peux pas vraiment faire des têtes ou queues de cela.
mise à JOUR: j'ai été demandé de poster la requête...
select acctNo,min(date) earliestDate
from(
select acctNo,tradeDate as date
from datafeed_trans
where feedid=@feedID and feedDate=@feedDate
union
select acctNo,feedDate as date
from datafeed_money
where feedid=@feedID and feedDate=@feedDate
union
select acctNo,feedDate as date
from datafeed_jnl
where feedid=@feedID and feedDate=@feedDate
)t1
group by t1.acctNo
OPTION(RECOMPILE)
lors de l'exécution de l'essai à partir de L'Analyseur de requête, Je prépose les lignes suivantes:
declare @feedID int
select @feedID=20
declare @feedDate datetime
select @feedDate='1/2/2009'
lors de l'appel depuis mon programme C#, les paramètres sont passés via la propriété SqlCommand.Parameters .
Pour les fins de cette discussion, vous pouvez supposer que les paramètres ne changent jamais, donc nous pouvons exclure paramètre sous-optimal sentant comme la cause.
4 réponses
il y a des moments où utiliser OPTION(RECOMPILE) a du sens. D'après mon expérience, le seul moment où c'est une option viable est lorsque vous utilisez le SQL dynamique. Avant de vous demander si cela a du sens dans votre situation, je vous recommande de reconstituer vos statistiques. Cela peut être fait en exécutant ce qui suit:
EXEC sp_updatestats
et puis recréer votre plan d'exécution. Cela garantit que lorsque votre plan d'exécution est créé, il utilisera les informations les plus récentes.
ajouter OPTION(RECOMPILE) reconstruit le plan d'exécution chaque fois que votre requête exécute. Je n'ai jamais entendu que décrit comme creates a new lookup strategy , mais peut-être que nous sommes juste en utilisant des termes différents pour la même chose.
quand une procédure stockée est créée (je soupçonne que vous appelez ad-hoc sql à partir de .NET mais si vous utilisez une requête paramétrée alors cela finit par être un appel proc stocké ) les tentatives de serveur SQL pour déterminer le plus plan d'exécution efficace pour cette requête basée sur les données de votre base de données et les paramètres passés dans ( paramètre reniflant ), puis cache ce plan. Cela signifie que si vous créez la requête où il y a 10 enregistrements dans votre base de données et l'exécutez quand il y a 100.000.000 d'enregistrements le plan d'exécution en cache peut ne plus être le plus efficace.
en résumé - je ne vois aucune raison que OPTION(RECOMPILE) serait un avantage ici. Je suspect vous avez juste besoin de mettre à jour vos statistiques et votre plan d'exécution. Les statistiques de reconstruction peuvent être une partie essentielle du travail de DBA en fonction de votre situation. Si vous avez encore des problèmes après avoir mis à jour vos statistiques, je vous suggérerais d'afficher les deux plans d'exécution.
Et pour répondre à votre question, oui, je dirais qu'il est très inhabituel pour votre meilleure option à recompiler le plan d'exécution chaque fois que vous exécutez la requête.
Souvent, quand il y a une différence radicale d'une exécution d'une requête, je trouve que c'est souvent l'une des 5 questions.
-
statistiques - les statistiques sont périmées. Une base de données stocke des statistiques sur la gamme et la distribution des types de valeurs dans diverses colonnes sur les tableaux et les index. Cela aide le moteur de requête à développer un "Plan" d'attaque pour la façon dont il fera la requête, par exemple le type de méthode qu'il utilisera pour faire correspondre les clés entre les tables en utilisant un hachage ou en regardant à travers l'ensemble. Vous pouvez appeler des statistiques de mise à jour sur l'ensemble de la base de données ou juste certains tableaux ou index. Cela ralentit la requête d'une exécution à l'autre parce que lorsque les statistiques sont périmées, il est probable que le plan de requête n'est pas optimal pour les données nouvellement insérées ou modifiées pour la même requête (expliqué plus tard ci-dessous). Il se peut qu'il ne soit pas approprié de mettre à jour les statistiques immédiatement sur une base de données de Production, car il y aura des frais généraux, ralentir et attendre en fonction de la quantité de données à échantillonner. Vous pouvez également choisir d'utiliser un balayage complet ou un échantillonnage pour mettre à jour les statistiques. Si vous regardez le plan de requête, vous pouvez alors également voir les statistiques sur les index utilisés tels en utilisant la commande DBCC SHOW_STATISTICS (tablename, indexname) . Cela vous montrera la distribution et les plages des clés que le plan de requête utilise pour baser son approche.
-
paramètre SNIFFING - le plan de requête qui est caché n'est pas optimal pour les paramètres particuliers que vous passez, même si la requête elle-même n'a pas changé. Par exemple, si vous passez dans un paramètre qui ne récupère que 10 sur 1.000.000 de lignes, alors le plan de requête créé peut utiliser une jointure de hachage, cependant si le paramètre que vous passez utilisera 750.000 des 1.000.000 lignes, le plan créé peut être un balayage d'index ou de table. Dans une telle situation, vous pouvez dire le SQL déclaration pour utiliser l'option OPTION (RECOMPILE) ou un SP à utiliser avec RECOMPILE. Dire au moteur qu'il s'agit d'un" plan à usage unique " et ne pas utiliser un plan en cache qui ne s'applique probablement pas. Il n'y a pas de règle sur la façon de prendre cette décision, cela dépend de savoir comment la requête sera utilisée par les utilisateurs.
-
INDEXES - il est possible que la requête n'ait pas changé, mais un changement ailleurs tel que la suppression d'un index très utile a ralenti la requête.
-
les lignes changent radicalement d'un appel à l'autre. En général, les statistiques sont automatiquement mises à jour dans ces cas. Cependant, si vous construisez du SQL dynamique ou si vous appelez du SQL dans une boucle serrée, il est possible que vous utilisiez un plan de requête obsolète basé sur le nombre drastique erroné de lignes ou de statistiques. Encore dans ce cas L'OPTION (recompiler) est utile.
-
LA LOGIQUE c'est Logique, votre requête n'est plus efficace, il a été très bien pour un petit nombre de lignes, mais ce n'est plus les échelles. Cela implique généralement une analyse approfondie du Plan de Requête. Par exemple, vous ne pouvez plus faire des choses en vrac, mais devez fractionner les choses et faire de plus petites Commits, ou votre produit croisé était très bien pour un ensemble plus petit, mais prend maintenant CPU et de la mémoire comme il échelles plus grandes, cela peut aussi être vrai pour L'utilisation de DISTINCT, vous appelez une fonction pour chaque ligne, vos correspondances de clés n'utilisent pas un index en raison de la conversion de type de moulage ou NULLS ou des fonctions... Trop de possibilités ici.
en général, lorsque vous écrivez une requête, vous devez avoir une certaine image mentale de la façon dont certaines données sont distribuées dans votre table. Une colonne par exemple, peut avoir un nombre uniformément distribué de différentes valeurs, ou il peut être biaisé, 80% du temps ont un ensemble spécifique de valeurs, si la distribution varie fréquemment dans le temps ou être assez statique. Cela vous donnera une meilleure idée de la façon de construire une requête efficace. Mais aussi lorsque la performance de requête de débogage ont une base pour construire une hypothèse quant à la raison pour laquelle il est lent ou inefficace.
pour ajouter à l'excellente liste (donnée par @CodeCowboyOrg) des situations où L'OPTION (RECOMPILE) peut être très utile,
- Variables De Table . Lorsque vous utilisez les variables de tableau, il n'y aura pas de statistiques pré-construites pour la variable de tableau, conduisant souvent à de grandes différences entre les lignes estimées et réelles dans le plan de requête. L'utilisation de L'OPTION (recompiler) sur les requêtes avec des variables de table permet la génération d'un plan de requête qui a une bien meilleure estimation du nombre de rangées concernées. J'avais une utilisation particulièrement critique d'une variable de table qui était inutilisable, et que j'allais abandonner, jusqu'à ce que j'ajoute OPTION(RECOMPILE). Le temps d'exécution est allé de quelques heures à quelques minutes. C'est probablement inhabituel, mais dans tous les cas, si vous utilisez des variables de table et que vous travaillez sur l'optimisation, il vaut la peine de voir si L'OPTION(RECOMPILE) fait une différence.
les toutes premières actions avant tunning requêtes est de défragmenter/reconstruire les indices et les statistiques, autrement vous perdez votre temps.
vous devez vérifier le plan d'exécution pour voir s'il est stable (est le même lorsque vous changez les paramètres), sinon, vous pourriez avoir à créer un index de couverture (dans ce cas pour chaque table) (sachant que le système vous pouvez créer un qui est utile pour d'autres requêtes aussi).
comme exemple : créer des index idx01_datafeed_trans On datafeed_trans ( feedid, feedDate)) comprend (acctNo, tradeDate)
si le plan est stable ou si vous pouvez le stabiliser, vous pouvez exécuter la phrase avec sp_executesql('phrase sql') pour sauvegarder et utiliser un plan d'exécution fixe.
si le plan est instable, vous devez utiliser une instruction ad-hoc ou EXEC('phrase sql') pour évaluer et créer un plan d'exécution à chaque fois. (ou une procédure stockée "avec recompiler.)"
J'espère que ça aidera.