SQL Server TempDB 데이터베이스 및 래치 경합

SQL Server에서 사용되는 TempDB 데이터베이스는 무엇입니까?

TempDB 데이터베이스는 SQL Server의 시스템 데이터베이스 중 하나이지만 다른 시스템 데이터베이스와 구별되는 다양한 고유 기능을 가지고 있습니다. 이 SQL Server TempDB 데이터베이스에 전역 및 로컬 임시 테이블이 만들어지고 이러한 테이블의 데이터는이 데이터베이스에 저장됩니다. 동시에이 데이터베이스 리소스에서 테이블 변수, 임시 저장 프로 시저 및 커서가 사용됩니다. 또한 TempdDB 리소스는 다음 기능에서도 사용됩니다.

• 스냅 샷 격리 및 읽기 커밋 된 스냅 샷 격리
• 온라인 인덱스 작업
• MARS – (다중 활성 결과 집합)

SQL 엔진을 다시 시작하면 TempdDB 데이터베이스가 삭제되고 다시 생성됩니다. 우리는이 데이터베이스를 백업 할 수 없으며 복구 모델을 단순에서 다른 것으로 변경할 수 없습니다. 이 모든 것을 고려할 때 TempDB 데이터베이스 설정이 쿼리 성능에 직접적인 영향을 미친다고 말할 수 있습니다.

SQL Server의 래치는 무엇입니까?

SQL 버퍼 풀은 SQL Server 용으로 운영 체제에서 예약 한 메모리 공간이며 SQL 버퍼 캐시라고도합니다. SQL Server는 데이터 페이지를 읽거나 조작하기 위해 디스크에서 메모리로 데이터 페이지를 전송하고 특수 논리 에 따라 디스크로 다시 보냅니다 . 이 메커니즘의 주요 목적은 메모리가 항상 스토리지 시스템보다 빠르기 때문에 클라이언트에 더 빠른 성능을 제공하려는 것입니다. 이러한 맥락에서 우리는 버퍼 풀에서 데이터 페이지 일관성을 보장하는 메커니즘이 필요합니다. 래치는 SQL Server가 메모리에서 데이터 페이지의 일관성을 보장 할 수 있도록 메모리에 저장된 데이터 구조를 불일치 및 손상으로부터 보호하는 데 사용되는 동기화 개체입니다. 이 동기화 작업은 SQL Server에서 내부적으로 관리합니다.

TempDB 데이터베이스 메타 데이터 경합

TempDB 메타 데이터 경합은 임시 테이블을 생성하는 동안 많은 세션이 SQL Server TempDB의 시스템 테이블에 동시에 액세스하려고 할 때 발생합니다. 이러한 과중한 워크로드는 이러한 이유로 인해 이러한 시스템 테이블에서 대기 시간을 유발하고 쿼리 성능이 저하됩니다.

이제이 문제를 시뮬레이션하기 위해 TempDB에 가짜 워크로드를 생성합니다. SQLQueryStress 라는 구식이지만 좋은 도구 를 사용하여 TempDB 데이터베이스에 가짜 워크로드를 생성합니다.

먼저 다음 절차를 생성합니다. 이 저장 프로시 저는 임시 테이블을 만들고 sys.all_columns 테이블에서 임의의 20 개 행을 삽입합니다.

CREATE PROC ProcTest AS BEGIN CREATE TABLE #t1 (   c1 INT,   c2 INT); INSERT INTO #t1        SELECT TOP 20 column_id,                      system_type_id        FROM sys.all_columns WITH(NOLOCK); END

SQLQueryStress를 시작하고 다음 쿼리를 쿼리 패널에 붙여 넣습니다. 이 쿼리는 WHILE 루프에서 ProcTest 저장 프로 시저를 100 번 실행합니다.

DECLARE @i INT; SET @i = 1; WHILE @i <= 100     BEGIN         EXEC ProcTest;         SET @i = @i + 1;     END

우리는 설정합니다 반복의 수 100을 설정할 것 스레드 수 저장 프로 시저가 2500 번 실행되도록 25을.

데이터베이스 버튼 을 클릭하고 데이터베이스 연결 및 자격 증명 설정을 지정합니다.

GO 버튼을 클릭 하여 쿼리 실행을 시작합니다.

SQLQueryStress가 쿼리를 수행하는 동안 sp_WhoisActive를 실행 하고 결과를 분석합니다.

보시다시피 PAGELATCH_EX 대기 유형은 TempDB 데이터베이스의 wait_info 열에서 볼 수 있습니다. TempDB 데이터베이스의 경우 SQL Server 2019의 새로운 기능을 사용하여이 대기 시간을 극복 할 수 있습니다. 다음 섹션에서는이 기능에 대해 알아 봅니다.

메모리 최적화 TempDB 메타 데이터

메모리 최적화 TempDB 메타 데이터 기능을 활성화하면 일부 SQL Server TempDB 시스템 테이블을 비 지속적 메모리 최적화 테이블로 변환하여 TempDB의 시스템 테이블에 대한 대기 시간을 최소화합니다. 메모리 최적화 테이블은 짧은 지연 시간, 높은 처리량 및 가속화 된 응답 시간을 제공하므로이 기능은 이러한 성능 향상을 활용합니다.

다음 쿼리를 통해이 기능을 활성화 할 수 있습니다.

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

또는 다음 쿼리를 사용하여이 옵션을 활성화 할 수 있습니다.

EXEC sys.sp_configure N' show advanced options', 1; RECONFIGURE; EXEC sys.sp_configure N'tempdb metadata memory-optimized', 1; RECONFIGURE;

