RDS for SQL Server 인스턴스에서 인덱스를 재구축하기 위한 유지 관리 작업을 생성하려면 어떻게 해야 하나요?

간단한 설명

인덱스 조각화는 SQL Server 데이터베이스를 즉시 재구축하지 않을 경우 성능 문제를 일으킬 수 있는 심각한 문제입니다. 조각화 수준을 모니터링하고 정기적으로 인텍스를 재작성 및 재구성하는 것이 가장 좋습니다. 인덱스 조각화는 데이터 페이지의 격차 및 논리적 조각화로 발생합니다.

참고: 인덱스를 재구축하면 인덱스가 삭제되었다가 다시 생성됩니다. 인덱스 유형과 데이터베이스 엔진 버전에 따라 재구축 작업을 오프라인 또는 온라인으로 수행할 수 있습니다. 재구성을 사용하면 인덱스를 삭제한 다음 다시 생성하는 대신 페이지의 정보가 재구성됩니다.

RDS for SQL Server에는 인덱스를 재구축 또는 재구성하기 위한 계획 및 태스크를 자동으로 생성하는 유지 관리 계획이 없습니다. 하지만 다음 방법을 사용하여 인덱스를 재구축할 수 있습니다.

• 방법 1: SQL Server 에이전트 작업을 생성하여 색인을 재구축하고 통계 자료를 업데이트합니다.
• 방법 2: 스크립트를 사용하여 수동으로 재구축 작업을 생성하고 필요에 따라 스케줄링합니다.

해결 방법

방법 1: SQL Server 에이전트 작업을 생성하여 색인을 재구축하고 통계 자료를 업데이트합니다

1.    Microsoft SQL Server Management Studio 클라이언트를 시작한 다음 로그인합니다.

2.    오른쪽 패널에서 SQL Server 에이전트를 마우스 오른쪽 단추로 클릭합니다. 그런 다음, 새로 만들기, 작업을 선택하여 SQL 에이전트 작업을 생성합니다.

3.    에이전트 작업의 이름과 설명을 입력한 다음, 확인을 선택합니다.

예시:

• 이름: Indexrebuild_job
• 설명: 인덱스 재구축을 위한 SQL 서버 에이전트 작업용 RDS.

4.    단계를 선택한 다음, 새로 만들기를 선택하여 실행 단계를 추가합니다. 그러면 새 창이 나타납니다.

5.    단계 이름을 입력합니다.

6.    데이터베이스를 선택한 다음, 주기적으로 실행할 명령을 추가합니다.

다음은 인덱스 재구축 SQL 명령 예시입니다. 이 예제 명령을 사용하여, 지정된 DB에서 조각화가 30%를 초과하는 모든 테이블의 조각화된 인덱스를 재구축할 수 있습니다. 첫 줄의 [DBNAME] 값을 사용자 데이터베이스의 올바른 이름으로 변경합니다. 모든 데이터베이스에 대해 같은 SQL 명령을 실행하는 경우, 명령을 그에 맞춰 수정하거나 각 데이터베이스에 대해 별도의 작업을 생성하세요.

Use [DBNAME]
SET NOCOUNT ON
DECLARE @Objectid INT, @Indexid INT,@schemaname VARCHAR(100),@tablename VARCHAR(300),@ixname VARCHAR(500),@avg_fragment float,@command VARCHAR(4000)
DECLARE AWS_Cusrsor CURSOR FOR
SELECT A.object_id,A.index_id,QUOTENAME(SS.NAME) AS schemaname,QUOTENAME(OBJECT_NAME(B.object_id,B.database_id))as tablename ,QUOTENAME(A.name) AS ixname,B.avg_fragmentation_in_percent AS avg_fragment FROM sys.indexes A inner join sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') AS B
ON A.object_id=B.object_id and A.index_id=B.index_id
INNER JOIN SYS.OBJECTS OS ON A.object_id=OS.object_id
INNER JOIN sys.schemas SS ON OS.schema_id=SS.schema_id
WHERE B.avg_fragmentation_in_percent>30  AND A.index_id>0 AND A.IS_DISABLED<>1
ORDER BY tablename,ixname
OPEN AWS_Cusrsor
FETCH NEXT FROM AWS_Cusrsor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fragment
WHILE @@FETCH_STATUS=0
BEGIN
IF @avg_fragment>=30.0
BEGIN
SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REBUILD '+N' WITH (ONLINE = ON)';
--Can add following line for index reorganization. Else remove following line.
SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REORGANIZE';
END
--PRINT @command
EXEC(@command)
FETCH NEXT FROM AWS_Cusrsor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fragment
END
CLOSE AWS_Cusrsor
DEALLOCATE AWS_Cusrsor

7.    확인을 선택합니다.

8.    일정을 선택한 다음, 새로 만들기를 선택하여 색인 재구축 작업 실행 시간에 대한 일정을 추가합니다.

9.    일정 이름, 일정 유형및 유사한 필드를 입력한 다음확인을 선택합니다.

10.    방금 생성한 작업을 확인하고 마우스 오른쪽 버튼으로 클릭합니다. 그런 다음 단계에서 작업 시작을 선택해 작업을 수동으로 실행하여 작업이 제대로 실행될 수 있는지 확인합니다.

