전문 인덱스의 성능 향상하기

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

이 글에서는 전체 텍스트 색인과 쿼리의 성능 저하의 일반적인 원인과 이를 완화하는 방법을 다룹니다.

성능 문제의 일반적인 원인

이 섹션에서는 전체 텍스트 색인을 사용할 때 흔히 발생하는 성능 문제의 원인을 설명합니다.

하드웨어 리소스 문제

메모리, 디스크 속도, CPU 속도, 기계 아키텍처와 같은 하드웨어 자원은 전체 텍스트 인덱싱과 전체 텍스트 쿼리의 성능에 영향을 미칩니다.

하드웨어 자원 제한은 전체 텍스트 인덱싱 성능을 저하시킵니다.

• CPU. 필터 데몬 호스트 프로세스(fdhost.exe)나 SQL Server 프로세스(sqlservr.exe)의 CPU 사용량이 거의 100%에 가깝다면, CPU가 병목 현상입니다.

• 메모리. 물리적 기억 부족은 병목 현상을 초래할 수 있습니다.

• 디스크. 평균 디스크 대기열 길이가 디스크 헤드 수의 두 배를 초과하면 디스크에 병목 현상이 있는 것입니다. 기본 해결 방법은 SQL Server 데이터베이스 파일 및 로그와 별개의 전체 텍스트 카탈로그를 만드는 것입니다. 로그, 데이터베이스 파일 및 전체 텍스트 카탈로그를 서로 다른 디스크에 저장합니다. 또한 더 빠른 디스크를 설치하고 RAID를 사용하면 인덱싱 성능을 향상시키는 데 도움이 될 수 있습니다.

전체 텍스트 일괄 처리 문제

시스템에 하드웨어 병목 현상이 없다면, 전체 텍스트 검색의 인덱싱 성능은 주로 다음 요인들에 달려 있습니다:

• 데이터베이스 엔진이 전체 텍스트 배치를 생성하는 데 걸리는 시간.

• 필터 데몬이 그 배치들을 얼마나 빨리 소비하는지.

전체 텍스트 인덱스 작성 문제

• 인구 유형. 전체 인구와 달리, 점진적, 수동, 자동 변경 추적 인구는 더 빠른 속도를 위해 하드웨어 자원을 극대화하도록 설계되지 않습니다. 따라서 이 글의 튜닝 제안은 증분, 수동, 자동 변경 추적 집단을 사용할 때 전체 텍스트 색인의 성능을 향상시키지 못할 수 있습니다.

• 마스터 병합. 채우기가 완료되면 최종 병합 프로세스가 인덱스 조각들을 하나의 마스터 전문 인덱스로 병합합니다. 이 과정은 여러 인덱스 조각 대신 마스터 인덱스만 쿼리하면 되므로 쿼리 성능이 향상됩니다. 더 나은 점수 통계가 관련성 순위에 활용될 수 있습니다. 하지만 인덱스 조각을 병합할 때 많은 양의 데이터를 쓰고 읽어야 하므로 마스터 병합은 입출력 집약적일 수 있습니다. 하지만 들어오는 쿼리를 차단하지는 않습니다.

  대량의 데이터를 마스터 병합하면 장기 트랜잭션이 생성되어 체크포인트 중 트랜잭션 로그의 절단을 지연시킬 수 있습니다. 이 경우 전체 복구 모델에서는 트랜잭션 로그가 크게 증가할 수 있습니다. 전체 복구 모델이 사용되는 데이터베이스에서 큰 전체 텍스트 인덱스를 다시 구성하기 전에 장기 실행 트랜잭션에 대비하여 트랜잭션 로그에 충분한 공간을 확보하는 것이 가장 좋은 방법입니다. 자세한 내용은 트랜잭션 로그 파일의 크기 관리를 참조 하세요.

전체 텍스트 인덱스 성능 향상

전체 텍스트 인덱스의 성능을 극대화하려면 다음 모범 사례를 구현하세요.

• 모든 CPU 코어를 최대한 활용하려면 시스템 내 코어 수를 변경 max full-text crawl range 하세요. 자세한 내용은 서버 구성: 최대 전체 텍스트 크롤 범위를 참조하세요.

• 기본 테이블에 클러스터형 인덱스가 있는지 확인합니다. 클러스터형 인덱스의 첫 번째 열에 정수 데이터 형식을 사용합니다. 클러스터형 인덱스의 첫 번째 열에서 GUID 사용을 피하세요. 클러스터형 인덱스에서 다중 범위 채우기는 가장 높은 채우기 속도를 생성할 수 있습니다. 전체 텍스트 키 역할을 하는 열에 정수 데이터 타입을 사용하세요.

