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

적용 대상:SQL ServerAzure SQL Database

이 항목에서는 전체 텍스트 인덱스 및 쿼리 성능 저하의 몇 가지 일반적인 원인에 대해 설명합니다. 또한 이러한 문제를 완화하고 성능을 향상시키기 위한 몇 가지 제안 사항도 제공합니다.

성능 문제의 일반적인 원인

하드웨어 리소스 문제

전체 텍스트 인덱싱 및 전체 텍스트 쿼리의 성능은 메모리, 디스크 속도, CPU 속도 및 컴퓨터 아키텍처와 같은 하드웨어 리소스의 영향을 받습니다.

전체 텍스트 인덱싱 성능이 저하되는 주요 원인은 하드웨어 리소스 제한입니다.

• CPU. 필터 디먼 호스트 프로세스(fdhost.exe) 또는 SQL Server 프로세스(sqlservr.exe)의 CPU 사용량이 100%에 가까운 경우 CPU는 병목 상태가 됩니다.

• 메모리. 실제 메모리가 부족한 경우 메모리가 병목 상태일 수 있습니다.

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

  참고 항목

  SQL Server 2008(10.0.x)부터 전체 텍스트 엔진은 sqlservr.exe 프로세스의 일부이므로 AWE 메모리를 사용할 수 있습니다. 자세한 내용은 전체 텍스트 검색 아키텍처를 참조하세요.

전체 텍스트 일괄 처리 문제

시스템에 하드웨어 병목 현상이 없는 경우 전체 텍스트 검색의 인덱싱 성능은 대부분 다음 사항에 따라 달라집니다.

• SQL Server에서 전체 텍스트 일괄 처리를 만드는 데 걸리는 시간.

• 필터 데몬에서 이러한 일괄 처리를 완료할 수 있는 시간

전체 텍스트 인덱스 채우기 문제

• 모집단의 유형입니다. 전체 채우기와 달리 증분, 수동 및 자동 변경 내용 추적 채우기는 더 빠른 속도를 달성하기 위해 하드웨어 리소스를 최대화하도록 설계되지 않았습니다. 따라서 이 항목의 튜닝 제안은 증분, 수동 또는 자동 변경 내용 추적 채우기를 사용하는 경우 전체 텍스트 인덱싱의 성능을 향상시키지 못할 수 있습니다.

• 마스터 병합. 채우기가 완료되면 인덱스 조각을 하나의 마스터 전체 텍스트 인덱스로 병합하는 최종 병합 프로세스가 트리거됩니다. 이로 인해 많은 인덱스 조각이 아닌 마스터 인덱스만 쿼리해야 하므로 쿼리 성능이 향상되고 관련성 순위에 더 나은 점수 매기기 통계가 사용될 수 있습니다. 그러나 인덱스 조각을 병합할 때 많은 양의 데이터를 읽고 써야 하기 때문에 마스터 병합에 많은 I/O 사용량이 필요할 수 있지만 이로 인해 들어오는 쿼리가 차단되지는 않습니다.

  많은 양의 데이터를 마스터 병합하면 장기 실행 트랜잭션이 생성될 수 있으며 이로 인해 검사점이 사용되는 동안 트랜잭션 로그의 잘림이 지연될 수 있습니다. 이 경우 전체 복구 모델에서 트랜잭션 로그가 크게 증가할 수 있습니다. 전체 복구 모델을 사용하는 데이터베이스에서 큰 전체 텍스트 인덱스를 다시 구성하기 전에 트랜잭션 로그에 장기 실행 트랜잭션을 위한 충분한 공간이 포함되어 있는지 확인하는 것이 가장 좋습니다. 자세한 내용은 트랜잭션 로그 파일의 크기 관리를 참조하세요.

전체 텍스트 인덱스의 성능 조정

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

• 모든 CPU 프로세서 또는 코어를 최대로 활용하려면 sp_configure 'max full-text crawl range'를 시스템의 CPU 개수로 설정합니다. 이 구성 옵션에 대한 자세한 내용은 max full-text crawl range 서버 구성 옵션을 참조하세요.

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

• UPDATE STATISTICS 문을 사용하여 기본 테이블의 통계를 업데이트합니다. 더 중요한 것은 전체 채우기에 대한 클러스터형 인덱스 또는 전체 텍스트 키에 대한 통계를 업데이트하는 것입니다. 이렇게 하면 다중 범위 채우기에서 테이블에 적절한 파티션을 생성하는 데 도움이 됩니다.

