Sql Server System 쿼리
--시스템 테이블을 이용한 각 테이블의 COUNT정보
select o.name as TABLE_NAME, i.rows as ROW_COUNT_ALL
from sysindexes i, sysobjects o
where o.xtype = 'U'
and o.name <> 'sysdiagrams'
and o.id = i.id
order by o.name
go
--특정 저장 프로시져의 내용정보
select o.name,c.text from syscomments c , sysobjects o
where c.id = o.id
and o.name = 'SP_NAME'
order by c.id,c.colid
go
--뷰를 작성한 SELECT문장 정보
select o.name,c.text from syscomments c , sysobjects o
where c.id = o.id
and o.name = 'VIEW_NAME
order by c.id,c.colid
go
--특정 테이블의 컬럼 정보
select o.name,c.name
from syscolumns c, sysobjects o
where c.id=o.id
and o.name = TABLE_NAME
go
--특정 테이블의 인덱스 정보
select o.name,col.name
from sysindexkeys ik, sysobjects o, syscolumns c,
(select i.id,i.indid,i.status
from sysindexes i
where (i.status & 2048)<>0) sub_i
where ik.id=o.id
and ik.id=c.id
and ik.colid=c.colid
and ik.id=sub_i.id
and ik.indid=sub_i.indid
and o.name='TABLE_NAME'
order by ik.id,ik.indid,ik.keyno
go
--현재 서버상에 존재하는 프로세서 정보
select
spid,kpid,lastwaittype,status,hostname,program_name
from master..sysprocesses
go
--특정일 이후에 작성된 오브젝트 객체만을 알고 싶을때
select o.name table_name, substring(v.name + x.name,1,16) type , o.crdate create_date
,substring(user_name(uid),1,8) owner
from sysobjects o, master.dbo.spt_values v, master.dbo.spt_values x
where o.sysstat & 0xf = v.number
and v.type = 'O'
and x.type = 'R'
and o.userstat & -32768 = x.number
and o.crdate >= 'DATE_BASE'
order by 4,2,3,1
go
--현재 트랜잭션 락 현상을 일으키는 세션정보와 대상 오브젝트 정보
select convert (smallint, req_spid) spid,
rsc_dbid dbid,rsc_objid objId,o.name objname,rsc_indid IndId,
substring (v.name, 1, 4) type,substring (rsc_text, 1, 16) resource,
substring (u.name, 1, 8) mode,substring (x.name, 1, 5) status
from master.dbo.syslockinfo,master.dbo.sysobjects o,
master.dbo.spt_values v,master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and master.dbo.syslockinfo.rsc_objid *= o.id and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number and u.type = 'L'
order by spid
go
--테이블의 인덱스를 강제로 타게 만들기
select * from TABLE_NAME tablename WITH(INDEX(PK_NAME))
where tablename.empno = 1
go
--서버상의 쿼리 부하 확인
select p.status, p.program_name, p.hostname
, p.spid, p.blocked, p.kpid, p.cpu, p.physical_io, p.waittype, p.waittime, p.lastwaittype, p.waitresource, p.dbid
, p.uid, p.memusage, p.login_time, p.last_batch
, p.ecid, p.open_tran, p.sid, p.hostprocess
, p.cmd, p.nt_domain, p.nt_username, p.net_address, p.net_library, p.loginame
, p.context_info, p.sql_handle, p.stmt_start, p.stmt_end
FROM master..sysprocesses p
where (status like 'run%' or waittime > 0 OR blocked <> 0 OR open_tran <> 0
OR EXISTS(SELECT * FROM master..sysprocesses p1 where p.spid = p1.blocked and p1.spid <> p1.blocked)
)
--AND spid > 50
--AND spid <> @@spid
AND LTRIM(RTRIM(program_name)) = 'Microsoft JDBC Driver for SQL Server'
ORDER BY CASE WHEN status like 'run%' THEN 0 ELSE 1 END
, waittime DESC, open_tran desc
SELECT
[er].[session_id],
[es].[program_name],
[est].text,
[er].[database_id],
[eqp].[query_plan],
[er].[cpu_time]
FROM sys.dm_exec_requests [er]
INNER JOIN sys.dm_exec_sessions [es] ON
[es].[session_id] = [er].[session_id]
OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]
WHERE
[es].[is_user_process] = 1
AND [er].[last_Wait_type] = N'SOS_SCHEDULER_YIELD'
ORDER BY
[er].[session_id];
GO
--쿼리 실행 시간 확인
SELECT 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),
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,
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
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.total_worker_time DESC -- CPU time