문제상황시 유용한 sql쿼리들

--get the connections

select st.text,

c.connect_time, c.net_transport, c.auth_scheme, c.num_reads, c.num_writes,

c.last_read, c.last_write, c.connection_id, c.parent_connection_id,

st.dbid, st.objectid 

from sys.dm_exec_connections c

cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) st

go

 

--get the sessions

select

s.login_time, s.host_name, s.program_name, s.login_name, s.status,

s.cpu_time, s.memory_usage, s.total_scheduled_time, s.total_elapsed_time,

s.last_request_start_time, s.last_request_end_time, s.reads, s.writes, s.logical_reads,

s.is_user_process, s.transaction_isolation_level, s.row_count, s.original_login_name,

s.database_id, s.authenticating_database_id, s.open_transaction_count

from sys.dm_exec_sessions s

join sys.dm_exec_connections c on s.session_id = c.session_id

cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) st

go

 

--get all requests executing in SQL Server

select st.text,

SUBSTRING(st.text, (r.statement_start_offset/2)+1,

        ((CASE r.statement_end_offset

          WHEN -1 THEN DATALENGTH(st.text)

         ELSE r.statement_end_offset

         END - r.statement_start_offset)/2) + 1) AS statement_text,

p.query_plan,

r.session_id, r.blocking_session_id, r.start_time, r.status, r.command, r.database_id,

r.wait_type, r.wait_time, r.last_wait_type, r.open_transaction_count, r.transaction_id,

r.percent_complete, r.estimated_completion_time, r.cpu_time, r.total_elapsed_time,

r.reads, r.writes, r.logical_reads, r.transaction_isolation_level, r.row_count, r.granted_query_memory,

r.query_hash, r.query_plan_hash

from sys.dm_exec_requests r

cross apply sys.dm_exec_sql_text(r.sql_handle) st

cross apply sys.dm_exec_query_plan(r.plan_handle) p

--where r.blocking_session_id > 0 --This filter can be used to see any blocking issues.

go

 

--get the expensive queries

select top 50 coalesce (db_name(st.dbid), db_name(convert (int, pa.value)), 'Empty') as DBName,

SUBSTRING(st.text, (qs.statement_start_offset/2)+1,

        ((CASE qs.statement_end_offset

          WHEN -1 THEN DATALENGTH(st.text)

         ELSE qs.statement_end_offset

         END - qs.statement_start_offset)/2) + 1) AS statement_text,

st.text as ProcedureTextOrBatchText,        

qs.plan_generation_num as PlanGenerationNumber,qs.execution_count as ExecutionCount,

(qs.total_worker_time/1000) as CPUTimeTotal,

((qs.total_worker_time/1000)/qs.execution_count) as CPUTimeAvg,

(qs.total_elapsed_time/1000) as DurationTimeTotal,

((qs.total_elapsed_time/1000)/qs.execution_count) as DurationTimeAvg,qs.total_physical_reads as PhysicalReadsTotal,

(qs.total_physical_reads/qs.execution_count) as PhysicalReadsAvg,qs.total_logical_reads as LogicalReadsTotal,

(qs.total_logical_reads/qs.execution_count) as LogicalReadsAvg,qs.last_execution_time as LastExecutionTime

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(sql_handle) st

cross apply sys.dm_exec_query_plan(plan_handle) qp

cross apply sys.dm_exec_plan_attributes(qs.plan_handle) pa

where attribute = 'dbid'

--order by CPUTimeTotal desc

--order by CPUTimeAvg desc

--order by DurationTimeTotal desc

--order by DurationTimeAvg desc

--order by PhysicalReadsTotal desc

--order by PhysicalReadsAvg desc

--order by LogicalReadsTotal descorder by LogicalReadsAvg desc

 

--get the bottleneck in SQL Server

select wait_type, wait_time_ms, waiting_tasks_count from sys.dm_os_wait_stats