• 대규모 다중 CPU 컴퓨터에서 전체 채우기를 수행하기 전에 fdhost.exe 프로세스 및 운영 체제 사용에 충분한 메모리를 남겨 두도록 최대 서버 메모리 값을 설정하여 버퍼 풀의 크기를 일시적으로 제한하는 것이 좋습니다. 자세한 내용은 이 항목의 뒷부분에 있는 필터 디먼 호스트 프로세스(fdhost.exe)의 메모리 요구 사항 추정을 참조하세요.

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

전체 모집단의 성능 문제 해결

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

성능 문제를 진단하려면 전체 텍스트 탐색 로그를 검토합니다.

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

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

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

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

• <DatabaseID> - 데이터베이스의 ID입니다. <dbid> 는 앞에 0이 있는 5자리 숫자입니다.
• <FullTextCatalogID> - 전체 텍스트 카탈로그 ID입니다. <catid> 는 앞에 0이 있는 5자리 숫자입니다.
• <n> - 동일한 전체 텍스트 카탈로그의 하나 이상의 크롤링 로그가 있음을 나타내는 정수입니다.

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

실제 메모리 사용량 확인

전체 텍스트 채우기를 수행하는 동안 fdhost.exe 또는 sqlservr.exe의 메모리가 부족하거나 아예 없을 수 있습니다.

• 전체 텍스트 크롤링 로그에 fdhost.exe가 자주 다시 시작되는 것으로 나타나거나 오류 코드 8007008이 반환된 것으로 나타날 경우 해당 프로세스 중 하나에 메모리 부족 문제가 발생한 것입니다.
• fdhost.exe가 특히 대형 다중 CPU 컴퓨터에서 덤프를 생성하는 경우 이는 메모리 부족 때문일 수 있습니다.
• 전체 텍스트 크롤링에서 사용되는 메모리 버퍼에 대한 정보를 얻으려면 sys.dm_fts_memory_buffers(Transact-SQL)를 참조하세요.

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

• 메모리가 부족합니다. 전체 채우기 중에 사용할 수 있는 실제 메모리 양이 0이면 SQL Server 버퍼 풀이 시스템에서 대부분의 실제 메모리를 소비할 수 있습니다.

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

  SQL Server 버퍼 풀의 최대 서버 메모리 값을 적절하게 설정하여 이 문제를 해결할 수 있습니다. 자세한 내용은 이 항목의 뒷부분에 있는 필터 디먼 호스트 프로세스(fdhost.exe)의 메모리 요구 사항 추정을 참조하세요. 전체 텍스트 인덱싱에 사용되는 일괄 처리 크기를 줄이는 것도 도움이 될 수 있습니다.

• 메모리 경합. 다중 CPU 컴퓨터에서 전체 텍스트 채우기 중에 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는 fdhost.exe에 필요한 예상 메모리(MB)입니다.

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

• M,(최적의 max server memory 설정)

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

수식 관련 참고 사항

1. 여러 전체 채우기가 진행 중인 경우 F1, F2와 같이 각 채우기 작업에 대한 fdhost.exe 메모리 요구 사항을 개별적으로 계산합니다. 그런 다음 M을 T- sigma**(_F_i)**로 계산합니다.
2. 500MB는 시스템의 다른 프로세스에 필요한 메모리의 추정치입니다. 시스템에서 추가 작업을 수행하는 경우 이 값을 적절하게 늘입니다.
3. . ism_size x64 플랫폼의 경우 8MB로 간주됩니다.

예: fdhost.exe의 메모리 요구 사항 예측

이 예제는 8GB RAM과 4개의 듀얼 코어 프로세서가 장착된 64비트 컴퓨터에 해당합니다. fdhost.exe -F에 필요한 메모리의 첫 번째 계산 추정값입니다. 탐색 범위 수는 8입니다.

F = 8*10*8 = 640

다음 계산에서는 max server memory의 최적 값 -M을 구합니다. 이 시스템에서 사용할 수 있는 총 실제 메모리는 MB-T-is입니다8192.

M = 8192-640-500 = 7052

예: 최대 서버 메모리 설정

