Règle générale sur l'utilisation de l'option WITH RECOMPILE

Je comprends que l'option with RECOMPILE les forces de l'optimiseur pour reconstruire le plan de requête pour les procs, mais quand vous voulez que cela se produise?

Ce sont quelques règles de base sur l'utilisation de l'option with RECOMPILE et quand ne pas le faire?

Quelle est l'efficacité de frais généraux associés à la il suffit de le mettre sur chaque procédure stockée?

source d'informationauteur Bob Probst

  1. 16

    Comme d'autres l'ont dit, vous ne voulez pas de simplement inclure WITH RECOMPILE dans chaque procédure stockée comme une question d'habitude. En faisant cela, vous seriez en éliminant l'un des principaux avantages de procédures stockées: le fait qu'il permet d'économiser le plan de requête.

    Pourquoi est-ce que potentiellement une grosse affaire? Le calcul d'un plan de requête est beaucoup plus intense que la compilation régulière code de procédure. Parce que la syntaxe d'une instruction SQL spécifie uniquement ce vous voulez, et non pas (en général), comment pour l'obtenir, qui permet à la base de données un grand degré de flexibilité lors de la création du plan physique (c'est l'étape par étape les instructions pour fait de recueillir des données et de les modifier). Il y a beaucoup de "trucs" à la requête de base de données pré-processeur peut faire et les choix qu'il peut faire - ce que pour joindre les tables, les index pour les utiliser, que ce soit pour appliquer WHERE clauses avant ou après les jointures, etc.

    Pour une instruction SELECT simple, il ne pourrait pas faire une différence, mais pour n'importe quel non-trivial de la requête, la base de données va passer un peu de temps (mesuré en millisecondes, contrairement à l'habitude microsecondes) à venir avec un plan optimal. Pour vraiment requêtes complexes, il ne peut même pas garantir une optimale plan, il suffit d'utiliser des heuristiques pour trouver une assez bonne plan. Donc, en l'obligeant à recompiler à chaque fois, vous avez dit qu'il doit passer par le processus encore et encore, même si le plan qu'il a eu avant était parfaitement bon.

    Selon le vendeur, il devrait être automatique des déclencheurs pour recompiler les plans de requête - par exemple, si les statistiques sur une table de changer de manière significative (par exemple, l'histogramme des valeurs dans une colonne spécifique commence uniformément distribué par au fil du temps devient très inégale), puis la DB est à noter que et de recompiler le plan. Mais d'une manière générale, la mise en œuvre d'une base de données vont être plus intelligent que l'ensemble des que vous.

    Comme tout ce qui est lié à la performance, ne pas prendre des photos dans l'obscurité; la figure où les goulets d'étranglement qui coûtent 90% de vos performances, et de les résoudre en premier.

  2. 14

    De le mettre sur chaque procédure stockée n'est PAS une bonne idée, parce que la compilation d'un plan de requête est une opération relativement coûteuse et vous ne verrez pas de bénéficier de la requête des plans de mise en cache et ré-utilisés.

    Le cas d'une dynamique où la clause construit à l'intérieur d'une procédure stockée peut être manipulé à l'aide de sp_executesql pour exécuter le TSQL plutôt que d'ajouter WITH RECOMPILE à la procédure stockée.

    Une autre solution (SQL Server à partir de 2005) est l'utilisation de l'indice des paramètres spécifiques à l'aide de la OPTIMIZE FOR soupçon. Cela fonctionne bien si les valeurs dans les lignes sont statiques.

    SQL Server 2008 a mis en place un peu connu appelé "OPTIMIZE FOR UNKNOWN":

    Cette astuce dirige l'optimiseur de requête
    pour utiliser les algorithmes standards qu'il a
    toujours utilisé si aucune des valeurs de paramètres
    avait été transmise à la requête.
    Dans ce cas, l'optimiseur de look
    à toutes les données statistiques disponibles à
    parvenir à une détermination de ce que l'
    les valeurs des variables locales utilisées pour
    générer la queryplan devrait être,
    au lieu de chercher à la
    les valeurs de paramètre qui lui ont été transmis
    la requête par l'application.

  3. 2

    L'usage le plus commun est lorsque vous avez une dynamique clause where dans une procédure...vous ne voudriez pas que ce plan de requête pour obtenir compilé et enregistré pour les exécutions suivantes, car il pourrait très bien ne pas être exactement la même clause, la prochaine fois que la procédure est appelée.

  4. 2

    généralement une bien meilleure alternative à WITH RECOMPILE est OPTION(RECOMPILE)
    comme vous pouvez le voir sur l'explication ci-dessous, prises de la réponse à cette question ici

    Lorsqu'un paramètre de sensibilité problème est rencontré, une pièce commune de
    des conseils sur les forums et les Q&Un des sites est "l'utilisation de recompiler" (en supposant que le
    d'autres options de paramétrage présentés précédemment ne sont pas adaptés). Malheureusement,
    ce conseil est souvent interprétée à tort AVEC l'ajout de RECOMPILER
    option à la procédure stockée.

    AVEC l'aide de RECOMPILER effectivement nous renvoie à SQL Server 2000
    comportement, où l'ensemble de la procédure stockée est recompilé à chaque
    l'exécution. Une meilleure alternative, sur SQL Server 2005 et versions ultérieures, est à
    utilisez l'OPTION (RECOMPILER) indicateur de requête sur la déclaration que
    souffre à partir du paramètre renifleurs de problème. Cet indicateur de requête les résultats
    dans une recompilation de la problématique de résultat; les plans d'exécution
    pour d'autres instructions à l'intérieur de la procédure stockée sont mises en cache et réutilisés
    comme d'habitude.

    AVEC l'aide de RECOMPILER signifie également le plan compilé pour la stockées
    la procédure n'est pas mis en cache. En conséquence, aucune information sur le rendement est
    maintenu dans Dmv comme sys.dm_exec_query_stats. L'aide de la requête
    astuce au lieu de cela signifie que la compilation d'un plan peut être mis en cache, et de la performance
    l'information est disponible dans le Dmv (même si elle est limitée à la plus
    exécution récente, de la déclaration).

    Pour les instances en cours d'exécution, au moins, SQL Server 2008 construire 2746 (Service
    Pack 1 avec la mise à Jour Cumulative 5), l'utilisation de l'OPTION (RECOMPILER) a un autre
    avantage significatif par rapport À RECOMPILER: uniquement en OPTION (RECOMPILER)
    permet le Paramètre d'Intégration de l'Optimisation.

  5. 1

    Il ne doit être utilisé lors de l'essai avec reprentative les données et le contexte de démontrer que l'on fait sans produit non valide les plans de requête (quelles que soient les raisons possibles peuvent être). Ne présumez pas à l'avance (sans test) que le PS n'optimise pas correctement.

    Seule exception pour l'appel manuel uniquement (c'est à dire ne pas de code dans le SP): Quand vous savez que vous avez considérablement modifié le caractère de la table cible. par exemple, TRONQUER, chargement en vrac, etc.

    C'est encore une autre occasion pour l'optimisation prématurée.

    Remarque: j'ai beaucoup de points. Si un newby soumet la même réponse ci-dessous, et vous acceptez, upvote leur.