참고: 프로덕션 RDS 데이터베이스에 배포하기 전에 개발 RDS 데이터베이스에서 이 예시에 제공된 스크립트를 테스트하세요. 인덱스 재구축 시간은 인덱스 크기 및 수에 따라 크게 달라집니다.

옵티마이저는 최적의 실행 계획을 생성하기 위해 테이블 열의 키값(통계) 분포에 대한 최신 정보를 가지고 있어야 합니다. 모든 테이블의 통계를 정기적으로 업데이트하는 것이 모범 사례입니다. 색인을 재구축하는 날짜에는 통계를 업데이트하지 마세요.

통계 업데이트는 한 번에 한 테이블에서만 작동한다는 점을 기억하세요. 데이터베이스 수준 명령 sp_updatestats(Microsoft 웹 사이트)는 Amazon RDS에서 사용할 수 없습니다. 통계 업데이트를 사용하여 데이터베이스의 모든 객체에 대한 통계를 업데이트하는 커서를 작성합니다. 또는, sp_updatestats 주변에 래퍼를 구축하고 일정을 지정합니다.

sp_updatestats 주변에 래퍼를 사용하려면 다음을 수행합니다.

1.    명령을 실행하여 저장 프로시저를 생성합니다.

create procedure myRDS_updatestats
with execute as ‘dbo’
as
exec sp_updatestats
go

2.    새 프로시저의 사용자에게 실행 권한을 부여합니다.

grant execute on myRDS_updatestats to user

3.    앞에서 설명한 방법 1의 단계에 따라 통계 업데이트 작업을 예약합니다.

방법 2: 스크립트를 사용하여 수동으로 재구축 작업을 생성하고 필요에 따라 예약

수동으로 스크립트나 프로시저를 생성해 조각화된 인덱스를 확인하고 일정에 따라 해당 인덱스에 대해 인덱스 재구축을 실행할 수 있습니다. 스크립트를 사용하여 자체 코드를 생성하고 수동 유지 관리 작업을 구성할 수 있습니다.

GitHub의 aws-rds-indexmaintenance-job-example SQL 스크립트를 사용할 수도 있습니다. 이러한 스크립트는 조각화 수준에 따라 매주 인덱스를 재구축 및 재구성합니다. 스크립트는 데이터베이스(IndexStats)와 객체(테이블)를 생성하여 인스턴스의 모든 데이터베이스에 관한 정보를 저장합니다. 이 정보에는 데이터베이스의 테이블, 인덱스 및 조각화 백분율이 포함됩니다.

aws-rds-indexmaintenance-job-example에는 CreateDatabaseAndObjects.sql과 CreateWeeklyMaintenanceJob.sql이라는 두 개의 스크립트가 포함되어 있습니다.

CreateDatabaseAndObjects.sql스크립트는 다음과 같은 작업을 수행합니다.

• ServerDatabases라는 이름의 테이블을 생성합니다. 이 테이블은 현재 인스턴스의 데이터베이스를 기록합니다. 시스템 데이터베이스(마스터, 모델, TempDB 및 msdb)는 제외됩니다. SSIS 및 SSRS(rdsadmin_ReportServer, rdsadmin_ReportServerTempDB)와 같은 SQL Server 구성 요소에 의해 생성된 데이터베이스도 제외됩니다.
• ServerTables라는 이름의 테이블을 생성합니다. 이 테이블은 ServerDatabases 테이블의 모든 데이터베이스에 대한 테이블을 수집합니다.
• Messages라는 이름의 테이블을 생성합니다. 이 테이블에는 인덱스에서 처리한 메시지(REBUILD(재구축) 또는 REORGANIZATION(재구성))가 포함됩니다. 필요한 경우 메시지를 복사하여 수동으로 실행할 수 있습니다.

CreateWeeklyMaintenanceJob.sql 스크립트는 다음과 같은 저장 프로시저를 생성합니다.

• sp_PopulateDatabases: 이 프로시저는 인스턴스의 모든 데이터베이스를 검토하여 이를ServerDatabases라는 테이블에 기록합니다. SSAS 및 SSRS와 같은 SQL Server 구성 요소에 의해 생성된 시스템 데이터베이스 또는 데이터베이스는 포함되지 않습니다. SSIS용 SSIDB는 포함됩니다.
• sp_PopulateTables: 이 프로시저는 각 데이터베이스를 검토하여 해당 테이블을 ServerTables에 기록합니다. 테이블을 기록한 후 테이블이 속한 스키마를 확인한 다음 포함된 인덱스를 찾습니다. 저장 프로시저는 인덱스를 검토하여 조각화가 가장 많이 진행된 인덱스 정보를 찾아 기록합니다.
• Sp_ReindexTables: 이 프로시저는 ServerTable에서 정보를 읽고 다음 규칙에 따라 재구축 또는 조각 모음 프로세스를 시작합니다.
  조각화 09% = NOTHING(없음)
  조각화 1030% = REORGANIZE(재구성)
  조각화 31~100% = REBUILD(재구축)

GitHub의 aws-rds-indexmaintenance-job-example 스크립트를 사용하려면 먼저 CreateDatabaseAndObjects.sql을 복사하여 실행합니다. 그런 다음, CreateWeeklyMaintenanceJob.sql 스크립트를 실행합니다.

참고: 관리형 서비스 제한으로 인해 SQL Server 에이전트 작업은 현재 로그인한 계정으로만 생성할 수 있습니다. 다른 계정은 작업 소유자로 허용되지 않습니다.