DBA 업무시 필요한 쿼리

DBA를 하면서 점검 시 필요한 스크립트를 정리해봤습니다.

제가 정리한것과 퍼온글이 있으니 틀린 부분이 있을 수 있으니, 참고만 부탁드립니다.

틀린 부분은 메일로 문의 주시면 수정 하겠습니다 ^^

내용은 다음과 같습니다.

1. 인덱스 조각화 확인 및 리빌딩

2. 통계 확인 및 업데이트

3. CHECKDB 관련 체크 및 복구 

USE [데이터베이스명] GO   /* 1. 인덱스 인덱스의 조각화 정도를 확인하고, 조각화가 많이 일어났으면 해당 인덱스 또는 해당 DB의 모든 인덱스를 리빌딩 해줘야 한다. */ /* ===================================================================================================== 인덱스 조각화 확인 ======================================================================================================*/ DBCC SHOWCONTIG ('테이블 명')     /* ===================================================================================================== 인덱스 조각화 확인( dm_db_index_physical_stats 를 이용한 확인) https://msdn.microsoft.com/ko-kr/library/ms188917.aspx ======================================================================================================*/ SELECT database_id as '데이터베이스 ID',B.name AS '테이블 명',C.NAME AS '인덱스 명', A.index_type_desc AS '인덱스 타입',avg_fragmentation_in_percent AS '인덱스 논리 조각화 정도(%)',fragment_count AS '리프 수준 조각화 수' FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(''), NULL, NULL, NULL) AS A INNER JOIN SYS.tables AS B ON A.object_id = B.object_id INNER JOIN SYS.indexes AS C ON A.index_id = C.index_id AND A.Object_id = C.Object_id AND A.index_id = C.index_id ORDER BY avg_fragmentation_in_percent DESC       /* ===================================================================================================== 인덱스 다시 리빌드(개별) https://msdn.microsoft.com/ko-kr/library/ms189858.aspx ======================================================================================================*/ ALTER INDEX ALL ON [테이블명] REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90) GO     /* ===================================================================================================== 모든 인덱스 다시 리빌드 http://mdj1234.tistory.com/11 ======================================================================================================*/ DECLARE @i int, @sql varchar(1000) DECLARE @tablename varchar(1000),@ownerName  varchar(1000)   SET @i = 1   DECLARE DB_Cursor CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME   OPEN DB_Cursor   FETCH NEXT FROM DB_Cursor INTO @ownerName, @tablename WHILE @@FETCH_STATUS = 0 BEGIN   SET @sql = 'ALTER INDEX ALL ON ' + @ownerName + '.' + @tablename + ' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90) ' EXEC (@sql)   PRINT CONVERT(VARCHAR, @i) + '__' + @ownerName + '.' + @tablename + '............ OK' SET @i = @i + 1   FETCH NEXT FROM DB_Cursor INTO @ownerName, @tablename   END   CLOSE DB_Cursor DEALLOCATE DB_Cursor     /* 통계정보 업데이트 통계정보가 업데이트 되지 않으면 옵티마이저가 잘못된 통계 정보를 참조하여, 성능에 영향을 미칠수 있으므로, 통계를 주기적으로 업데이트 해줘야 한다.   * 통계 업데이트는 플랜 생성 시 영향이 있을 수 있으므로, 필히 점검떄 진행해야 한다. */ /* ===================================================================================================== 통계 확인 https://msdn.microsoft.com/ko-kr/library/ms174384.aspx ======================================================================================================*/ DBCC SHOW_STATISTICS ([테이블명],[인덱스 명])       /* ===================================================================================================== 데이터베이스의 모든 통계 업데이트 ======================================================================================================*/ SP_UPDATESTATS       /* ===================================================================================================== 데이터베이스의 개별 통계 업데이트 ======================================================================================================*/ UPDATE STATISTICS [테이블 이름]           /* 데이터베이스 무결성 체크 데이터베이스가 문제가 없는지 주기적으로 체크하여, 문제가 CHECK DB를 통해서 복구할 수 있다. */ /* ===================================================================================================== 데이터베이스 무결성 체크 https://msdn.microsoft.com/ko-kr/library/ms176064.aspx ======================================================================================================*/ DBCC CHECKDB('데이터베이스 명')       /* ===================================================================================================== 데이터베이스 복구 * 복구 옵션              - REPAIR_FAST : 인덱스 재생성 없이도 수정 가능한 오류 복구              - REPAIR_REBUILD : REPAIR_FAST의 작업 뿐만 아니라 인덱스 재생성 작업을 통해 복구              - REPAIR_ALLOW_DATA_LOSS : 오류를 복구하지만 데이터 손실 가능성이 있음   옵션을 위부터 가장 작은 오류로 분류하며, 아래로 갈수록 크리티컬한 오류복구 https://msdn.microsoft.com/ko-kr/library/ms176064.aspx ======================================================================================================*/ -- 단일 사용자모드로 변경 ALTER DATABASE [데이터베이스 명] SET SINGLE_USER WITH ROLLBACK IMMEDIATE   -- 복구시도 DBCC CHECKDB('데이터베이스 명',REPAIR_FAST)   -- 멀티 사용자모드로 변경 ALTER DATABASE [데이터베이스 명] SET MULTI_USER