L'OPTION (RECOMPILE) est toujours plus rapide ; pourquoi ?

Question

J'ai rencontré une situation étrange où l'ajout de OPTION (RECOMPILE) à ma requête la fait s'exécuter en une demi-seconde, alors que si je l'omets, la requête prend bien plus de cinq minutes.

C'est le cas lorsque la requête est exécutée depuis Query Analyzer ou depuis mon programme C# via SqlCommand.ExecuteReader() . Appeler (ou ne pas appeler) DBCC FREEPROCCACHE o DBCC dropcleanbuffers ne fait aucune différence ; les résultats des requêtes sont toujours renvoyés instantanément avec OPTION (RECOMPILE) et plus de cinq minutes sans. La requête est toujours appelée avec les mêmes paramètres [pour les besoins de ce test].

J'utilise SQL Server 2008.

Je suis assez à l'aise avec l'écriture du SQL mais je n'ai jamais utilisé un OPTION dans une requête et je n'étais pas familier avec le concept de cache de plan jusqu'à ce que je consulte les messages sur ce forum. D'après ce que j'ai lu dans ces messages, j'ai compris que OPTION (RECOMPILE) est une opération coûteuse. Elle crée apparemment une nouvelle stratégie de recherche pour la requête. Alors pourquoi les requêtes ultérieures qui omettent l'élément OPTION (RECOMPILE) sont si lents ? Les requêtes suivantes ne devraient-elles pas utiliser la stratégie de recherche calculée lors de l'appel précédent, qui comprenait l'indice de recompilation ?

Est-il très rare d'avoir une requête qui nécessite un indice de recompilation à chaque appel ?

Désolé pour cette question de base, mais je n'arrive pas à comprendre ce qui se passe.

UPDATE : On m'a 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)

Lorsque j'exécute le test à partir de Query Analyzer, je fais précéder les lignes suivantes :

declare @feedID int
select @feedID=20

declare @feedDate datetime
select @feedDate='1/2/2009'

Lorsque je l'appelle à partir de mon programme C#, les paramètres sont transmis par l'intermédiaire de la fonction SqlCommand.Parameters propriété.

Pour les besoins de cette discussion, vous pouvez supposer que les paramètres ne changent jamais, ce qui nous permet d'exclure la cause d'un reniflement sous-optimal des paramètres.

Answer 1

Il y a des moments où l'utilisation OPTION(RECOMPILE) a du sens. D'après mon expérience, la seule fois où cette option est viable est lorsque vous utilisez le SQL dynamique. Avant d'examiner si cela a un sens dans votre situation, je vous recommande de reconstruire vos statistiques. Cela peut être fait en exécutant la commande suivante :

EXEC sp_updatestats

Et ensuite recréer votre plan d'exécution. Cela garantira que lorsque votre plan d'exécution sera créé, il utilisera les dernières informations.

Ajout de OPTION(RECOMPILE) reconstruit le plan d'exécution à chaque fois que votre requête s'exécute. Je n'ai jamais entendu dire que cela était décrit comme creates a new lookup strategy mais peut-être que nous utilisons simplement des termes différents pour la même chose.

Lorsqu'une procédure stockée est créée (je soupçonne que vous appelez le sql ad-hoc depuis .NET mais si vous utilisez une requête paramétrée, il s'agit d'un appel à une procédure stockée. ) Le serveur SQL tente de déterminer le plan d'exécution le plus efficace pour cette requête en fonction des données de votre base de données et des paramètres transmis ( reniflage des paramètres ), puis met en cache ce plan. Cela signifie que si vous créez la requête lorsque votre base de données contient 10 enregistrements et que vous l'exécutez lorsqu'il y a 100 000 000 d'enregistrements, le plan d'exécution mis en cache peut ne plus être le plus efficace.

En résumé - je ne vois aucune raison pour que OPTION(RECOMPILE) serait un avantage ici. Je suppose que vous devez juste mettre à jour vos statistiques et votre plan d'exécution. Reconstruire les statistiques peut être une partie essentielle du travail de DBA selon votre situation. Si vous avez toujours des problèmes après avoir mis à jour vos statistiques, je vous suggère de poster les deux plans d'exécution.

Pour répondre à votre question, oui, je dirais qu'il est très inhabituel que votre meilleure option soit de recompiler le plan d'exécution à chaque fois que vous exécutez la requête.

Answer 2

Souvent, lorsqu'il y a une différence radicale d'une exécution à l'autre d'une requête, je constate qu'il s'agit souvent de l'un des cinq problèmes suivants.

1. STATISTIQUES - Les statistiques sont dépassées. Une base de données stocke des statistiques sur l'étendue et la distribution des types de valeurs dans diverses colonnes des tables et des index. Cela aide le moteur de recherche à développer un "plan" d'attaque pour la façon dont il effectuera la recherche, 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 examinant l'ensemble complet. Vous pouvez appeler Update Statistics sur l'ensemble de la base de données ou seulement sur certaines tables ou index. Cela ralentit la requête d'une exécution à l'autre, car lorsque les statistiques ne sont pas à jour, il est probable que le plan de requête ne soit pas optimal pour les données nouvellement insérées ou modifiées pour la même requête (voir ci-dessous). Il n'est pas forcément approprié de mettre à jour les statistiques immédiatement sur une base de données de production, car il y aura une certaine surcharge, un ralentissement et un décalage 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 également voir les statistiques sur les index utilisés en utilisant la commande suivante DBCC SHOW_STATISTICS (nom du tableau, nom de l'index) . Cela vous montrera la distribution et les plages des clés que le plan de requête utilise pour fonder son approche.

