MS SQL에서 인덱스를 재구성하고 다시 작성하는 방법

SQL

인덱스는 MS SQL 데이터베이스 파일의 객체입니다. 이러한 스키마 객체는 데이터베이스의 목차처럼 작용합니다. 이들은 MS SQL 데이터베이스 테이블에서 데이터 검색 작업을 개선하는 데 사용됩니다.

그러나 시간이 지나면서, 테이블은 지속적인 INSERT 및 UPDATE 작업으로 인해 단편화될 수 있습니다. 또한, 다른 객체들과 마찬가지로 인덱스도 손상될 수 있습니다. 인덱스가 손상되면 다음과 같은 오류가 발생할 수 있습니다:

Plain Text

Table error: table 'Items' (ID 1954106002). Data row does not have a matching index row in the index 'IX_Advertise_BrandCopy_Price_Stock' (ID 69). Possible missing or invalid keys for the index row matching:

​

Msg 8955, Level 16, State 1, Line 1

​

Data row (1:11226494:9) identified by (Id = 11078215) with index values 'AdvertiseFlag = 1 and BrandCopyParentId = 0 and NSFPPrice = 137.50 and NSFPQtyInStock = 0 and Id = 11078215'.

​

Msg 8951, Level 16, State 1, Line 1

인덱스의 손상과 관련된 오류를 해결하거나 인덱스의 단편화 수준이 증가할 때, 인덱스를 재구성하거나 재조직할 수 있습니다. 이 기사에서는 MS SQL Server에서 인덱스를 재조직하고 재구성하는 방법에 대해 논의할 것입니다. 또한 손상된 SQL 데이터베이스 파일에서 인덱스 및 기타 객체를 신속하고 정확하게 복원할 수 있는 고급 MS SQL 데이터베이스 복구 도구를 언급할 것입니다.

인덱스의 단편화 비율 확인하기

인덱스를 재조직하기 전에 단편화 비율을 알아야 합니다. 아래 명령어를 사용하여 인덱스 단편화 비율을 확인할 수 있습니다:

SQL

SELECT 
    OBJECT_NAME(object_id) tableName12,
    index_id,
    index_type_desc,
    avg_fragmentation_in_percent,
    page_count
FROM 
    sys.dm_db_index_physical_stats (
        DB_ID('Adventureworks2019'), 
        OBJECT_ID('[Person].[Person]'), 
        NULL, NULL, 'DETAILED'
    );
​

SQL Server에서 인덱스 재조직하기

인덱스의 단편화 수준이 10과 30 사이일 경우, 인덱스를 재조직하는 것이 권장됩니다. 인덱스를 재조직하기 위해 T-SQL에서 다음 코드를 사용할 수 있습니다:

SQL

ALTER INDEX [PK_Person_BusinessEntityID] ON [Person].[Person]
REORGANIZE;
​

대안으로, SQL Server Management Studio (SSMS)를 사용하여 인덱스를 재구성할 수 있습니다. 아래 단계를 따르세요:

SSMS에서 SQL Server 인스턴스에 연결합니다.
객체 탐색기에서 데이터베이스를 확장합니다.
테이블 폴더를 확장한 다음 인덱스 폴더를 확장합니다.
재구성이 필요한 인덱스를 마우스 오른쪽 버튼으로 클릭하고 ‘재구성’을 클릭합니다.

MS SQL에서 인덱스 재작성

조각화가 더 높다면 인덱스를 재작성할 수 있습니다. 인덱스를 재작성하기 위해 DBCC DBREINDEX 명령을 사용할 수 있습니다. 이 명령을 실행하는 방법은 다음과 같습니다:

SQL

DBCC DBREINDEX
(
    table_name
    [ , index_name [ , fillfactor ] ]
)
    [ WITH NO_INFOMSGS ]
​

이 명령은 공간 인덱스 및 메모리 최적화 열 저장소 인덱스를 지원하지 않습니다.

대안으로, ALTER INDEX 명령을 사용할 수 있습니다:

SQL

ALTER INDEX [PK_Person_BusinessEntityID] ON [Person].[Person]
REBUILD;
​

SSMS의 그래픽 사용자 인터페이스를 사용하여 인덱스를 재작성할 수도 있습니다. 단계는 다음과 같습니다:

SSMS의 객체 탐색기에서 인덱스를 재작성해야 하는 테이블이 포함된 데이터베이스를 확장합니다.
테이블 폴더를 확장한 다음 인덱스를 재작성해야 하는 테이블을 확장합니다.
인덱스 폴더를 확장하고, 재작성할 인덱스를 마우스 오른쪽 버튼으로 클릭한 후 ‘재작성’을 선택합니다..

SQL Server 데이터베이스 복구

인덱스 재구성 또는 재구축이 작동하지 않는 경우, DBCC CHECKDB 명령을 REPAIR_ALLOW_DATA_LOSS 옵션과 함께 사용하여 데이터베이스를 수리할 수 있습니다. 이 명령어를 사용하는 방법은 다음과 같습니다:

먼저, 아래 명령을 사용하여 데이터베이스 모드를 SINGLE_USER로 변경하십시오:

SQL

ALTER DATABASE BusinessEntity SET SINGLE_USER

다음으로, 아래와 같이 DBCC CHECKDB 명령을 실행하여 데이터베이스를 수리하십시오:

SQL

DBCC CHECKDB (N ’BusinessEntity’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; 
GO
​

수리 후, 아래 명령을 실행하여 데이터베이스 모드를 MULTI_USER로 설정하십시오:

SQL

ALTER DATABASE BusinessEntity SET MULTI_USER

위의 DBCC CHECKDB 명령을 사용하면 인덱스 손상과 관련된 모든 오류를 해결할 수 있습니다. SQL 데이터베이스에서 클러스터형 및 비클러스터형 인덱스 모두를 수리할 수 있습니다. 그러나 완전한 데이터 복구를 보장하지는 않으며 데이터 손실이 발생할 수 있습니다. 데이터 손실을 방지하려면 전문 MS SQL 수리 도구를 사용할 수 있습니다. 이러한 도구들은 클러스터형 및 비클러스터형 인덱스, 저장 프로시저, 트리거 등을 포함한 모든 객체를 복구하기 위해 고급 알고리즘으로 설계되어 있습니다.

요약

인덱스 조각화를 감소시키려면 인덱스를 재구성하거나 재구축할 수 있습니다. 이 문서에서는 인덱스에서 조각화 비율을 확인하는 방법과 인덱스를 재구축하는 방법을 설명했습니다.

Source:
https://dzone.com/articles/reorganize-and-rebuild-indexes-in-ms-sql