다음 쿼리는이 기능의 상태를 감지하는 데 도움이됩니다.

SELECT CASE  SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') WHEN 1 THEN 'Enable' WHEN 0 THEN 'Disable' END AS 'Memory-Optimized TempDB Metadata Status'

메모리 최적화 TempDB 메타 데이터 기능을 활성화 한 후 SQL Server 엔진을 다시 시작해야합니다. SQL Server를 다시 시작한 후 다음 쿼리를 통해 메모리 최적화 테이블로 변환 된 테이블 목록을 볼 수 있습니다.

SELECT mem_table.[object_id], obj.name   FROM tempdb.sys.all_objects AS obj   INNER JOIN tempdb.sys.memory_optimized_tables_internal_attributes AS mem_table   ON obj.[object_id] = mem_table.[object_id]

동일한 매개 변수를 사용하여 동일한 쿼리에 대해 SQLQueryStress를 다시 실행할 때. sp_WhoIsActive 출력이 변경되고 PAGELATCH_EX 대기 유형이 표시되지 않습니다 .

메모리 최적화 TempDB 메타 데이터 기능에는 몇 가지 제한 사항이 있으며 사용을 결정하기 전에 다음 제한 사항을 고려해야합니다.

• 메모리 최적화 TempDB 메타 데이터가 활성화 된 경우 임시 테이블에 대해 열 저장소 인덱스를 만들 수 없습니다.

  CREATE TABLE #temp1 (val1 INT, val2 NVARCHAR(50)); CREATE COLUMNSTORE INDEX indexcol1 ON #temp1(val2);

  

처음에 우리는 이름이 # temp1 인 로컬 임시 테이블을 만들었고 columnstore 인덱스를 만들려고 할 때 메모리 최적화 메타 데이터 기능이 활성화되어 있기 때문에 성공하지 못했습니다.

• sp_estimate_data_compression_savings 기본 제공 프로시 저는 메모리 최적화 TempDB 메타 데이터가 활성화 된 경우 columnstore 인덱스를 포함하는 테이블에 대해 실행되지 않습니다.

  CREATE TABLE t1 (val1 INT, val2 NVARCHAR(50)); CREATE COLUMNSTORE INDEX indexcol1 ON t1(val2); GO EXEC sp_estimate_data_compression_savings 'dbo', 't1', NULL, NULL, 'ROW' ;

  

저장 프로 시저 인 sp_estimate_data_compression_savings 는 작업을 압축하기 전에 테이블에 대해 예상되는 압축 이득을 계산합니다. 그러나 메모리 최적화 TempDB 메타 데이터 옵션을 활성화 한 경우이 절차는 columnstore 인덱스를 포함하므로 t1 테이블에 대해 작동하지 않습니다.

TempDB 데이터베이스 할당 페이지 경합

데이터 페이지 는 데이터 를 저장하는 SQL Server의 기본 단위이며 데이터 페이지의 크기는 8KB입니다. 물리적으로 연속 된 8 개의 데이터 페이지는 익스텐트 라고 합니다. 할당 된 익스텐트에 대한 정보는 GAM (Global Allocation Map)에 의해 기록됩니다 . 혼합으로 사용되는 익스텐트에 대한 정보는 SGAM (Shared Global Allocation Map)에 의해 기록됩니다 . 페이지 여유 공간 (PFS) 은 익스텐트의 어느 페이지에서 사용 가능한 여유 공간을 기록합니다.

세션은 임시 테이블을 만들고 삭제할 때 SQL Server TempDB 할당 페이지를 업데이트해야합니다. 이 동시 연결 수가 증가하기 시작하면 한 번에 하나의 스레드 만 이러한 페이지를 변경할 수 있으므로 다른 스레드가이 페이지가 할당 된 리소스를 해제 할 때까지 기다려야하기 때문에 이러한 페이지 할당에 액세스하는 것이 더 어려워집니다. 이제이 시나리오를 시뮬레이션합니다.

• SQLQueryStress를 시작하고 다음 쿼리를 쿼리 패널에 붙여 넣습니다.

  SELECT TOP 2500 * INTO #t1 FROM sys.all_objects WITH(NOLOCK);

• 우리는 설정합니다 반복의 수를 100 세트로 스레드 수 200 :

  

• 데이터베이스 버튼 을 클릭하고 데이터베이스 자격 증명 및 기타 설정을 지정합니다.

  

• GO 버튼을 클릭 하여 쿼리 실행을 시작합니다.

  

SQLQueryStress가 쿼리를 수행하는 동안 sp_WhoisActive를 실행하고 wait_info 열의 결과를 분석합니다.

EXEC sp_WhoIsActive

보시다시피 PAGELATCH_UP 대기 유형은 wait_info 열에서 볼 수 있습니다. TempDb 데이터베이스에 더 많은 데이터 파일을 추가하면이 문제가 최소화되며 Microsoft는 필요한 파일 수에 대한 공식을 권장합니다.

“논리 프로세서 수가 8 개 이하이면 논리 프로세서와 동일한 수의 데이터 파일을 사용하십시오. 논리 프로세서 수가 8 개보다 크면 8 개의 데이터 파일을 사용합니다. 경합이 계속되면 경합이 허용 가능한 수준으로 줄어들 때까지 논리 프로세서 수까지 4의 배수로 데이터 파일 수를 늘립니다.”

이 공식에 따르면 TempDB 데이터베이스의 파일 번호를 늘려이 문제를 최소화 할 수 있습니다.