• 문을 사용하여 기본 테이블의 통계를 업데이트합니다 UPDATE STATISTICS . 더 중요한 것은 클러스터형 인덱스 또는 전체 텍스트 키의 통계를 전체 데이터를 대상으로 업데이트하는 것입니다. 이 동작은 다중 범위 집단이 테이블 위에 좋은 분할을 생성하는 데 도움을 줍니다.

• 대규모 멀티코어 컴퓨터에서 전체 채우기를 수행하기 전에, max server memory 프로세스와 운영 체제에서 사용할 수 있도록 충분한 메모리를 남기기 위해 fdhost.exe 값을 설정하여 버퍼 풀의 크기를 일시적으로 제한하십시오. 자세한 내용은 이 문서의 뒷부분에 있는 필터 데몬 호스트 프로세스의 메모리 요구 사항 추정(fdhost.exe)을 참조하세요.

• 타임스탬프 열을 기반으로 증분 채우기를 사용하는 경우 타임스탬프 열에 보조 인덱스 빌드를 작성하여 증분 채우기의 성능을 향상시킵니다.

전체 인구의 성능 문제 해결

전체 인구의 성능 문제를 해결하려면 다음 섹션을 참고하세요.

전체 텍스트 크롤링 로그 검토

성능 문제를 진단하는 데 도움이 되려면 전체 텍스트 크롤 로그를 검토하세요.

탐색 중에 오류가 발생하면 전체 텍스트 검색 탐색 로깅 기능은 일반 텍스트 파일인 탐색 로그를 만들고 유지 관리합니다. 각 크롤링 로그는 특정 전체 텍스트 카탈로그에 해당합니다. 기본적으로 지정된 인스턴스(이 예제에서는 기본 인스턴스)에 대한 크롤링 로그는 %ProgramFiles%\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG 폴더에 있습니다.

크롤링 로그 파일은 다음 명명 체계를 따릅니다.

SQLFT<DatabaseID><FullTextCatalogID>.[<n>]

크롤링 로그 파일 이름의 변수 부분은 다음과 같습니다.

• <DatabaseID>: 데이터베이스의 ID로, 앞에 0이 있는 다섯 자리 숫자입니다.

• <FullTextCatalogID>: 전체 텍스트 카탈로그 ID는 5자리 숫자에 앞에 0이 붙은 번호입니다.

• <n>: 동일한 전체 텍스트 카탈로그의 하나 이상의 크롤 로그가 존재함을 나타내는 정수입니다.

예를 들어, SQLFT0000500008.2는 데이터베이스 ID가 5이고 전체 텍스트 카탈로그 ID가 8인 데이터베이스의 크롤링 로그 파일입니다. 파일 이름의 끝에 있는 2는 이 데이터베이스/카탈로그 쌍에 대해 크롤링 로그 파일이 두 개 있음을 나타냅니다.

실제 메모리 사용량 확인

전체 텍스트 채우기 작업 중에는 fdhost.exe 또는 sqlservr.exe 프로세스의 메모리가 부족해지거나, 심지어 메모리를 완전히 소진할 수도 있습니다.

• 전체 텍스트 크롤 로그에 fdhost.exe가 자주 다시 시작되거나 오류 코드 8007008을 반환하는 것으로 표시되면, 이는 이러한 프로세스 중 하나의 메모리가 부족하다는 의미입니다.

• fdhost.exe에서 덤프가 생성되는 경우, 특히 대규모 멀티코어 시스템에서는 메모리가 부족할 수 있습니다.

• 전체 텍스트 크롤에 사용되는 메모리 버퍼에 대한 정보는 sys.dm_fts_memory_buffers를 참조하세요.

메모리 부족 또는 메모리 초과 문제의 가능한 원인은 다음과 같습니다:

• 메모리 부족. 만약 전체 인구 집단 동안 사용 가능한 물리적 메모리 양이 0이라면, 데이터베이스 엔진 버퍼 풀이 시스템 내 대부분의 물리적 메모리를 소모하고 있을 수 있습니다.

  sqlservr.exe 프로세스는 버퍼 풀에 가능한 메모리를 구성된 최대 서버 메모리까지 모두 사용하려고 합니다. max server memory 할당이 너무 크면 fdhost.exe 프로세스에서 메모리 부족 상태와 공유 메모리 할당 실패가 발생할 수 있습니다.

  이 문제를 해결하기 위해 데이터베이스 엔진 버퍼 풀의 값을 적절히 설정 max server memory 하세요. 자세한 내용은 이 문서의 뒷부분에 있는 필터 데몬 호스트 프로세스의 메모리 요구 사항 추정(fdhost.exe)을 참조하세요. 전체 텍스트 색인에 사용되는 배치 크기를 줄이는 것도 도움이 될 수 있습니다.