WHERE wait_type NOT IN (

        N'BROKER_EVENTHANDLER',             N'BROKER_RECEIVE_WAITFOR',

        N'BROKER_TASK_STOP',                N'BROKER_TO_FLUSH',

        N'BROKER_TRANSMITTER',              N'CHECKPOINT_QUEUE',

        N'CHKPT',                           N'CLR_AUTO_EVENT',

        N'CLR_MANUAL_EVENT',                N'CLR_SEMAPHORE',

        N'DBMIRROR_DBM_EVENT',              N'DBMIRROR_EVENTS_QUEUE',

        N'DBMIRROR_WORKER_QUEUE',           N'DBMIRRORING_CMD',

        N'DIRTY_PAGE_POLL',                 N'DISPATCHER_QUEUE_SEMAPHORE',

        N'EXECSYNC',                        N'FSAGENT',

        N'FT_IFTS_SCHEDULER_IDLE_WAIT',     N'FT_IFTSHC_MUTEX',

        N'HADR_CLUSAPI_CALL',               N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',

        N'HADR_LOGCAPTURE_WAIT',            N'HADR_NOTIFICATION_DEQUEUE',

        N'HADR_TIMER_TASK',                 N'HADR_WORK_QUEUE',

        N'KSOURCE_WAKEUP',                  N'LAZYWRITER_SLEEP',

        N'LOGMGR_QUEUE',                    N'onDEMAND_TASK_QUEUE',

        N'PWAIT_ALL_COMPONENTS_INITIALIZED',

        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',

        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',

        N'REQUEST_FOR_DEADLOCK_SEARCH',     N'RESOURCE_QUEUE',

        N'SERVER_IDLE_CHECK',               N'SLEEP_BPOOL_FLUSH',

        N'SLEEP_DBSTARTUP',                 N'SLEEP_DCOMSTARTUP',

        N'SLEEP_MASTERDBREADY',             N'SLEEP_MASTERMDREADY',

        N'SLEEP_MASTERUPGRADED',            N'SLEEP_MSDBSTARTUP',

        N'SLEEP_SYSTEMTASK',                N'SLEEP_TASK',

        N'SLEEP_TEMPDBSTARTUP',             N'SNI_HTTP_ACCEPT',

        N'SP_SERVER_DIAGNOSTICS_SLEEP',     N'SQLTRACE_BUFFER_FLUSH',

        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',

        N'SQLTRACE_WAIT_ENTRIES',           N'WAIT_FOR_RESULTS',

        N'WAITFOR',                         N'WAITFOR_TASKSHUTDOWN',

        N'WAIT_XTP_HOST_WAIT',              N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',

        N'WAIT_XTP_CKPT_CLOSE',             N'XE_DISPATCHER_JOIN',

        N'XE_DISPATCHER_WAIT',              N'XE_TIMER_EVENT')

and waiting_tasks_count > 0

order by wait_time_ms desc

go

 

 

 

--최근 10분간 실행 된 쿼리 실행회수, Duration DESC

-- 플랜까지 같이 수집해서 처음 실행할땐 시간 오래걸린다.

-- 시스템에 따로 부하는 주지 않는다.

SELECT TOP 100

        Left(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), 200)

,       qs.execution_count

,       qs.total_worker_time  /qs.execution_count/1000 as avg_worker_time

,       qs.total_elapsed_time /qs.execution_count /1000 as avg_elapsed_time

,       qs.total_physical_reads /qs.execution_count as avg_physical_reads

,       qs.total_logical_reads /qs.execution_count as avg_logical_reads

,       qs.last_worker_time /1000 as last_worker_time

,       qs.max_worker_time /1000 as max_worker5_time

,       qs.last_elapsed_time /1000 as last_elapsed_time

,       qs.max_elapsed_time/1000 as max_elapsed_time

,       qs.last_physical_reads

,       qs.max_physical_reads

,       qs.last_logical_reads

,       qs.max_logical_reads

,       qp.query_plan

-- query stats가 실행된 쿼리들을 저장함.

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

WHERE qs.last_execution_time > dateadd(minute, -10, getdate())

ORDER BY qs.total_elapsed_time/qs.execution_count DESC 

 

 

--실시간 모니터링

--실행중인 쿼리 중 성능저하 쿼리 조회

select 

               r.session_id

               ,r.status

               ,r.percent_complete

               ,r.wait_type

               ,r.wait_resource

               ,r.blocking_session_id

               ,substring(qt.text,r.statement_start_offset/2,

                            (case when r.statement_end_offset = -1

                            then len(convert(nvarchar(max), qt.text)) * 2

                            else r.statement_end_offset end -                                                              r.statement_start_offset)/2)

               as query_text   --- statement of batch or proc executing right now

               ,qt.text

               ,qt.dbid   ,qt.objectid

               ,r.cpu_time

               ,r.total_elapsed_time

               ,r.reads

               ,r.writes

               ,r.logical_reads

               ,r.scheduler_id

               ,s.login_time

               ,s.host_name

               ,s.program_name

               ,s.client_interface_name

               ,s.login_name                     

from sys.dm_exec_requests r

inner join sys.dm_exec_sessions s on r.session_id = s.session_id

cross apply sys.dm_exec_sql_text(sql_handle) as qt

where r.session_id > 50 and r.status = 'running'

order by r.session_id

go

 

 

--성능저하 쿼리 조회

-- running 상태가 아니고 문제가 있거나 블락킹 걸려있는거 찾기

