SQL Server 查找占用CUP内存的SQL

SqlServer2008，系统巨慢，8核CPU都达到99%，内存占用超过10G，使用下边sql确定罪魁祸首，建索引改sql。

'=======================================================

'最占内存的sql

'=======================================================

select q.query_hash,   q.number_of_entries,   t.text as sample_query,   p.query_plan as sample_plan  

from (

    select top 20 query_hash,   count(*) as number_of_entries,   min(sql_handle) as sample_sql_handle,   

        min(plan_handle) as sample_plan_handle  

    from sys.dm_exec_query_stats  

    group by query_hash  

    having count(*) > 1  

    order by count(*) desc

        ) as q  

cross apply sys.dm_exec_sql_text(q.sample_sql_handle) as t  

cross apply sys.dm_exec_query_plan(q.sample_plan_handle) as p

'=======================================================

'最占CPU的sql

'=======================================================

select q.total_cpu_time, q.total_execution_count, t.text as sample_query, p.query_plan as sample_plan  

from (

    select top 50   

        sum(total_worker_time) as total_cpu_time,   sum(execution_count) as total_execution_count,  

        count(*) as  number_of_statements,   plan_handle   

    from   sys.dm_exec_query_stats

    group by plan_handle  

    order by sum(total_worker_time) desc  

) as q

cross apply sys.dm_exec_sql_text(q.plan_handle) as t  

cross apply sys.dm_exec_query_plan(q.plan_handle) as p