'SQL SERVER' 카테고리의 다른 글
| DAC 연결방법 (0) | 2018.10.27 |
|---|---|
| 패스워드까지 그대로 로그인(계정) 옮기는법 (0) | 2018.10.27 |
| tempdb 경합 모니터링 (0) | 2018.10.27 |
| SQL SERVER 로그 정보(사용량, usage) (0) | 2018.10.27 |
| bufferpool(버퍼풀) 메모리 사용량 (0) | 2018.10.27 |
2018. 10. 27. 22:39
SQL SERVER
SELECT
-- using statement_start_offset and
-- statement_end_offset we get the query text
-- from inside the entire batch
DB_NAME(qt .dbid) dbname,
OBJECT_NAME(qt .objectid) [object_name],
SUBSTRING(qt .TEXT, ( qs.statement_start_offset / 2 ) + 1,
( ( CASE qs.statement_end_offset
WHEN - 1 THEN DATALENGTH(qt .TEXT)
ELSE qs .statement_end_offset
END - qs. statement_start_offset ) / 2 ) + 1) AS [Text] ,
qs.execution_count ,
qs.total_logical_reads ,
qs.last_logical_reads ,
qs.total_logical_writes ,
qs.last_logical_writes ,
qs.total_worker_time ,
qs.last_worker_time ,
-- converting microseconds to seconds
qs.total_elapsed_time / 1000000 total_elapsed_time_in_S ,
qs.last_elapsed_time / 1000000 last_elapsed_time_in_S ,
qs.last_execution_time ,
qp.query_plan
FROM sys .dm_exec_query_stats qs
-- Retrieve the query text
CROSS APPLY sys. dm_exec_sql_text(qs .sql_handle) qt
-- Retrieve the query plan
CROSS APPLY sys. dm_exec_query_plan(qs .plan_handle) qp
ORDER BY qs. total_worker_time DESC -- CPU time