select

     req.session_id

 ,   req.status

 ,      req.blocking_session_id

 ,   req.wait_type

 ,   substring(sql_text.text,req.statement_start_offset/2

 , (case when req.statement_end_offset = -1

        then len(convert(nvarchar(max), sql_text.text)) * 2

     else req.statement_end_offset end - req.statement_start_offset)/2) as query_text --- statement of batch or proc executing right now

 ,   db_name(sql_text.dbid) as 'DB명'

 ,   cast(datediff(mi, ses.login_time, getdate()) as varchar)+ '분' as '실행시간'

 ,   object_name(sql_text.objectid) as 'SP명'

 ,   req.cpu_time

 ,   req.total_elapsed_time

 ,   req.reads

 ,   req.writes 

 ,   req.logical_reads 

 ,   req.scheduler_id

 from sys.dm_exec_requests as req with (nolock)

 inner join sys.dm_exec_sessions as ses with (nolock) on req.session_id = ses.session_id

 cross apply sys.dm_exec_sql_text(sql_handle) as sql_text

 where req.session_id > 50 and wait_type <> 'WAITFOR'

 order by req.scheduler_id, req.status, req.session_id

 

 

--OPEN_TRAN=1인데 SELECT가 BLOCKING하는 경우 KILL구문 포함

--select를 하는데 트랜잭션이 있어서 블락킹이 걸릴 때가 있다.

--sp reset connection을 날려서 킬해라.

SELECT

        open_tran

,       'KILL ' + CONVERT(VARCHAR(5), spid) AS killStr

,       'DBCC INPUTBUFFER(' + CONVERT(VARCHAR(5), spid) + ')' AS bufStr

,       spid

,       blocked

,       waittype

,       lastwaittype

,       hostname

,       cmd

 FROM master..sysprocesses WITH (NOLOCK)

WHERE open_tran = 1

  AND status = 'sleeping'

  AND cmd = 'awaiting command'

 

 

--LOCK을 많이 잡는 상위 5개 SPID확인

SELECT

        TOP 5 

        'KILL ' + CAST(req_spid AS VARCHAR(5)) AS killStr

,       'DBCC INPUTBUFFER(' + CAST(req_spid AS VARCHAR(5)) + ')'  AS inputBufferStr

,       COUNT(req_spid) AS LockCount

FROM master..syslockinfo WITH (NOLOCK)

GROUP BY req_spid

ORDER BY COUNT(req_spid) DESC

 

 

--파일별 IO 대기현황

SELECT

        vf.*

,       sys.filename

,       sys.name

  FROM ::fn_virtualfilestats(-1, -1) as vf

 INNER JOIN master..sysaltfiles as sys WITH (NOLOCK) ON vf.dbid = sys.dbid and vf.fileid = sys.fileid

WHERE vf.dbid > 2

ORDER BY vf.IoStallMs DESC

 

 

--CPU - What’s running in parallel?

-- 어떤 쿼리가 병렬로 수행되고 있는지

select

    req.session_id

,   req.request_id

,   max(isnull(exec_context_id, 0)) as number_of_workers

,   req.sql_handle

,   req.statement_start_offset

,   req.statement_end_offset

,   req.plan_handle

from sys.dm_exec_requests as req with (nolock)

 join sys.dm_os_tasks as ts with (nolock)  on req.session_id = ts.session_id

 join sys.dm_exec_sessions as ses on req.session_id = ses.session_id

where ses.is_user_process = 0x1

group by req.session_id, req.request_id, req.sql_handle, req.plan_handle,

         req.statement_start_offset, req.statement_end_offset

having max(isnull(exec_context_id, 0)) > 0

 

 

--Parallelism and CPU usage

--병렬로 실행중인 애들 중의 cpu사용량

select top 200

qs.sql_handle,

qs.statement_start_offset,

qs.statement_end_offset,

q.dbid,

object_name(q.objectid),

q.number,

q.encrypted,

q.text

from sys.dm_exec_query_stats qs

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

where qs.total_worker_time > qs.total_elapsed_time

 

--Recompilation Top 50

--플랜이 만들어지는 횟수가 1보다 큰 애들 확인

select top 50

    object_name(sql_text.objectid) as 'SP명'

, sql_handle

, plan_generation_num

, execution_count

, dbid

, objectid

from sys.dm_exec_query_stats as a with (nolock)

 Cross apply sys.dm_exec_sql_text(sql_handle) as sql_text

where plan_generation_num >1

order by plan_generation_num desc

 

--top cpu processes and batches

select top 50

        sum(qs.total_worker_time) as total_cpu_time

,       sum(qs.execution_count) as total_execution_count

,       count(*) as '#_statements'

,       qt.dbid

,       qt.objectid

,       qs.sql_handle

,       qt.text

  from sys.dm_exec_query_stats as qs

 cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

 group by qt.dbid, qt.objectid, qs.sql_handle, qt.text

 order by sum(qs.total_worker_time) desc, qs.sql_handle