Comment identifier les query couteux en CPU

L'utilisation des Dynamic Management Views permettent d'obtenir des informations bien utiles.
C'est ainsi que la requête suivante identifie les requêtes sql les plus couteuses (par cpu) parmis celles enregistrées dans le cache.

SELECT TOP 20
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
        qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
        (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
        qs.total_elapsed_time/1000000,
   st.text,
   qp.query_plan
from
    sys.dm_exec_query_stats as qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
    cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
ORDER BY qs.total_worker_time desc

Cette requête retourne la requête Sql (inclus stored proc) mais également l'exécution plan!
Source:

• How to identify the most costly SQL Server queries by CPU