Quelles sont les principales différences entre l'OPTION(OPTIMISER POUR les INCONNUS) et OPTION(RECOMPILER)?
- Je exécuter dans le classique La Détection Des Paramètres problèmes de SQL Server 2012. Basé sur quelques recherches, j'ai trouvé plusieurs options autour de ce problème. Les deux options que j'ai besoin de comprendre la différence entre sont OPTION(OPTIMIZE FOR UNKNOWN)
vs OPTION(RECOMPILE)
.
Je suis hésitant pas à utiliser OPTION(RECOMPILE)
à la fin de mes requêtes qui ont ce problème, car cela oblige le serveur pour générer un nouveau plan d'exécution à chaque fois. Si je l'appelle, cette requête est souvent monter le CPU de la machine.
Donc que j'utilise il meilleure solution disponible, quelles sont les réelles différences entre les deux options?
Sera OPTION(OPTIMIZE FOR UNKNOWN)
réutilisation de cache au lieu de les recompiler à chaque fois?
- Comment complexe sont vos questions? Vous ne voulez pas de recompiler des requêtes dans un environnement transactionnel si vous vous attendez à eux de remplir en une fraction de seconde. Mais si elles sont plus de requêtes, puis les frais généraux pour les recompiler est probablement négligeable par rapport à l'exécution réelle.
- Ma compréhension est que
OPTIMIZE FOR UNKNOWN
s'applique au moment de la compilation de la requête. Il raconte l'optimiseur d'ignorer les valeurs de paramètre spécifiques et l'utilisation de ses statistiques pour générer le plan. La résultante de plan de mise en cache et pas recompilé. - Je ne suis pas de l'utiliser dans une transaction. C'est un rapport qui a pour exécuter souvent. Il a environ 10 left join et 1 inner join. La requête elle-même n'est pas compliqué.
- alors, est-il juste de dire que le
OPTION(RECMPILE)
recompilé à chaque fois oùOPTION(OPTIMIZE FOR UNKNOWN)
compile à la fois à l'aide de UNKNOW? doncOPTION(OPTIMIZE FOR UNKNOWN)
est moins cher dans le cas? - Je n'ai pas testé, mais c'est ma compréhension, oui. Toutefois, si le plan de requête n'est pas très bon pour le cas particulier que vous allez utiliser, vous pourriez être mieux avec l'option de recompilation. Parfois, il est évident à partir du plan de requête de savoir si cela fonctionne pour vous ou pas dans votre cas d'utilisation. Si elles ne sont pas évidentes, vous pouvez faire des repères. Recompiler option est cher, mais inefficace des requêtes peut être encore plus coûteux.
Vous devez vous connecter pour publier un commentaire.
Oui, il le fera.
Il y a deux différences principales entre
OPTION(OPTIMIZE FOR UNKNOWN)
etOPTION(RECOMPILE)
comme on peut le voir à partir de cette citation de MSDN:Donc, les deux principales différences sont:
Généralement la requête générée plan est mis en cache et réutilisés.
OPTIMIZE FOR UNKNOWN
n'affecte pas cette fonctionnalité du moteur.RECOMPILE
supprime cette fonction et indique au moteur d'ignorer le plan et ne pas le mettre dans le cache.Généralement optimizer "renifle" les valeurs de paramètre et utilise ces valeurs lors de la création du plan.
OPTIMIZE FOR UNKNOWN
supprime cette fonction et indique au moteur de traiter tous les paramètres, comme si leurs valeurs étaient inconnus. L'optimiseur a intégré les règles et les heuristiques comment utiliser les statistiques disponibles pour les différents critères de filtrage. Voir Optimiser pour les... Médiocre? pour plus de détails. Normalement détection de paramètre est utilisé lors de la première exécution de la requête/procédure stockée et utilise les valeurs de paramètres lors de la première exécution. Le plan généré est mis en cache et plus tard peut être réutilisé.Un non-évident chose à retenir ici est que dans les deux cas (normal, sans indicateurs de requête et avec
OPTIMIZE FOR UNKNOWN
allusion) le plan généré doit être valide et produire de résultat correct pour tout possible valeur de paramètre. Il est adapté à la renifla les valeurs qui ont été utilisées lors du premier run en normal/pas-indice de cas; il n'est pas adapté à une quelconque valeur dans leOPTIMIZE FOR UNKNOWN
cas, mais il est toujours valable même si les changements de paramètres tard de toute façon.C'est important et il empêche l'optimiseur d'effectuer certaines transformations et des simplifications de la plan de.
OPTION(RECOMPILE)
permet à l'optimiseur de inline les valeurs réelles des paramètres lors de chaque course et optimiseur utilise les valeurs réelles des paramètres pour générer un meilleur plan. Il n'a pas à s'inquiéter que le plan peut ne pas fonctionner avec certains autres de la valeur de paramètre, car le plan ne sera pas mis en cache et réutilisés.Cet effet est surtout visible pour les Dynamique Des Conditions De Recherche requêtes. Par exemple:
Si
@ParamSomeID
est0
optimiseur va traiter la requête, comme si elle n'avait pas du toutWHERE
clause. Le plan ne serait pas mentionnerOtherTable
à tous.Si
@ParamSomeID
est-1
, le régime devrait rejoindreT
àOtherTable
à l'aide de la Gauche Anti Semi Rejoindre et de numériser l'ensemble de laOtherTable
.Si
@ParamSomeID
est, disons, 5, le plan serait faire une recherche d'index dans l'index unique surOtherTable
et lu une seule ligne deOtherTable
.Sans
OPTION(RECOMPILE)
ce genre de simplification et de transformation ne serait pas le cas.Une autre raison d'utiliser
OPTION(RECOMPILE)
, c'est quand vos données de distribution est très asymétrique. Par exemple, vous avez une table avec 1M de lignes. Une colonne a la valeur 0 dans 990K des lignes et des valeurs de 1 à 10 en 1K lignes. Les requêtes qui filtre sur cette colonne doit avoir des plans différents en fonction de la valeur réelle du filtre.Dans les deux exemples ci-dessus
OPTIMIZE FOR UNKNOWN
serait de générer un médiocre plan.Oui. Optimiser pour l'inconnu va influencer la façon dont le plan est généré (c'est à dire explicitement l'empêcher de renifler les paramètres et de les comparer avec les données de la colonne de l'histogramme), mais une fois générées, le plan reste dans le cache et est réutilisé.
OPTION(RECOMPILE)
va obliger à recompiler à chaque exécution et est plutôt une approche musclée. Il n'a de sens que dans une analyse DW/BI environnements où chaque requête peut être différent, complexe et probablement avec un important moment de l'exécution.Vous avez aussi d'autres options à votre disposition:
Ces deux vous permettent d'obtenir le même effet que dans votre post, mais de façon non invasive (pas de code de l'application/de la requête de modification).
J'ai utilisé les deux.
OPTION(OPTIMIZE FOR UNKNOWN)
a été utilisé pour une lourde de recherche procédure stockée qui a pris dans une variété de paramètres. Il y avait certaines conditions, inconnu pour moi (statistiques et ce n'est pas), ce serait jeter de l'optimisation off, la requête a été banal, cependant, il pourrait causer de sérieux retards (et en même temps).OPTION(OPTIMIZE FOR UNKNOWN)
résolu ce problème, mais n'était pas idéale.Du très lourd même procédure de recherche aurait des problèmes intermittents, sens après quelques mois, la recherche de bon temps. La solution serait d'appeler
sp_recompile
, qui est synonyme de l'ajout d'unOPTION(RECOMPILE)
de la clause de la procédure stockée.Les entrailles de la procédure stockée a propulsé une "à mesure que vous tapez" solution dans laquelle tous les trois frappes déclencherait une base de données de recherche et les résultats s'affichent dans une liste déroulante.
En fin de compte, j'ai enlevé le
OPTION(OPTIMIZE FOR UNKNOWN)
et tout simplement ajouté unEXEC sp_recompile<sp>
de ma nuit de travail d'entretien et qui a résolu tous les problèmes.