• 메모리 경합. 멀티코어 시스템에서 전체 텍스트 채우기 중에 fdhost.exe와 sqlservr.exe는 버퍼 풀 메모리를 두고 경합할 수 있습니다. 그 결과 공유 메모리가 부족해져 배치 재시도, 메모리 스래싱 및 fdhost.exe 프로세스에서의 덤프가 발생합니다.

• 페이징 문제. 예를 들어 확장이 제한된 작은 페이지 파일이 있는 시스템처럼 페이지 파일 크기가 부족한 경우에도 fdhost.exe 또는 sqlservr.exe 프로세스에서 메모리가 부족해질 수 있습니다. 크롤 로그에서 메모리 관련 장애가 없다면, 과도한 페이징이 성능 저하를 초래할 가능성이 큽니다.

필터 데몬 호스트 프로세스의 메모리 요구량을 추정합니다 (fdhost.exe)

프로세스가 fdhost.exe 채우기 위해 필요한 메모리 양은 주로 사용하는 전체 텍스트 크롤 범위 수, 인바운드 공유 메모리(ISM) 크기, 그리고 최대 ISM 인스턴스 수에 따라 달라집니다.

필터 데몬 호스트의 메모리 소비량은 다음 공식을 사용하여 대략적으로 추정할 수 있습니다:

number_of_crawl_ranges * ism_size * max_outstanding_isms * 2

앞서 언급한 공식의 변수들의 기본 값은 다음과 같습니다:

다음 표는 의 fdhost.exe메모리 요구량을 추정하는 지침을 제시합니다. 이 테이블의 수식에서는 다음 값을 사용합니다.

• F는 (MB 단위)에서 필요한 fdhost.exe 메모리 추정값입니다.

• T는 시스템에서 사용 가능한 실제 총 메모리(MB)입니다.

• M이 최적의 설정입니다 max server memory .

다음 수식에 대한 필수 정보는 테이블 뒤에 있는 참고 사항을 참조하세요.

1. 여러 개의 전체 집단이 진행 중이라면, 각각의 메모리 요구량을 fdhost.exeF1, F2 등으로 따로 계산하세요. 그 다음 M 을 T - Σ(Fi)로 계산합니다.

2. 500MB는 시스템의 다른 프로세스에 필요한 예상 메모리 양입니다. 시스템에서 추가 작업을 수행하는 경우 이 값을 적절하게 늘입니다.

3. x64 플랫폼에서는 ism_size 8MB로 추정됩니다.

예시: 의 메모리 요구량을 추정합니다. fdhost.exe

이 예시는 8GB RAM과 4개의 듀얼코어 프로세서를 가진 64비트 컴퓨터를 위한 것입니다. 첫 번째 계산은 fdhost.exeF에 필요한 메모리를 추정합니다. 크롤 범위의 수는 8입니다.

F = 8 * 10 * 8 = 640

다음 계산은 (max server memory)의 최적 값을 얻습니다. 이 시스템에서 사용 가능한 총 물리적 메모리는 MB(T)입니다 8192.

M = 8192 - 640 - 500 = 7052

예시: Set max server memory

이 예시는 sp_configure와 RECONFIG Transact-SQL 문장을 사용하여 앞서 계산된 max server memory의 값으로 설정합니다: 7052

USE master;
GO

EXECUTE sp_configure 'max server memory', 7052;
GO

RECONFIGURE;
GO

서버 메모리 옵션에 대한 자세한 내용은 서버 메모리 구성 옵션을 참조하세요.

CPU 사용량 확인

전체 집단의 성능은 평균 CPU 사용량이 약 30% 미만일 때 최적이 아닙니다. CPU 사용량에 영향을 주는 몇 가지 요인은 다음과 같습니다.

• 페이지에 대한 높은 대기 시간

  페이지 대기 시간이 높은지 확인하려면 다음 Transact-SQL 문을 실행합니다.

  SELECT TOP 10 *
  FROM sys.dm_os_wait_stats
  ORDER BY wait_time_ms DESC;
  

  다음 표는 이자에 대한 대기 유형을 설명합니다.

  대기 유형	설명	가능한 해결 방법
  PAGEIO_LATCH_SH(_EX 또는 _UP)	이 대기 유형은 I/O 병목 현상을 나타낼 수 있으며, 이 경우 일반적으로 평균 디스크 대기열 길이가 길어집니다.	전체 텍스트 인덱스를 다른 디스크의 다른 파일 그룹으로 옮기면 I/O 병목 현상을 줄일 수 있습니다.
  PAGELATCH_EX (또는 _UP)	이 대기 유형은 동일한 데이터베이스 파일에 쓰려는 스레드들 간에 많은 경쟁이 있음을 나타낼 수 있습니다.	전체 텍스트 인덱스가 위치한 파일 그룹에 파일을 추가하면 이러한 갈등을 완화하는 데 도움이 될 수 있습니다.

  자세한 내용은 sys.dm_os_wait_stats 참조하세요.

