Question sys.dm_exec_query_stats interaction avec recompilation


Nous utilisons sys.dm_exec_query_stats pour dépister les requêtes lentes et les requêtes qui sont des contrevenants IO.

Cela fonctionne très bien, nous avons beaucoup de statistiques très perspicaces. Il est clair que ce n'est pas aussi précis que d'exécuter une trace de profileur, car vous ne savez pas quand SQL Server décidera de lancer un plan d'exécution.

Nous avons quelques requêtes où le mauvais plan d'exécution est mis en cache. Par exemple, les requêtes sont les suivantes:

SÉLECTIONNER LE TOP 30
        aide
DE Postes a
        JOIN Posts q ON q.Id = a.ParentId
        JOIN PostTags pt ON q.Id = pt.PostId
O a.PostTypeId = 2
        ET a.DeletionDate EST NULL
        ET a.CommunityOwnedDate IS NULL
        ET a.CreationDate> @date
        ET LEN (a.Body)> 300
        ET pt.Tag = @tag
        ET a.Score> 0
ORDER BY a.Score DESC

Le problème est que le plan idéal dépend vraiment de la date choisie (capture d'écran du plan idéal):

ideal plans

Cependant, si le mauvais plan est mis en cache, il s'étouffe totalement lorsque la plage de dates est grande: (remarquez les grosses lignes)

wrong plan

Pour surmonter cela, il nous a été recommandé d’utiliser soit OPTION (OPTIMIZE FOR UNKNOWN) ou OPTION (RECOMPILE) 

OPTIMIZE FOR UNKNOWN aboutit à un plan légèrement meilleur, ce qui est loin d'être optimal. Les exécutions sont suivies dans sys.dm_exec_query_stats.

RECOMPILE résulte dans le meilleur plan étant choisi, cependant aucune exécution ne compte et les statistiques sont suivies dans sys.dm_exec_query_stats.

Y at-il un autre DMV que nous pourrions utiliser pour suivre les statistiques sur les requêtes avec OPTION (RECOMPILE)? Ce comportement est-il une conception? Y a-t-il un autre moyen de recompiler tout en gardant les statistiques suivies sys.dm_exec_query_stats?

Remarque: la structure exécutera toujours les requêtes paramétrées à l'aide de sp_executesql 


7
2018-03-10 22:02


origine


Est-ce pourquoi serverfault.com/review/first-questions est-ce que je me suis fait chier hier? - squillman
@squillman, il s'agit en réalité de la requête "réponses chaudes", mais ce problème est assez complexe dans beaucoup de nos requêtes. Les premières questions sont sûrement problématiques. - Sam Saffron
Oui, pas d'amour aujourd'hui non plus :) - squillman
@squillman le problème avec la page / review est maintenant résolu, pour des détails sanglants, voir: meta.stackexchange.com/questions/83069/… - Sam Saffron
À quelle fréquence cette requête est-elle exécutée? La date ne constitue peut-être pas le seul élément problématique de la requête: la balise pourrait également faire une grande différence. - Jon Seigel


Réponses:


Peut-être devriez-vous utiliser un guide de plan au lieu de l'option RECOMPILE. Vous avez déjà un bon plan, ajoutez-le donc simplement comme guide de planification pour votre requête. L’optimiseur produira alors ce plan à chaque fois. Voir Optimisation des requêtes dans les applications déployées à l'aide de guides de planification et Spécification de plans de requête avec forçage de plan.

Dans votre cas, c'est vraiment trivial, appelez simplement sp_create_plan_guide_from_handle avec le bon descripteur de plan de requête:

Vous pouvez utiliser cette procédure stockée pour   assurer que l'optimiseur de requête utilise toujours   un plan de requête spécifique pour un   question.


1
2018-03-11 07:05



J'ai en fait deux plans en jeu ici, l'un est efficace pour les plages de dates courtes, l'autre pour les plus longues ... pour le moment, ma solution est de coder simplement la date (qui ne change qu'une fois par jour) un string. Le problème avec lui dire quel plan choisir est que nous devons adapter cela à environ 60 dbs. - Sam Saffron
Utilisez différentes requêtes, une pour les plages courtes, une pour les plages longues. Guidez chacun avec son plan approprié. - Remus Rusanu
J'ai résolu ce problème en utilisant .CreationDate> (GETUTCDATE () - 20), etc., codé en dur dans la chaîne SQL. Cela nous aide dans de nombreux endroits, mais nous sommes coincés avec un bidouillage plus laid dans d’autres endroits. Je suppose qu’il n’existe aucun logiciel DMV permettant de suivre les requêtes de recompilation d’option, et j’imagine que cela est voulu par la conception, sans solution de contournement. - Sam Saffron