SQL Server 2019 메모리 최적화 TempDB 메타 데이터

1. 메모리 내 최적화 된 TempDB 메타 데이터의 세부 정보
2. 메모리 내 최적화 된 TempDB 메타 데이터를 보여주기 위해 T-SQL 샘플 준비
3. 메모리 최적화 TempDB 메타 데이터 기능을 활성화하는 방법을 논의합니다.
4. OStress 유틸리티의 사용 설명
5. OStress 명령을 사용하여 워크로드 구현
6. tempdb 카탈로그 뷰에서 성능 및 리소스 경합 측정
7. 한계에 대해 논의
8. 그리고 더…

소개

인 메모리 최적화 기술은 TempDB 데이터베이스의 가장 많이 기다려온 리소스 경합 관리 중 하나에 그 공간을 활용했습니다. 메모리 최적화 TempDB 메타 데이터 경합은 이전 SQL Server 버전에서 실행되는 과중한 워크로드를 확장하는 데 병목 현상이 나타 났던 많은 과중한 워크로드에 대한 솔루션입니다. SQL Server 2019는 메모리에 최적화 된 데이터베이스  기능 세트에 새로운 기능인 "메모리 최적화 TempDB 메타 데이터"를 도입했습니다 . 리소스 경합을 대폭 단순화하고 효과적으로 관리하고 과도한 TempDB 워크로드를 처리하고 확장 할 수있는 유연성을 제공합니다.

시작하다

이 기사는 두 단계로 설명됩니다. 먼저 TempDB의 작동 방식에 대한 일반적인 시나리오를 살펴 보겠습니다. 모두 알고 있듯이 SQL Server에서 가장 일반적인 성능 문제는 TempDB 경합으로 간주됩니다. 임시 테이블을 생성하는 시나리오를 시뮬레이션하고 매번 임시 테이블이 생성되는 방식과 SQL Server가 메타 데이터 관리와 함께 리소스 할당을 유지하기위한 내부 압력을 구축하는 방법을 살펴볼 것입니다.

둘째, 임시 테이블 메타 데이터를 관리 할 때 시스템 테이블이 유지되는 방식을 보여주고 래치가없는 비 지속적 테이블로 이동하는 방법을 알아보기 위해 메모리 최적화 TempDB 의 새로운 기능을 살펴 보겠습니다.

T-SQL 스크립트 준비

이 섹션에서는 중첩 스크립트를 만드는 방법, OSTRESS를 사용하여 스트레스 테스트를 수행하는 방법, 페이지 래치 세부 정보를 찾는 방법, 경합을 측정하는 방법에 대해 알아 봅니다.

TempDB 데이터베이스에서 경합 및 워크로드를 생성하는 간단한 스크립트입니다. sys.sysobjects 시스템 뷰 에서 삽입 된 행을 사용하여 각 호출에서 #dummytable이 생성되는 것을 볼 수 있습니다 . OStress 유틸리티를 사용하여 SQL Server를 강조하는 방법 에 대한 Nikilesh Patel의 기사를 참조 할 수 있습니다 .

T-SQL 스크립트

SSMS를 시작하고 새 쿼리 창을 연 후 다음 T-SQL 스크립트를 실행하여 두 개의 프로 시저를 만듭니다.

스크립트 1 :

CREATE OR ALTER PROCEDURE dbo.SPROC_tempdbMemOptimzedtest_1 AS BEGIN    SET       NOCOUNT       ON; CREATE TABLE #DummyTable ( ID BIGINT NOT NULL ); INSERT INTO    #DummyTable    SELECT       T.RowNum    FROM       (          SELECT             TOP (1) ROW_NUMBER() OVER (          ORDER BY (             SELECT                NULL)) RowNum             FROM                sys.sysobjects       )       T; END

스크립트 2 :

CREATE OR ALTER PROCEDURE dbo.SPROC_tempdbMemOptimzedtest_2 AS BEGIN    DECLARE @id int = 0; WHILE( @id < 10 ) BEGIN    EXECUTE dbo.SPROC_tempdbMemOptimzedtest_1;    SET       @id = @id + 1; END END

OStress 유틸리티

OStress는 RML 유틸리티 패키지와 함께 번들로 제공되는 무료 Microsoft입니다. 데이터베이스에서 T-SQL 쿼리를 강조하거나 재생하는 데 사용할 수있는 확장 가능한 명령 줄 도구입니다. 여러 반복으로 스크립트를 실행하여 부하 증가 워크로드를 시뮬레이션하는 데 사용할 수있는 다양한 스위치를 나열합니다.

