[DB] SQL SERVER
[MSSQL] 통계 업데이트, 인덱스, LOCK 등 DBA 기초 쿼리문 모음
mewoni
2023. 6. 19. 13:31
반응형
USE [DB명]
/********************************** 인덱스(S) **********************************/
--TABLE 상세 정보 쿼리
SELECT a.NAME AS table_name,
b.NAME AS column_name,
c.NAME AS data_type,
c.length AS data_length
FROM sys.tables a
INNER JOIN sys.syscolumns b
ON a.object_id = b.id
INNER JOIN sys.systypes c
ON c.xtype = b.xtype
WHERE a.NAME = '테이블명'
ORDER BY table_name
--통계확인(업데이트날짜, 테이블명, 인덱스명)
SELECT Stats_date (o.id, i.indid) AS stats_updated,
o.NAME,
i.NAME
FROM dbo.sysobjects o
INNER JOIN dbo.sysindexes i
ON i.id = o.id
WHERE o.NAME = '테이블명'
--테이블의 모든 통계의 이름 반환(통계명, 해당 컬럼)
EXEC Sp_helpstats
'테이블명',
'ALL'
--인덱스명, 인덱스키 확인
EXEC Sp_helpindex 테이블명
--인덱스 단편화 정보 조회
DBCC showcontig (테이블명)
--인덱스 생성
CREATE INDEX 인덱스명
ON 테이블명(컬럼명)
--해당 인덱스명의 통계보기
DBCC show_statistics (테이블명, 인덱스명)
/*
Rows : 총 행수
Rows Sampled : 샘플 총 행수(즉, 통계정보를 수집하기 위해서 사용한 샘플링 행수)
8MBtye 이하는 샘플링을 하지 않고 전체 풀 스캔을 한다. 즉, 8MBtye 이하는 테이블은
Rows의 값과 Rows Sampled 의 값이 동일하다.
Steps : Max 200
Density : 밀도(평균밀도) - 밀도의 값은 1보다 클수 없다.
*/
--인덱스 조각모음. - table 인덱스 다시 잡음
DBCC dbreindex (테이블명)
or
dbcc indexdefrag (테이블명)
--테이블 통계 업데이트
UPDATE STATISTICS 테이블명
--DB 의 모든 인덱스를 재구축한다. 엄청난 시간이 소요될 수 있다.
DBCC dbreindexall('DB명')
--하나의 인덱스 통계만 업데이트
ALTER INDEX 인덱스명 ON 테이블명 REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
--모든 통계 업데이트
/*
EXEC Sp_msforeachtable
@command1="print '?' DBCC DBREINDEX ('?', '', 90)"
*/
--테이블에 대한 가장 최근 통계의 날짜 반환
SELECT NAME AS stats_name,
Stats_date(object_id, stats_id) AS statistics_update_date
FROM sys.stats
WHERE object_id = Object_id('patient_order');
SELECT NAME AS index_name,
Stats_date(object_id, index_id) AS statistics_update_date
FROM sys.indexes
WHERE object_id = Object_id('patient_order');
/********************************** 인덱스(E) **********************************/
/********************************** 기타(S) **********************************/
/* 커서를 임시테이블로 대체하여 구현하는 방법*/
DECLARE @tmptable TABLE
(
nid INT IDENTITY(1, 1) NOT NULL,
customer_id VARCHAR(4) NOT NULL
)
INSERT @tmptable
(customer_id)
SELECT customer_id
FROM patient_order --커서문에서 for select 에 해당
DECLARE @i INT,
@maxno INT,
@customer_id VARCHAR(4)
SELECT @i = 1,
@maxno = Max(nid)
FROM @tmptable
WHILE @i <= @maxno
BEGIN
SELECT @customer_id = customer_id
FROM @tmptable
WHERE nid = @i
-- fetch into 에 해당
PRINT @customer_id
SET @i = @i + 1
END
/* 프로시저 리컴파일 작업 */
SELECT Db_id('디비명') --디비명
--위 쿼리에서 나온 디비번호값을 아래에 대입
DBCC flushprocindb(12)
/********************************** 기타(E) **********************************/
/********************************** 전체 프로시저 보기(S) **********************************/
SELECT CONVERT(VARCHAR(10), Getdate(), 120) AS str_today -- 실행된날짜검색조건을위해-00-00형식을취함
,
Getdate() AS str_today -- 실행된시각
,
so.NAME -- 저장프로시저이름
,
so.type_desc -- object 타입
,
so.create_date -- 생성일
,
so.modify_date -- 수정일
,
ss.definition
FROM sys.objects AS so
INNER JOIN sys.sql_modules AS ss
ON so.object_id = ss.object_id
WHERE so.schema_id = 1
AND type = 'P'
/********************************** 전체 프로시저 보기(E) **********************************/
/**********************************lock 확인 ******************************/
SELECT session_id,
blocking_session_id,
wait_time,
wait_type,
last_wait_type,
wait_resource,
transaction_isolation_level,
lock_timeout
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
/*
?Blocking_session_id : 블록킹 세션의 SPID
?Wait_type : 대기의 종류
?Wait_time : 대기 시간(밀리초)
?Last_wait_type : 마지막 대기 타입
?Wait_resource : 요청을 기다리는 자원
?Transaction_isolation_level : 트랜잭션 격리 수준
?Lock_timeout : 잠금 제한 시간
*/
exec sp_lock
/*
S : 공유 잠금 Lock.
U : 업데이트 잠금 Lock.
X : 독점 잠금 Lock.**********
IS : 의도 공유 잠금 Lock.
IU : 의도 업데이트 잠금 Lock.
IX : 독점 의도 잠금 Lock.**********
BU : 대량 업데이트 잠금 Lock
*/
EXEC SP_WHO2
--개별ID 확인
EXEC SP_WHO 457
--ID의 쿼리문 확인
DBCC INPUTBUFFER(457)
--킬
kill 457
--락 타임아웃 조회
select @@lock_timeout;
--락 쿼리
SELECT s.session_id AS spid
,s.[status]
,s.login_name AS loginName
,s.[host_name] AS hostName
,r.blocking_session_id AS blkBy
,r.wait_time
,r.wait_type
,r.last_wait_type
,r.percent_complete
,DB_NAME(r.database_id) AS dbName
,r.command
,s.cpu_time AS cpuTime
,s.reads + s.writes AS diskIO
,s.last_request_end_time AS lastBatch
,s.[program_name] AS programName
,s.session_id
,r.request_id
,CASE
WHEN s.transaction_isolation_level = 0 THEN 'Unspecified'
WHEN s.transaction_isolation_level = 1 THEN 'ReadUncommitted'
WHEN s.transaction_isolation_level = 2 THEN 'ReadCommitted'
WHEN s.transaction_isolation_level = 3 THEN 'Repeatable'
WHEN s.transaction_isolation_level = 4 THEN 'Serializable'
WHEN s.transaction_isolation_level = 5 THEN 'Snapshot'
END AS transactionIsolationLevel
,OBJECT_NAME(t.objectid) AS objectName
,t.[text] AS lastSQLText
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
WHERE s.is_user_process = 1;
/**********************************lock 확인 ******************************/
/**********************************최근 시간걸리는 쿼리문(S) ******************************/
/* 최근 시간걸리는 쿼리문 */
SELECT top 10 st.text,
pl.query_plan,
qs.*
FROM sys.dm_exec_query_stats qs
CROSS apply sys.Dm_exec_sql_text(qs.sql_handle) AS st
CROSS apply sys.Dm_exec_query_plan(qs.plan_handle) AS pl;
/**********************************최근 시간걸리는 쿼리문(E) ******************************/
/**********************************암호화 여부 파악 쿼리문(S) ******************************/
SELECT o.NAME,
s.definition,
o.type_desc,
CASE
WHEN definition IS NULL THEN 'yes'
ELSE 'no'
END AS 'is_encrypted'
FROM sys.sql_modules s
INNER JOIN sys.objects o
ON s.object_id = o.object_id
WHERE type IN ( 'p', 'tr', 'FN', 'tf', 'v' )
/**********************************암호화 여부 파악 쿼리문(E) ******************************/
반응형