이 예제에서는 sp_configure 및 RECONFIGURE Transact-SQL 문을 사용하여 최대 서버 메모리를 앞의 예제7052에서 M에 대해 계산된 값으로 설정합니다.

USE master;  
GO  
EXEC 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)	IO 병목 상태를 나타내며, 이 경우 일반적으로 평균 디스크 큐 길이가 깁니다.	전체 텍스트 인덱스를 다른 디스크의 다른 파일 그룹으로 이동하면 IO 병목 상태가 줄어들 수 있습니다.
  PAGELATCH_EX(또는 _UP)	이는 동일한 데이터베이스 파일에 쓰려는 스레드 간에 많은 경합을 나타낼 수 있습니다.	전체 텍스트 인덱스가 상주하는 파일 그룹에 파일을 추가하면 이러한 경합을 완화할 수 있습니다.

  자세한 내용은 sys.dm_os_wait_stats(Transact-SQL)를 참조 하세요.

• 기본 테이블 검사의 비효율성

  전체 채우기는 기본 테이블을 검색하여 일괄 처리를 생성합니다. 다음 시나리오에서는 이 테이블 검색이 비효율적일 수 있습니다.

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

  • 기본 테이블이 심하게 조각화된 경우 검색이 비효율적일 수 있습니다. 행 외부 데이터 및 인덱스 조각화를 계산하는 방법에 대한 자세한 내용은 sys.dm_db_partition_stats(Transact-SQL) 및 sys.dm_db_index_physical_stats(Transact-SQL)를 참조하세요.

    조각화를 줄이기 위해 클러스터형 인덱스 다시 구성 또는 다시 작성할 수 있습니다. 자세한 내용은 인덱스 다시 구성 및 다시 작성을 참조하세요.

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

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

• Microsoft Word 문서와 같은 일부 문서는 다중 스레드 필터를 사용하여 필터링됩니다.
• Adobe Acrobat PDF(이식 가능한 문서 형식) 문서와 같은 다른 문서는 단일 스레드 필터를 사용하여 필터링됩니다.

보안상의 이유로 필터는 필터 데몬 호스트 프로세스에 의해 로드됩니다. 서버 인스턴스는 모든 다중 스레드 필터에 다중 스레드 프로세스를 사용하고 모든 단일 스레드 필터에 단일 스레드 프로세스를 사용합니다. 다중 스레드 필터를 사용하는 문서에 단일 스레드 필터를 사용하는 포함된 문서가 포함된 경우 전체 텍스트 엔진은 포함된 문서에 대한 단일 스레드 프로세스를 시작합니다. 예를 들어 PDF 문서가 포함된 Word 문서가 있을 경우 전체 텍스트 엔진은 Word 콘텐츠에 대해 다중 스레드 프로세스를 사용하고 PDF 콘텐츠에 대해 단일 스레드 프로세스를 시작합니다. 그러나 단일 스레드 필터는 이 환경에서 제대로 작동하지 않을 수 있으며 필터링 프로세스를 불안정하게 만들 수 있습니다. 이러한 포함이 일반적인 특정 상황에서는 불안정으로 인해 프로세스가 충돌할 수 있습니다. 이 경우 전체 텍스트 엔진은 실패한 문서(예: 포함된 PDF 콘텐츠가 포함된 Word 문서)를 단일 스레드 필터링 프로세스로 다시 라우팅합니다. 다시 라우팅이 자주 발생하면 전체 텍스트 인덱싱 프로세스의 성능이 저하됩니다.

이 문제를 해결하려면 컨테이너 문서(이 예제의 Word 문서)에 대한 필터를 단일 스레드 필터로 표시합니다. 필터를 단일 스레드 필터로 표시하려면 필터의 ThreadingModel 레지스트리 값을 Apartment Threaded로 설정합니다. 단일 스레드 아파트에 대한 자세한 내용은 COM 스레딩 모델 이해 및 사용 백서를 참조하세요.

참고 항목

서버 메모리 서버 구성 옵션
max full-text crawl range 서버 구성 옵션
전체 텍스트 인덱스 채우기
전체 텍스트 인덱스 만들기 및 관리
sys.dm_fts_memory_buffers(Transact-SQL)
sys.dm_fts_memory_pools(Transact-SQL)
전체 텍스트 인덱싱 문제 해결
전체 텍스트 검색 아키텍처