TempDB 데이터베이스에 대한 스트레스를 시뮬레이션하기 위해 OStress 유틸리티를로드하겠습니다. 이 섹션에서는 OStress 명령을 사용하여 배치 파일을 만드는 방법을 살펴 봅니다. 이렇게하면 호출을 매우 간단하게 유지할 수 있으며 배치 파일을 쉽게 변경할 수 있습니다.

이 예에서는 다음 내용 으로 stresstest.bat 파일을 만듭니다 .

cd C:\Program Files\Microsoft Corporation\RMLUtils\   ostress.exe -Slocalhost -E -dMongoDB -Q'EXECUTE dbo.SPROC_tempdbMemOptimzedtest_2;' –n100 –r30

이제 배치 파일을 실행할 준비가되었습니다.

ostress.exe, '-S'에 사용되는 매개 변수는 서버 (이 경우 localhost)입니다. '–d'는 데이터베이스 용입니다. MongoDB는 저장 프로 시저를 생성 한 데이터베이스이고 '–Q'는 독립 실행 형 SQL 쿼리를 실행하기위한 것입니다.이 경우에는 30 회 반복을 동시에 실행하는 100 개의 세션으로 실행되는 반복 저장 프로 시저입니다. .

배치 파일을 실행하고 워크로드를 측정 해 보겠습니다.

다음 SQL을 실행하여 TempDB에서 경합을 확인하십시오.

SELECT    req.session_id,    req.wait_type,    req.wait_resource,    OBJECT_NAME(inf.[object_id], inf.database_id) as [object_name],    req.blocking_session_id,    req.command,    SUBSTRING(txt.text,    (       req.statement_start_offset / 2    )     + 1,    ( (       CASE          req.statement_end_offset          WHEN             - 1          THEN             DATALENGTH(txt.text)          ELSE             req.statement_end_offset       END       - req.statement_start_offset) / 2    )    + 1) AS statement_text, inf.database_id, inf.[file_id], inf.page_id, inf.[object_id], inf.index_id, inf.page_type_desc FROM    sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS txt    CROSS APPLY sys.fn_PageResCracker (req.page_resource) AS pgc    CROSS APPLY sys.dm_db_page_info(pgc.[db_id], pgc.[file_id], pgc.page_id, 'DETAILED') AS inf WHERE    req.wait_type like '%page%'

다음 출력에서 ​​wait_type 'PAGELATCH_EX'를 볼 수 있으며 TempDB 데이터베이스의 EXECUTE 재귀 저장 프로시 저도 볼 수 있습니다.

다음 스 니펫에서 100 개의 사용자 연결 시뮬레이션으로 재귀 프로 시저 호출이 실행되고 30 개의 동시 실행이 67 초 소요되었음을 알 수 있습니다.

이 섹션에서는 메모리 최적화 TempDB 메타 데이터의 의미를 연구하고 이해합니다.

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

SQL Server에서 TempDB 기능을 사용하려면 두 단계가 필요하고 세 번째 단계는 선택 사항입니다.

1. 서버 구성을 변경하려면 다음 T-SQL을 실행하십시오.

   ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA=ON;

2. SQL Server 인스턴스에 반영 할 변경 사항에 참여하려면 SQL 서비스를 다시 시작하십시오.

   sp_configure 를 사용하여 다음 T-SQL을 사용하여 메모리 최적화 TempDB 메타 데이터 매개 변수를 설정할 수도 있습니다 .

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

   다음 스 니펫에서 TempDB 메타 데이터 메모리 최적화 매개 변수가 이미 활성화되어 있음을 확인할 수 있습니다.

   

3. 서버 구성을 확인하려면 다음 T-SQL을 실행하십시오.

   SELECT SERVERPROPERTY('IsTempDBMetadataMemoryOptimized') AS IsTempDBMetadataMemoryOptimized;

   출력 "1"은 메모리 최적화 TempDB가 SQL Server 인스턴스에서 활성화되었음을 나타냅니다.

   

   sp_configure 및 sys.configuration 시스템보기를 사용하여 구성을 확인할 수도 있습니다.

   EXEC sp_configure 'tempdb metadata memory-optimized'   select * From sys.configurations where name like '%tempdb%'

   

