MS-SQL 통계 업데이트, 인덱스, LOCK 등 DBA 기초 쿼리문 모음

제목 : MS-SQL 통계 업데이트, 인덱스, 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) ******************************/

내용 : 

예제 소스 파일 : 

쿼리문.sql

 출처 :