MS-SQL DB 속도 느려질때 확인방법

--악성쿼리(부하)가 높은 query 확인 
--내용 : 내DB에 부하가 많은 쿼리를 순으로 원하는 순위만큼 뽑은 쿼리다.
--DMV(동적관리뷰)가 이용되었고 이용하려면 'VIEW SERVER STATE' 권한이 필요함              
select top 20 qt.text SP,
              substring(qt.text, (qs.statement_start_offset/2)+2,
                        (( case qs.statement_end_offset when -1 then datalength(qt.text) else qs.statement_end_offset end - qs.statement_start_offset)/2)+1) 수행쿼리,
              qs.execution_count 쿼리계획이_마지막컴파일후_실행횟수,
              qs.total_logical_reads 쿼리계획이_컴파일후_실행될때_수행한_총논리적읽기수,
              qs.last_logical_reads 마지막_수행한_논리적읽기수,
              qs.total_logical_writes 쿼리계획이_컴파일후_실행될때_수행한_총논리적쓰기수,
              qs.last_logical_writes 마지막_수행한_논리적쓰기수,
              qs.total_worker_time [쿼리계획이_컴파일후_실행될때_사용된_총CPU시간(마이크로초)],
              qs.last_worker_time [마지막_실행될때_사용된_CPU시간(마이크로초)],
              qs.total_elapsed_time/1000000 [실행완료하는데 사용된 총 경과시간(마이크로초)_초로변환],
              qs.last_elapsed_time/1000000 [최근실행완료하는데 소요된 경과시간(마이크로초)_초로변환],
              qs.last_execution_time 해달쿼리가_마지막으로_실행된_시간,
              qp.query_plan 실행계획
 from sys.dm_exec_query_stats qs
 cross apply sys.dm_exec_sql_text(qs.sql_handle) qt
 cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
 order by qs.last_worker_time DESC
 -- order by qs.total_logical_reads DESC
 -- order by qs.totla_logical_writes DESC
 -- order by qs.total_worker_time DESC

 --SP에 관련된 비용이나 실행속도, 샐행수를 보려면 아래문장으로 보면됨
 select top 50 qt.text as 'SP명',
        qs.execution_count as '실행수',
  qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) as '[실행수/초]',
  qs.total_worker_time/qs.execution_count as '[작동시간/실행수]',
  qs.total_worker_time as '총작동시간',
  qs.total_elapsed_time/qs.execution_count as 'AvgElapsedTime',
  qs.max_logical_reads [논리적읽기수],
  qs.max_logical_writes [논리적쓰기수],
  qs.total_physical_reads [물리적읽기수],
  DATEDIFF(Minute, qs.creation_time, GetDate()) as 'Age in Cache'
 from sys.dm_exec_query_stats as qs
 cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
 where qt.dbid = db_id()  --Filter by current database
 --order by qs.execution_count desc  --샐행수별
 order by qs.total_worker_time DESC   --총작동시간별
 --order by qs.total_logical_reads DESC  --total_logical_reads
 --order by qs.total_physical_reads DESC  --total_physical_reads
 --order by qs.total_logical_writes DESC  --total_logical_wrietes