OStress 유틸리티를 사용하여 SQL Server를 강조하겠습니다. 이 섹션에서는 OStress 명령을 사용하여 적용된 과중한 워크로드를 볼 수 있습니다.

이 경우 앞서 언급 한 동일한 OStress 명령 유틸리티가 실행되고 (배치 파일이 다시 실행 됨) 출력이 아래 스 니펫에 캡처됩니다.

아래 T-SQL 스크립트는 현재 SQL Server 인스턴스에서 실행중인 쿼리를 찾는 데 사용됩니다. T-SQL에서는 페이지 할당 및 페이지 리소스 wait_types 세부 정보를 보는 데 사용되는 새로운 동적 관리 함수의 사용도 볼 수 있습니다.

SELECT    req.session_id,    req.wait_type,    req.wait_resource,    OBJECT_NAME(inf.[object_id], inf.database_id) as [object_name],    req.blocking_session_id,    req.command,    SUBSTRING(txt.text,    (       req.statement_start_offset / 2    )     + 1,    ( (       CASE          req.statement_end_offset          WHEN             - 1          THEN             DATALENGTH(txt.text)          ELSE             req.statement_end_offset       END       - req.statement_start_offset) / 2    )    + 1) AS statement_text, inf.database_id, inf.[file_id], inf.page_id, inf.[object_id], inf.index_id, inf.page_type_desc FROM    sys.dm_exec_requests AS req    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS txt    CROSS APPLY sys.fn_PageResCracker (req.page_resource) AS pgc    CROSS APPLY sys.dm_db_page_info(pgc.[db_id], pgc.[file_id], pgc.page_id, 'DETAILED') AS inf WHERE    req.wait_type like '%page%'

다음 스 니펫에서 TempDB 테이블 관리에 경합이 없음을 알 수 있습니다.

다음 스 니펫에서 100 명의 사용자 연결과 30 개의 동시 실행으로 SP 실행을 완료하는 데 31 초 밖에 걸리지 않음을 알 수 있습니다.

MEMORY_OPTIMIZED TEMPDB_METADATA를 비활성화하려면 다음 T-SQL을 실행합니다.

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA=OFF;

변경 사항을 적용하려면 SQL 서비스를 다시 시작해야합니다.

결론

이 기사에서는 새로운 메모리 최적화 TempDB 메타 데이터에 대해 설명했습니다. 또한 시스템 카탈로그가 래치없는 임시 테이블 메타 데이터 관리에 어떻게 관여하는지 살펴 보았습니다. SQL Server 2019는 인 메모리 최적화 기술과 함께 또 다른 확장 성 기능을 도입했습니다. TempDB 메타 데이터 경합은 오랫동안 병목 현상이었습니다. tempdb 메타 데이터 시스템 테이블을 관리하는 기존 방법은 래치가없는 비 지속성 메모리 최적화 테이블 사용으로 이동되었습니다.

이 확장 가능한 옵션을 통해 SQL Server는 tempdb 작업을 훨씬 더 나은 방식으로 처리 할 수 ​​있습니다. 이 기능을 활성화하기위한 제한 사항도 이해하고 있기를 바랍니다. 메모리 최적화 메타 데이터를 켜고 끄려면 SQL 서비스를 다시 시작해야합니다.

메모리 최적화 테이블에서 실행되는 SQL 쿼리는 SQL 힌트를 지원하지 않습니다. 잠금 및 격리 SQL 힌트를 사용하는 경우 TempDB 메타 데이터 카탈로그 뷰에 대해 실행되는 쿼리가 작동하지 않습니다.

메모리 최적화 tempdb 메타 데이터 테이블의 다른 제한은 격리입니다. 이는 단일 트랜잭션이 둘 이상의 데이터베이스에있는 메모리 최적화 테이블에 액세스 할 수 없음을 의미합니다. 트랜잭션 내에서 자체적으로 포함됩니다.

이 경우 동일한 트랜잭션에서 TempDB 시스템보기에 액세스하면 다음 오류가 발생합니다.

"메모리 최적화 테이블 또는 고유하게 컴파일 된 모듈에 액세스하는 사용자 트랜잭션은 둘 이상의 사용자 데이터베이스 또는 데이터베이스 모델 및 msdb에 액세스 할 수 없으며 마스터에 쓸 수 없습니다."