[MSSQL] 통계 업데이트, 인덱스, LOCK 등 DBA 기초 쿼리문 모음

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) ******************************/

 

출처 : https://cong4u.tistory.com/226