Discussion :

[Christophe Charron]

Bonjour,

je cherche à identifier les requêtes les plus gourmandes dans une base de données précise.
Je pensais pouvoir récupérer l'identifiant de la base dans "sys.dm_exec_sql_text" via une requête de ce genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT TOP 500 st.text,st.dbid,st.encrypted,st.objectid,qs.*   
FROM sys.dm_exec_query_stats AS qs   
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st   
ORDER BY execution_count DESC,creation_time DESC;

Mais pour toutes les requêtes dont je suis certain qu'elles attaquent ma base en question, j'ai "dbid = NULL"

Me fourvoie-je en espérant obtenir l'identifiant de la base ainsi ? Puis-je l'obtenir d'une autre manière ?

D'avance, merci pour vos lumières.

[elsuket]

Bonjour,

En fait cette colonne comme la colonne objectid vous poitent vers l'objet de type module, c'est à dire une procédure stockée, une vue, un trigger ou une fonction dans une base de données particulière.
Si certaines des requêtes sont soumises au moteur à l'aide de sp_executesql par exemple, alors ces colonnes sont à NULL.
Dans ce cas vous pouvez vous en remettre à la DMV sys.dm_exec_plan_attributes, en filtrant sur la colonne attribute = 'dbid' et value = l'ID de la base sur laquelle vous souhaitez filtrer.
Attention, cette dernière colonne est de type sql_variant

@++

[Oishiiii]

Hello,

Il y a article Connect à ce sujet : sys.dm_exec_query_stats DBID column NULL for dynamic SQL

[Christophe Charron]

Merci à tous les deux pour vos éclairages.

@elsuket : je n'ai pas compris votre réponse d'autant mais ...
@Oishiiii : grâce à votre lien, j'ai pu construire une requête qui, a priori, fait ce que je veux :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT TOP 500st.text,st.dbid,st.encrypted,st.objectid,qs.*   
FROM sys.dm_exec_query_stats AS qs   
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
where qs.sql_handle in (SELECT pvt.sql_handle
FROM (
    SELECT plan_handle, epa.attribute, epa.value 
    FROM sys.dm_exec_cached_plans 
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
    WHERE cacheobjtype = 'Compiled Plan' AND objtype = 'adhoc') AS ecpa 
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("dbid", "sql_handle")) AS pvt 
where pvt.dbid=12)
ORDER BY execution_count DESC,creation_time DESC;

En fait, non, je ne récupère pas les "bonnes" requêtes.
En pièces jointes
un fichier export1.txt correspondant à l'export du résultat de la requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT st.text,st.dbid,st.encrypted,st.objectid,qs.*   
FROM sys.dm_exec_query_stats AS qs   
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st 
where st.text like '%paktsoc%'  
ORDER BY execution_count DESC,creation_time DESC;

un fichier export2.txt correspondant à l'export du résultat de la requête citée plus haut.

Et j'ai l'impression que dans mon second export, je ne retrouve que des requêtes exécutées dans Microsoft SQL Manager Studio
En tout cas aucune de celle auxquelles je m'attendais.

Une recherche de paktsoc dans les 2 fichiers est significative...

[Christophe Charron]

Cette fois ci, je retrouve mes billes.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT TOP 500 st.text,st.dbid,st.encrypted,st.objectid,qs.*   
FROM sys.dm_exec_query_stats AS qs   
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
where qs.plan_handle in (SELECT pvt.plan_handle
FROM (
    SELECT plan_handle, epa.attribute, epa.value 
    FROM sys.dm_exec_cached_plans 
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
    WHERE cacheobjtype = 'Compiled Plan' AND objtype = 'prepared') AS ecpa 
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("dbid", "sql_handle")) AS pvt 
)
ORDER BY execution_count DESC,creation_time DESC;

Il fallait "juste" conditionner objtype= 'prepared' à la place de objtype = 'adhoc'
Je vais continuer mes recherches en orientant désormais sur le query_plan_hash, plus rassembleur.

Merci encore