2. RENIFLAGE DES PARAMÈTRES - Le plan de requête qui est mis en cache n'est pas optimal pour les paramètres particuliers que vous transmettez, même si la requête elle-même n'a pas changé. Par exemple, si vous passez un paramètre qui ne récupère que 10 des 1 000 000 de lignes, le plan de requête créé peut utiliser un Hash Join, mais si le paramètre que vous passez utilise 750 000 des 1 000 000 de lignes, le plan créé peut être un index scan ou un table scan. Dans une telle situation, vous pouvez indiquer à l'instruction SQL d'utiliser l'option OPTION (RECOMPILATION) ou un SP pour utiliser WITH RECOMPILE. Pour indiquer au moteur qu'il s'agit d'un "plan à usage unique" et non d'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 la façon dont la requête sera utilisée par les utilisateurs.

3. INDEXES - Il est possible que la requête n'ait pas changé, mais qu'un changement ailleurs, comme la suppression d'un index très utile, ait ralenti la requête.

4. LIGNES MODIFIÉES - Les lignes que vous interrogez changent radicalement d'un appel à l'autre. Habituellement, 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 mauvais nombre de lignes ou de statistiques. Encore une fois, dans ce cas OPTION (RECOMPILATION) est utile.

5. LA LOGIQUE C'est la Logique, votre requête n'est plus efficace, elle était bien pour un petit nombre de lignes, mais elle n'évolue plus. Cela implique généralement une analyse plus approfondie du plan de requête. Par exemple, vous ne pouvez plus faire les choses en vrac, mais vous devez les fragmenter et faire de plus petits commits, ou votre produit croisé était bien pour un ensemble plus petit mais prend maintenant beaucoup de CPU et de mémoire quand il s'agrandit, cela peut aussi être vrai pour l'utilisation de DISTINCT, vous appelez une fonction pour chaque ligne, vos correspondances clés n'utilisent pas d'index à cause de la conversion de type CASTING ou NULLS ou fonctions... Trop de possibilités ici.

En général, lorsque vous écrivez une requête, vous devez avoir une idée de la façon dont certaines données sont distribuées dans votre table. Une colonne, par exemple, peut avoir un nombre de valeurs différentes distribuées de manière égale, ou elle peut être asymétrique, avoir 80% du temps un ensemble spécifique de valeurs, que la distribution varie fréquemment dans le temps ou qu'elle soit assez statique. Cela vous donnera une meilleure idée de la façon de construire une requête efficace. Mais aussi, lorsque vous déboguez les performances d'une requête, vous disposez d'une base pour élaborer une hypothèse sur les raisons de sa lenteur ou de son inefficacité.

Answer 3

Pour ajouter à l'excellente liste (donnée par @CodeCowboyOrg) de situations où OPTION(RECOMPILE) peut être très utile,

6. Variables du tableau . Lorsque vous utilisez des variables de table, il n'y aura pas de statistiques préétablies pour la variable de table, ce qui entraîne souvent de grandes différences entre les lignes estimées et réelles dans le plan de requête. L'utilisation de OPTION(RECOMPILE) sur les requêtes avec des variables de table permet de générer un plan de requête qui a une bien meilleure estimation du nombre de lignes impliqué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 passé de plusieurs heures à quelques minutes seulement. C'est probablement inhabituel, mais dans tous les cas, si vous utilisez des variables de table et travaillez sur l'optimisation, cela vaut la peine de voir si OPTION(RECOMPILE) fait une différence.

Answer 4

La première chose à faire avant d'optimiser les requêtes est de défragmenter/reconstruire les index et les statistiques, sinon vous perdez votre temps.

Vous devez vérifier le plan d'exécution pour voir s'il est stable (reste le même lorsque vous changez les paramètres), si non, vous devrez peut-être créer un index de couverture (dans ce cas pour chaque table) (connaissant le système vous pouvez en créer un qui est utile pour d'autres requêtes aussi).

par exemple : créer un index idx01_datafeed_trans Sur datafeed_trans ( feedid, feedDate) INCLUDE( acctNo, tradeDate)

si le plan est stable ou si vous pouvez le stabiliser, vous pouvez exécuter la phrase avec sp_executesql('sql phrase') pour sauvegarder et utiliser un plan d'exécution fixe.

si le plan est instable, vous devez utiliser une instruction ad-hoc ou EXEC('sql sentence') pour évaluer et créer un plan d'exécution à chaque fois. (ou une procédure stockée "avec recompilation").

J'espère que cela vous aidera.

Answer 5

Cette question est récurrente, mais il y a une explication que personne ne semble avoir envisagée.

STATISTIQUES - Les statistiques ne sont pas disponibles ou sont trompeuses.

Si tous les éléments suivants sont vrais :

1. Les colonnes feedid et feedDate sont susceptibles d'être fortement corrélées (par exemple, un feed id est plus spécifique qu'une date de feed et le paramètre date est une information redondante).
2. Il n'y a pas d'index avec les deux colonnes comme colonnes séquentielles.
3. Il n'y a pas de statistiques créées manuellement couvrant ces deux colonnes.

Dans ce cas, le serveur SQL peut supposer à tort que les colonnes ne sont pas corrélées, ce qui entraîne des estimations de cardinalité plus faibles que prévu pour l'application des deux restrictions et la sélection d'un plan d'exécution médiocre. Dans ce cas, la solution consiste à créer un objet statistique reliant les deux colonnes, ce qui n'est pas une opération coûteuse.