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,   

          mi n(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