• 기본 테이블 스캔의 비효율성

  전체 데이터 인구는 기본 테이블을 스캔하여 일괄 처리를 생성합니다. 이 테이블 스캔은 다음과 같은 상황에서 비효율적일 수 있습니다:

  • 기본 테이블이 전체 텍스트 인덱싱되는 행 외부 열의 비율이 높은 경우 기본 테이블을 검사하여 일괄 처리를 생성하면 병목 현상이 발생할 수 있습니다. 이 경우, varchar(max) 또는 nvarchar(max) 를 사용해 작은 데이터를 행 안으로 이동시키는 것이 도움이 될 수 있습니다.

  • 기본 테이블이 심하게 조각화된 경우 검색이 비효율적일 수 있습니다. 행 밖 데이터와 인덱스 단편화 계산에 관한 정보는 sys.dm_db_partition_stats 및 sys.dm_db_index_physical_stats를 참조하세요.

    조각화를 줄이려면 클러스터형 인덱스를 다시 구성하거나 다시 작성할 수 있습니다. 자세한 내용은 인덱스 유지 관리를 최적화하여 쿼리 성능을 개선하고 리소스 소비를 줄이는 방법을 참조하십시오.

문서의 느린 인덱싱 문제 해결

전체 텍스트 엔진은 전체 텍스트 인덱스를 채울 때 다중 스레드 필터 및 단일 스레드 필터라는 두 가지 필터 유형을 사용합니다.

• Word 문서와 같은 일부 문서는 멀티스레드 필터를 사용합니다.
• Adobe Acrobat 휴대용 문서 형식(PDF) 문서와 같은 다른 문서들은 단일 스레드 필터를 사용합니다.

보안상의 이유로 필터는 필터 데몬 호스트 프로세스에 의해 로드됩니다. 서버 인스턴스는 모든 다중 스레드 필터에 대해 다중 스레드 프로세스를 사용하고 모든 단일 스레드 필터에 대해 단일 스레드 프로세스를 사용합니다. 다중 스레드 필터를 사용하는 문서에 단일 스레드 필터를 사용하는 포함된 문서가 포함된 경우 전체 텍스트 엔진은 포함된 문서에 대한 단일 스레드 프로세스를 시작합니다. 예를 들어 PDF 문서가 포함된 Word 문서가 있을 경우 전체 텍스트 엔진은 Word 콘텐츠에 대해 다중 스레드 프로세스를 사용하고 PDF 콘텐츠에 대해 단일 스레드 프로세스를 시작합니다. 하지만 단일 스레드 필터는 이 환경에서 잘 작동하지 않을 수 있고 필터링 과정을 불안정하게 만들 수 있습니다.

이러한 포함이 일반적인 특정 상황에서는 불안정으로 인해 프로세스가 충돌할 수 있습니다. 이 상황이 발생하면 Full-Text 엔진은 실패한 문서(예: PDF 콘텐츠가 포함된 Word 문서)를 단일 스레드 필터링 프로세스로 재라우팅합니다. 다시 라우팅이 자주 발생하면 전체 텍스트 인덱싱 프로세스의 성능이 저하됩니다.

이 문제를 해결하려면 컨테이너 문서(예시의 Word 문서)의 필터를 단일 스레드 필터로 표시하세요. 필터를 단일 스레드 필터로 표시하려면 필터의 ThreadingModel 레지스트리 값을 Apartment Threaded로 설정하십시오. 싱글 스레드 아파트에 대한 정보는 COM 스레드 모델 이해 및 활용을 참조하세요.

관련 콘텐츠

• 서버 메모리 구성 옵션
• 서버 구성: 최대 전체 텍스트 크롤 범위
• 전체 텍스트 인덱스 채우기
• 전체 텍스트 인덱스 만들기 및 관리
• sys.dm_fts_memory_buffers(Transact-SQL)
• sys.dm_fts_memory_pools(Transact-SQL)
• 전체 텍스트 인덱싱 문제 해결
• 전체 텍스트 검색 아키텍처