개요
이 샘플에서는 SQL Server 2014의 일부인 새로운 In-Memory OLTP 기능을 보여 줍니다. 새로운 메모리 최적화 테이블과 고유하게 컴파일된 저장 프로시저를 보여 줍니다. 또한 In-Memory OLTP의 성능 이점을 보여 주는 데 사용할 수 있습니다.
비고
SQL Server 2016에 대한 이 항목을 보려면 In-Memory OLTP를 보여 주는 AdventureWorks 확장을 참조하세요.
이 샘플은 AdventureWorks 데이터베이스의 5개 테이블을 메모리 최적화로 마이그레이션하며 판매 주문 처리를 위한 데모 워크로드를 포함합니다. 이 데모 워크로드를 사용하여 서버에서 메모리 내 OLTP를 사용할 때의 성능 이점을 확인할 수 있습니다.
샘플 설명에서는 SQL Server 2014의 메모리 최적화 테이블에 대해 아직 지원되지 않는 기능을 설명하기 위해 테이블을 In-Memory OLTP로 마이그레이션할 때 발생하는 장단분에 대해 설명합니다.
이 샘플의 설명서는 다음과 같이 구성되어 있습니다.
샘플을 설치하고 데모 워크로드를 실행하기 위한 필수 구성 요소
샘플 테이블 및 프로시저에 대한 설명 - 여기에는 In-Memory OLTP 샘플에서 AdventureWorks에 추가된 테이블 및 프로시저에 대한 설명과 원래 AdventureWorks 테이블 중 일부를 메모리 최적화로 마이그레이션하기 위한 고려 사항이 포함됩니다.
데모 워크로드를 사용하여 성능 측정을 수행하기 위한 지침 - 여기에는 워크로드를 구동하는 데 사용하는 도구인 ostress를 설치 및 실행하기 위한 지침과 데모 워크로드 자체를 실행하기 위한 지침이 포함됩니다.
필수 조건
SQL Server 2014 RTM - Evaluation, Developer 또는 Enterprise Edition
성능 테스트의 경우 프로덕션 환경과 유사한 사양을 가진 서버입니다. 이 특정 샘플의 경우 SQL Server에서 16GB 이상의 메모리를 사용할 수 있어야 합니다. In-Memory OLTP용 하드웨어에 대한 일반적인 지침은 다음 블로그 게시물을 참조하세요.https://cloudblogs.microsoft.com/sqlserver/2013/08/01/hardware-considerations-for-in-memory-oltp-in-sql-server-2014/
AdventureWorks 기반의 인메모리 OLTP 샘플 설치
다음 단계에 따라 샘플을 설치합니다.
AdventureWorks2014 데이터베이스의 전체 백업을 위한 보관 파일을 다운로드합니다.
다음 https://msftdbprodsamples.codeplex.com/downloads/get/880661을 열어주세요.
파일을 로컬 폴더에 저장하라는 메시지가 표시되면
로컬 폴더(예: 'c:\temp')에 AdventureWorks2014.bak 파일을 추출합니다.
Transact-SQL 또는 SQL Server Management Studio 사용하여 데이터베이스 백업 복구.
데이터 파일의 대상 폴더 및 파일 이름을 식별(예:
'h:\DATA\AdventureWorks2014_Data.mdf'
로그 파일의 대상 폴더 및 파일 이름을 식별(예:
'i:\DATA\AdventureWorks2014_log.ldf'
- 로그 파일은 데이터 파일과 다른 드라이브에 배치해야 하며, 성능을 최대화하려면 SSD 또는 PCIe 스토리지와 같은 대기 시간이 짧은 드라이브가 가장 좋습니다.
T-SQL 스크립트 예제:
RESTORE DATABASE [AdventureWorks2014] FROM DISK = N'C:\temp\AdventureWorks2014.bak' WITH FILE = 1, MOVE N'AdventureWorks2014_Data' TO N'h:\DATA\AdventureWorks2014_Data.mdf', MOVE N'AdventureWorks2014_Log' TO N'i:\DATA\AdventureWorks2014_log.ldf' GOSQL Server Management Studio의 쿼리 창에서 다음 명령을 실행하여 데이터베이스 소유자를 서버의 로그인으로 변경합니다.
ALTER AUTHORIZATION ON DATABASE::AdventureWorks2014 TO [<NewLogin>]SQL Server 2014 RTM In-Memory OLTP 샘플에서 샘플 스크립트 ' SQL Server 2014 RTM In-Memory OLTP Sample.sql '을 로컬 폴더로 다운로드합니다.
'SQL Server 2014 RTM In-Memory OLTP Sample.sql' 스크립트의 'checkpoint_files_location' 변수 값을 업데이트하여 In-Memory OLTP 검사점 파일의 대상 위치를 가리킵니다. 검사점 파일은 순차적 IO 성능이 좋은 드라이브에 배치해야 합니다.
AdventureWorks2014 데이터베이스를 가리키도록 변수 'database_name'의 값을 업데이트합니다.
경로 이름의 일부로 백슬래시 ''를 포함해야 합니다.
예제:
:setvar checkpoint_files_location "d:\DBData\" ... :setvar database_name "AdventureWorks2014"
다음 두 가지 방법 중 하나로 샘플 스크립트를 실행합니다.
sqlcmd 명령줄 유틸리티 사용. 예를 들어 스크립트가 포함된 폴더의 명령줄 프롬프트에서 다음 명령을 실행합니다.
sqlcmd -S . -E -i "ssSQL14 RTM hek_2 Sample.sql"Management Studio 사용:
쿼리 창에서 'SQL Server 2014 RTM In-Memory OLTP Sample.sql' 스크립트 열기
AdventureWorks2014 데이터베이스를 포함하는 대상 서버에 연결
'쿼리 -> SQLCMD 모드'를 클릭하여 SQLCMD 모드를 사용하도록 설정합니다.
'실행' 단추를 클릭하여 스크립트를 실행합니다.
샘플 테이블 및 프로시저에 대한 설명
이 샘플은 AdventureWorks의 기존 테이블을 기반으로 제품 및 판매 주문에 대한 새 테이블을 만듭니다. 새 테이블의 스키마는 아래에 설명된 대로 몇 가지 차이점이 있지만 기존 테이블과 유사합니다.
새 메모리 최적화 테이블에는 접미사 '_inmem'이 있습니다. 이 샘플에는 접미사 '_ondisk'을 포함하는 해당 테이블도 포함되어 있습니다. 이러한 테이블을 사용하여 시스템에서 메모리 최적화 테이블과 디스크 기반 테이블의 성능을 일대일로 비교할 수 있습니다.
성능 비교를 위해 워크로드에 사용되는 메모리 최적화 테이블은 완전히 내구성이 있고 완전히 기록됩니다. 성능 이점을 얻기 위해 내구성이나 안정성을 희생하지 않습니다.
이 샘플의 대상 워크로드는 제품 및 할인에 대한 정보도 고려하는 판매 주문 처리입니다. 이를 위해서는 SalesOrderHeader, SalesOrderDetail, Product, SpecialOffer 및 SpecialOfferProduct 테이블이 필요합니다.
Sales.usp_InsertSalesOrder_inmem 및 Sales.usp_UpdateSalesOrderShipInfo_inmem 두 개의 새 저장 프로시저는 판매 주문을 삽입하고 지정된 판매 주문의 배송 정보를 업데이트하는 데 사용됩니다.
새 스키마 'Demo'에는 데모 워크로드를 실행하는 도우미 테이블 및 저장 프로시저가 포함되어 있습니다.
구체적으로 In-Memory OLTP 샘플은 AdventureWorks에 다음 개체를 추가합니다.
예제에서 추가된 테이블
새 테이블
Sales.판매주문헤더_inmem
- 판매 주문에 대한 헤더 정보입니다. 각 판매 주문마다 이 테이블에 하나의 행이 있습니다.
Sales.SalesOrderDetail_inmem
- 판매 주문에 대한 세부 정보. 판매 주문의 각 품목에 해당하는 행이 이 테이블에 하나씩 있습니다.
Sales.특별할인_inmem
- 각 특별 제안과 관련된 할인율을 포함한 특별 제안에 대한 정보입니다.
Sales.SpecialOfferProduct_inmem (Note: In this case, no translation improvements are proposed beyond maintaining the identifier as in the original translation.)
- 특별 할인과 제품 간의 참조 테이블입니다. 각 특별 할인에는 0개 이상의 제품이 포함될 수 있으며, 각 제품은 0개 이상의 특별 할인에 포함될 수 있습니다.
Production.Product_inmem
- 가격을 비롯한 제품에 대한 정보
Demo.DemoSalesOrderDetailSeed
- 데모 워크로드에서 샘플 판매 주문을 생성하는 데 사용됩니다.
테이블의 디스크 기반 변형:
Sales.SalesOrderHeader_ondisk
Sales.판매주문상세_ondisk
Sales.SpecialOffer_ondisk
Sales.SpecialOfferProduct_ondisk
Production.Product_ondisk
원래 디스크 기반 테이블과 새 메모리 최적화 테이블 간의 차이점
대부분의 경우 이 샘플에서 도입된 새 테이블은 원래 테이블과 동일한 열과 동일한 데이터 형식을 사용합니다. 그러나 몇 가지 차이점이 있습니다. 아래에 변경 근거와 함께 차이점이 나열되어 있습니다.
Sales.판매주문헤더_inmem
기본 제약 조건은 메모리 최적화 테이블과 마이그레이션한 있는 그대로 대부분의 기본 제약 조건에 대해 지원됩니다. 그러나 원래 테이블 Sales.SalesOrderHeader에는 OrderDate 및 ModifiedDate 열에 대한 현재 날짜를 검색하는 두 가지 기본 제약 조건이 포함되어 있습니다. 동시성이 많은 높은 처리량의 주문 처리 워크로드에서 전역 리소스는 경합 지점이 될 수 있습니다. 시스템 시간은 글로벌 리소스이며, 특히 판매 주문 헤더의 여러 열과 판매 주문 세부 정보에 대해 시스템 시간을 검색해야 하는 경우 판매 주문을 삽입하는 메모리 내 OLTP 워크로드를 실행할 때 병목 현상이 발생할 수 있음을 확인했습니다. 이 샘플에서는 삽입된 각 판매 주문에 대해 시스템 시간을 한 번만 검색하고 저장 프로시저 Sales.usp_InsertSalesOrder_inmem SalesOrderHeader_inmem 및 SalesOrderDetail_inmem 날짜/시간 열에 해당 값을 사용하여 문제를 해결합니다.
별칭 UDT - 원래 테이블은 PurchaseOrderNumber 및 AccountNumber 열에 대해 두 개의 별칭 사용자 정의 데이터 형식(UDT) dbo.OrderNumber 및 dbo.AccountNumber를 사용합니다. SQL Server 2014는 메모리 최적화 테이블에 대한 별칭 UDT를 지원하지 않으므로 새 테이블은 각각 nvarchar(25) 및 nvarchar(15) 시스템 데이터 형식을 사용합니다.
인덱스 키의 Null 허용 열 - 원래 테이블에서는 SalesPersonID 열이 null을 허용하지만, 새 테이블에서는 해당 열이 null을 허용하지 않고 값(-1)이 있는 기본 제약 조건을 가지고 있습니다. 메모리 최적화 테이블의 인덱스에는 인덱스 키에 null 허용 열이 있을 수 없기 때문입니다. 이 경우 -1 NULL의 서로게이트입니다.
계산 열 - SQL Server 2014는 메모리 최적화 테이블의 계산 열을 지원하지 않으므로 SalesOrderNumber 및 TotalDue 계산 열은 생략됩니다. 새 뷰 Sales.vSalesOrderHeader_extended_inmem은 SalesOrderNumber 및 TotalDue 열을 표시합니다. 따라서 이러한 열이 필요한 경우 이 보기를 사용할 수 있습니다.
SQL Server 2014의 메모리 최적화 테이블에는 외래 키 제약 조건이 지원되지 않습니다. 또한 SalesOrderHeader_inmem 예제 워크로드의 핫 테이블이며, 이러한 제약 조건에서 참조되는 다른 모든 테이블에서 조회가 필요하므로 외래 키 제약 조건에는 모든 DML 작업에 대한 추가 처리가 필요합니다. 따라서 앱이 참조 무결성을 보장하고 행이 삽입될 때 참조 무결성의 유효성을 검사하지 않는다고 가정합니다. 이 테이블의 데이터에 대한 참조 무결성은 다음 스크립트를 사용하여 저장 프로시저 dbo.usp_ValidateIntegrity 사용하여 확인할 수 있습니다.
DECLARE @o int = object_id(N'Sales.SalesOrderHeader_inmem') EXEC dbo.usp_ValidateIntegrity @oSQ Server 2014의 메모리 최적화 테이블에는 CHECK 제약 조건이 지원되지 않습니다. 도메인 무결성은 다음 스크립트를 사용하여 참조 무결성과 함께 유효성을 검사합니다.
DECLARE @o int = object_id(N'Sales.SalesOrderHeader_inmem') EXEC dbo.usp_ValidateIntegrity @oRowguid - rowguid 열이 생략되었습니다. uniqueidentifier는 메모리 최적화 테이블을 지원하지만 SQL Server 2014에서는 ROWGUIDCOL 옵션이 지원되지 않습니다. 이러한 종류의 열은 일반적으로 병합 복제 또는 파일 스트림 열이 있는 테이블에 사용됩니다. 이 예제에는 둘 다 포함되어 있지 않습니다.
판매.판매주문세부사항
기본 제약 조건 - SalesOrderHeader와 유사하게 시스템 날짜/시간이 필요한 기본 제약 조건은 마이그레이션되지 않고 판매 주문을 삽입하는 저장 프로시저는 첫 번째 삽입 시 현재 시스템 날짜/시간을 삽입하는 작업을 처리합니다.
계산 열 - 계산 열이 SQL Server 2014의 메모리 최적화 테이블에서 지원되지 않으므로 계산 열 LineTotal이 마이그레이션되지 않았습니다. 이 열에 액세스하려면 뷰 Sales.vSalesOrderDetail_extended_inmem 사용합니다.
Rowguid - rowguid 열이 생략되었습니다. 자세한 내용은 SalesOrderHeader 테이블에 대한 설명을 참조하세요.
검사 및 외래 키 제약 조건은 SalesOrderHeader에 대한 설명을 참조하세요. 다음 스크립트를 사용하여 이 테이블에 대한 도메인 및 참조 무결성을 확인할 수 있습니다.
DECLARE @o int = object_id(N'Sales.SalesOrderHeader_inmem') EXEC dbo.usp_ValidateIntegrity @o
생산.제품
별칭 UDT - 원래 테이블은 사용자 정의 데이터 형식 dbo.Flag를 사용합니다. 여기서 플래그는 시스템 데이터 형식 bit와 동일합니다. 마이그레이션된 테이블은 비트 데이터 형식을 대신 사용합니다.
BIN2 데이터 정렬 - 열 이름 및 ProductNumber는 인덱스 키에 포함되므로 SQL Server 2014에 BIN2 데이터 정렬이 있어야 합니다. 여기서는 앱이 대/소문자 구분과 같은 데이터 정렬 세부 사항에 의존하지 않는다고 가정합니다.
Rowguid - rowguid 열이 생략되었습니다. 자세한 내용은 SalesOrderHeader 테이블에 대한 설명을 참조하세요.
고유, Check 및 Foreign Key 제약 조건은 Product.usp_InsertProduct_inmem 저장 프로시저와 Product.usp_DeleteProduct_inmem 사용하여 제품을 삽입하고 삭제하는 두 가지 방법으로 고려됩니다. 이러한 절차는 도메인 및 참조 무결성의 유효성을 검사하며 무결성을 위반하면 실패합니다. 또한 다음 스크립트를 사용하여 도메인 및 참조 무결성의 유효성을 검사할 수 있습니다.
DECLARE @o int = object_id(N'Production.Product') EXEC dbo.usp_ValidateIntegrity @o- 참고로, 저장 프로시저 usp_InsertProduct_inmem 및 usp_DeleteProduct_inmem은 마이그레이션된 테이블 간의 외래 키만 고려합니다. ProductModel, ProductSubcategory 및 UnitMeasure와 같은 다른 테이블에 대한 참조는 고려되지 않습니다.
판매.특별제안
Check 및 Foreign Key 제약 조건은 두 가지 방법으로 고려됩니다. Sales.usp_InsertSpecialOffer_inmem 저장 프로시저와 Sales.usp_DeleteSpecialOffer_inmem 사용하여 특별 제품을 삽입하고 삭제할 수 있습니다. 이러한 절차는 도메인 및 참조 무결성의 유효성을 검사하며 무결성을 위반하면 실패합니다. 또한 다음 스크립트를 사용하여 도메인 및 참조 무결성의 유효성을 검사할 수 있습니다.
DECLARE @o int = object_id(N'Sales.SpecialOffer_inmem') EXEC dbo.usp_ValidateIntegrity @oRowguid - rowguid 열이 생략되었습니다. 자세한 내용은 SalesOrderHeader 테이블에 대한 설명을 참조하세요.
세일.특별혜택제품
외래 키 제약 조건은 두 가지 방법으로 고려됩니다. 저장 프로시저 Sales.usp_InsertSpecialOfferProduct_inmem 사용하여 특별 제품과 제품 간의 관계를 삽입할 수 있습니다. 이 절차는 참조 무결성의 유효성을 검사하며 무결성을 위반하면 실패합니다. 또한 다음 스크립트를 사용하여 참조 무결성의 유효성을 있는 그대로 확인할 수 있습니다.
DECLARE @o int = object_id(N'Sales.SpecialOfferProduct_inmem') EXEC dbo.usp_ValidateIntegrity @oRowguid - rowguid 열이 생략되었습니다. 자세한 내용은 SalesOrderHeader 테이블에 대한 설명을 참조하세요.
메모리 최적화 테이블의 인덱스에 대한 고려 사항
메모리 최적화 테이블의 기준 인덱스는 포인트 조회(같음 조건자에서의 인덱스 검색), 범위 검색(부등식 조건자에서의 인덱스 검색), 전체 인덱스 검색 및 순서대로 검색을 지원하는 NONCLUSTERED 인덱스입니다. 또한 NONCLUSTERED 인덱스는 인덱스 키의 선행 열에 대한 검색을 지원합니다. 실제로 메모리 최적화 비클러스터형 인덱스는 디스크 기반 비클러스터형 인덱스가 지원하는 모든 작업을 지원하며, 유일한 예외는 역방향 스캔입니다. 따라서 NONCLUSTERED 인덱스를 사용하는 것이 인덱스에 안전한 선택입니다.
HASH 인덱스를 사용하여 워크로드를 추가로 최적화할 수 있습니다. 특히 지점 조회 및 행 삽입에 최적화되어 있습니다. 그러나 범위 검색, 정렬된 검색 또는 선행 인덱스 키 열 검색을 지원하지 않는다는 점을 고려해야 합니다. 따라서 이러한 인덱스를 사용할 때는 주의해야 합니다. 또한 생성 시 bucket_count 지정해야 합니다. 일반적으로 인덱스 키 값의 1~2배로 설정해야 하지만 일반적으로 과대평가는 문제가 되지 않습니다.
인덱스 지침 및 올바른 bucket_count 선택 지침에 대한 자세한 내용은 온라인 설명서를 참조하세요.
마이그레이션된 테이블의 인덱스는 데모 판매 주문 처리 워크로드에 맞게 조정되었습니다. 워크로드는 Sales.SalesOrderHeader_inmem 및 Sales.SalesOrderDetail_inmem 테이블의 삽입 및 점 조회에 의존하며 테이블 Production.Product_inmem 및 Sales.SpecialOffer_inmem 기본 키 열에 대한 지점 조회도 사용합니다.
Sales.SalesOrderHeader_inmem 성능상의 이유로 모든 HASH 인덱스이며 워크로드에 대해 정렬되거나 범위 검사가 필요하지 않으므로 세 개의 인덱스가 있습니다.
SalesOrderID에 대한 HASH 인덱스: 예상 판매 주문 수는 1,000만 개이므로, bucket_count 크기는 1,000만 개로 설정되었으며, 이는 올림하여 최대 1,600만 개입니다.
HASH 인덱스(SalesPersonID): bucket_count 100만 개입니다. 제공된 데이터 세트에는 영업 사원이 많지 않지만 향후 성장을 가능하게 하며, bucket_count 크기가 큰 경우 포인트 조회에 대한 성능 저하를 지불하지 않습니다.
HASH 인덱스(CustomerID): 버킷 수는 100만 개입니다. 제공된 데이터 집합에는 많은 고객이 없지만 향후 성장을 허용합니다.
Sales.SalesOrderDetail_inmem에는 성능상의 이유로 세 개의 인덱스가 있으며, 모두 HASH 인덱스입니다. 이는 워크로드에 대해 정렬되거나 범위 검사가 필요하지 않기 때문입니다.
HASH 인덱스 (SalesOrderID, SalesOrderDetailID): 이것은 기본 키 인덱스이며, 해시 인덱스를 사용하면 키에 대한 조회는 드물긴 하지만 행 삽입 속도를 높일 수 있습니다. bucket_count 크기는 5천만 개(최대 6,700만 개)입니다. 예상 판매 주문 수는 1,000만 개이며 주문당 평균 5개 항목이 있는 크기입니다.
HASH 인덱스 on(SalesOrderID): 판매 주문별 조회가 빈번합니다. 단일 주문에 해당하는 모든 품목을 찾으려고 합니다. 예상된 판매 주문 수가 1,000만 개이기 때문에 bucket_count의 크기는 1,000만(1,600만으로 반올림됨)으로 설정됩니다.
HASH 인덱스 (ProductID 기준): bucket_count는 100만 개입니다. 제공된 데이터 집합에는 많은 제품이 없지만 이후 성장을 허용할 수 있습니다.
Production.Product_inmem 인덱스가 세 개 있습니다.
HASH 인덱스 on(ProductID): ProductID에 대한 조회는 데모 워크로드에 대한 중요한 경로에 있으므로 해시 인덱스입니다.
비클러스터형 인덱스(이름): 제품 이름의 순서가 지정된 검사를 허용합니다.
비클러스터형 인덱스(ProductNumber): 제품 번호의 정렬된 검사를 허용할 수 있습니다.
Sales.SpecialOffer_inmem에는 SpecialOfferID에 대한 하나의 HASH 인덱스가 있습니다. 특별 할인 검색은 데모 워크로드의 핵심 부분입니다. 이 bucket_count 향후 성장을 위해 100만 개의 크기로 조정됩니다.
Sales.SpecialOfferProduct_inmem 데모 워크로드에서 참조되지 않으므로 워크로드를 최적화하기 위해 이 테이블의 해시 인덱스를 사용할 필요가 없습니다. (SpecialOfferID, ProductID) 및 (ProductID)의 인덱스는 비클러스터형입니다.
위의 일부 bucket_counts는 과도하게 크기가 조정되었지만, SalesOrderHeader_inmem과 SalesOrderDetail_inmem 인덱스에 대한 bucket_counts는 그렇지 않습니다. 이들은 단지 1,000만 개의 판매 주문에 맞춰 크기가 조정되어 있습니다. 이는 사용할 수 있는 메모리가 적은 시스템에 예제를 설치할 수 있도록 하기 위해서입니다. 하지만 이러한 경우에 데모 작업은 메모리 부족으로 실패합니다. 1,000만 개 이상의 판매 주문을 초과하여 확장하려면 버킷 수를 적절하게 늘릴 수 있습니다.
메모리 사용률 고려 사항
데모 워크로드를 실행하기 전과 후에 샘플 데이터베이스의 메모리 사용률은 메모리 최적화 테이블에 대한 메모리 사용률 센션에서 설명합니다.
예제에서 추가된 저장 프로시저
판매 주문을 삽입하고 배송 세부 정보를 업데이트하기 위한 두 가지 주요 저장 프로시저는 다음과 같습니다.
Sales.usp_InsertSalesOrder_inmem
데이터베이스에 새 판매 주문을 삽입하고 해당 판매 주문에 대한 SalesOrderID를 출력합니다. 입력 매개변수에는 판매 주문의 헤더 정보뿐만 아니라 주문 내 라인 항목의 세부 정보도 포함됩니다.
출력 매개 변수:
- @SalesOrderID int - 방금 삽입된 판매 주문의 SalesOrderID
입력 매개 변수(필수):
@DueDate datetime2
@CustomerID 정수형
@BillToAddressID [int]
@ShipToAddressID [int]
@ShipMethodID [int]
@SalesOrderDetails Sales.SalesOrderDetailType_inmem - 주문의 각 항목이 포함된 TVP
입력 매개 변수(선택적):
@Status [tinyint]
@OnlineOrderFlag [비트]
@PurchaseOrderNumber [nvarchar](25)
@AccountNumber [nvarchar](15)
@SalesPersonID [int]
@TerritoryID [int]
@CreditCardID [int]
@CreditCardApprovalCode [varchar](15)
@CurrencyRateID [int]
@Comment nvarchar(128)
Sales.usp_UpdateSalesOrderShipInfo_inmem
지정된 판매 주문에 대한 배송 정보를 업데이트합니다. 그러면 판매 주문의 모든 품목에 대한 배송 정보도 업데이트됩니다.
이는 Sales.usp_UpdateSalesOrderShipInfo_native 네이티브 컴파일된 저장 프로시저의 래퍼 프로시저로, 동일한 주문을 업데이트하는 동시 트랜잭션과의 (예기치 못한) 잠재적 충돌을 처리하기 위한 재시도 논리를 포함하고 있습니다. 재시도 논리에 대한 자세한 내용은 여기의 온라인 설명서 항목을 참조 하세요.
Sales.usp_UpdateSalesOrderShipInfo_native
- 배송 정보에 대한 업데이트를 실제로 처리하는 고유하게 컴파일된 저장 프로시저입니다. 이는 래퍼 저장 프로시저 Sales.usp_UpdateSalesOrderShipInfo_inmem을 호출하도록 의도된 것입니다. 클라이언트가 오류를 처리하고 재시도 논리를 구현하는 경우 래퍼 저장 프로시저를 사용하는 대신 이 프로시저를 직접 호출할 수 있습니다.
다음의 저장 프로시저는 데모 워크로드에 사용됩니다.
Demo.usp_DemoReset
- SalesOrderHeader 및 SalesOrderDetail 테이블을 비우고 다시 설정하여 데모를 다시 설정합니다.
다음 저장 프로시저는 도메인 및 참조 무결성을 보장하면서 메모리 최적화 테이블에서 삽입 및 삭제하는 데 사용됩니다.
Production.usp_InsertProduct_inmem
Production.usp_DeleteProduct_inmem
Sales.usp_InsertSpecialOffer_inmem
Sales.usp_DeleteSpecialOffer_inmem
Sales.usp_InsertSpecialOfferProduct_inmem
마지막으로 다음 저장 프로시저를 사용하여 도메인 및 참조 무결성을 확인합니다.
dbo.usp_ValidateIntegrity
선택적 매개 변수: @object_id – 무결성을 확인할 개체의 ID
이 프로시저는 무결성 규칙을 확인하기 위해 dbo.DomainIntegrity, dbo.ReferentialIntegrity 및 dbo.UniqueIntegrity 테이블에 의존합니다. AdventureWorks 데이터베이스의 원래 테이블에 있는 검사, 외래 키, 고유 제약 조건을 기반으로 이 샘플은 이러한 테이블을 채웁니다.
이 절차는 dbo.usp_GenerateCKCheck, dbo.usp_GenerateFKCheck, dbo.GenerateUQCheck 도우미 절차에 의존하여 무결성 검사를 수행하는 데 필요한 T-SQL을 생성합니다.
데모 작업을 사용한 성능 측정
Ostress는 Microsoft CSS SQL Server 지원 팀에서 개발한 명령줄 도구입니다. 이 도구를 사용하여 쿼리를 실행하거나 저장 프로시저를 병렬로 실행할 수 있습니다. 지정된 T-SQL 문을 병렬로 실행하도록 스레드 수를 구성할 수 있으며 이 스레드에서 문을 실행할 횟수를 지정할 수 있습니다. ostress는 스레드를 스핀업하고 모든 스레드에서 문을 병렬로 실행합니다. 모든 스레드의 실행이 완료된 후 ostress는 모든 스레드의 실행이 완료되는 데 걸린 시간을 보고합니다.
ostress 설치
Ostress는 RML 유틸리티의 일부로 설치됩니다. ostress에 대한 독립 실행형 설치가 없습니다.
설치 단계:
다음 페이지에서 RML 유틸리티에 대한 x64 설치 패키지를 다운로드하고 실행합니다. https://blogs.msdn.com/b/psssql/archive/2013/10/29/cumulative-update-2-to-the-rml-utilities-for-microsoft-sql-server-released.aspx
특정 파일이 사용 중이라는 대화 상자가 있는 경우 '계속'을 클릭합니다.
ostress 실행
Ostress는 명령줄 프롬프트에서 실행됩니다. RML 유틸리티의 일부로 설치된 "RML Cmd 프롬프트"에서 도구를 실행하는 것이 가장 편리합니다.
RML Cmd 프롬프트를 열려면 다음 지침을 따릅니다.
Windows Server 2012 [R2]와 Windows 8 및 8.1에서 Windows 키를 클릭하여 시작 메뉴를 열고 'rml'을 입력합니다. 검색 결과 목록에 있는 "RML Cmd 프롬프트"를 클릭합니다.
명령 프롬프트가 RML 유틸리티 설치 폴더에 있는지 확인합니다. 다음은 그 예입니다.
명령줄 옵션 없이 단순히 ostress.exe를 실행할 때 ostress에 대한 명령줄 옵션을 볼 수 있습니다. 이 예제와 함께 ostress를 실행하기 위해 고려할 기본 옵션은 다음과 같습니다.
연결할 MicrosoftSQL 서버 인스턴스의 -S 이름
-E Windows 인증을 사용하여 연결(기본값). SQL Server 인증을 사용하는 경우 –U 및 –P 옵션을 사용하여 사용자 이름과 비밀번호를 각각 지정합니다.
데이터베이스의 -d 이름(이 예제 AdventureWorks2014)입니다.
-Q 실행할 T-SQL 문
-n 각 입력 파일/쿼리를 처리하는 연결 수
-r 각 입력 파일/쿼리를 실행할 각 연결에 대한 반복 수입니다.
데모 워크로드
데모 워크로드에 사용되는 기본 저장 프로시저는 Sales.usp_InsertSalesOrder_inmem/ondisk입니다. 아래의 스크립트는 샘플 데이터를 사용하여 TVP(테이블 반환 매개 변수)를 생성하고 5개의 품목이 있는 판매 주문을 삽입하는 절차를 호출합니다.
ostress 도구는 판매 주문을 동시에 삽입하는 클라이언트를 시뮬레이션하기 위해 저장 프로시저 호출을 병렬로 실행하는 데 사용됩니다.
각 스트레스 실행 후 Demo.usp_DemoReset을 실행하여 데모를 다시 설정합니다. 이 절차는 메모리 최적화 테이블의 행을 삭제하고 디스크 기반 테이블을 잘라내고 데이터베이스 검사점을 실행합니다.
다음 스크립트는 동시에 실행되어 판매 주문 처리 워크로드를 시뮬레이션합니다.
DECLARE
@i int = 0,
@od Sales.SalesOrderDetailType_inmem,
@SalesOrderID int,
@DueDate datetime2 = sysdatetime(),
@CustomerID int = rand() * 8000,
@BillToAddressID int = rand() * 10000,
@ShipToAddressID int = rand() * 10000,
@ShipMethodID int = (rand() * 5) + 1;
INSERT INTO @od
SELECT OrderQty, ProductID, SpecialOfferID
FROM Demo.DemoSalesOrderDetailSeed
WHERE OrderID= cast((rand()*106) + 1 as int);
WHILE (@i < 20)
BEGIN;
EXEC Sales.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @ShipMethodID, @od;
SET @i += 1
END
이 스크립트를 사용하면 생성된 각 샘플 순서가 WHILE 루프에서 실행되는 20개의 저장 프로시저를 통해 20번 삽입됩니다. 루프는 데이터베이스가 샘플 순서를 생성하는 데 사용된다는 사실을 고려하는 데 사용됩니다. 일반적인 프로덕션 환경에서 중간 계층 애플리케이션은 삽입할 판매 주문을 생성합니다.
위의 스크립트는 메모리 최적화 테이블에 판매 주문을 삽입합니다. 판매 주문을 디스크 기반 테이블에 삽입하는 스크립트는 두 ‘_inmem’을 ‘_ondisk’로 바꿔서 파생됩니다.
여러 동시 연결을 사용하여 스크립트를 실행하기 위해 ostress 도구를 사용하려고 합니다. '-n' 매개 변수를 사용하여 연결 수를 제어하고 'r' 매개 변수를 사용하여 스크립트가 각 연결에서 실행되는 횟수를 제어하려고 합니다.
워크로드의 기능 유효성 검사
모든 것이 작동하는지 확인하기 위해 10개의 동시 연결과 5개의 반복을 사용하여 샘플 테스트로 시작하여 총 10 * 5 * 20 = 1000 판매 주문을 삽입합니다.
아래 명령을 사용하면 로컬 컴퓨터에서 기본 인스턴스를 사용하는 것으로 가정합니다. 명명된 인스턴스를 사용하거나 원격 서버를 사용하는 경우 매개 변수 -S를 사용하여 서버 이름을 적절하게 변경합니다.
메모리 최적화 테이블에 1000개의 판매 주문을 삽입하면 RML Cmd 프롬프트에서 다음 명령을 사용합니다.
복사 단추를 클릭하여 명령을 복사하고 RML 유틸리티 명령 프롬프트에 붙여넣습니다.
ostress.exe -n10 -r5 -S. -E -dAdventureWorks2014 -q -Q"DECLARE @i int = 0, @od Sales.SalesOrderDetailType_inmem, @SalesOrderID int, @DueDate datetime2 = sysdatetime(), @CustomerID int = rand() * 8000, @BillToAddressID int = rand() * 10000, @ShipToAddressID int = rand() * 10000, @ShipMethodID int = (rand() * 5) + 1; INSERT INTO @od SELECT OrderQty, ProductID, SpecialOfferID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID= cast((rand()*106) + 1 as int); while (@i < 20) begin; EXEC Sales.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @ShipMethodID, @od; set @i += 1 end"
모든 것이 예상대로 작동하면 명령 창이 다음과 유사하게 표시됩니다. 오류 메시지가 예상되지 않습니다.
RML Cmd 프롬프트에서 다음 명령을 실행하여 워크로드가 디스크 기반 테이블에 대해 예상대로 작동하는지 확인합니다.
복사 단추를 클릭하여 명령을 복사하고 RML 유틸리티 명령 프롬프트에 붙여넣습니다.
ostress.exe -n10 -r5 -S. -E -dAdventureWorks2014 -q -Q"DECLARE @i int = 0, @od Sales.SalesOrderDetailType_ondisk, @SalesOrderID int, @DueDate datetime2 = sysdatetime(), @CustomerID int = rand() * 8000, @BillToAddressID int = rand() * 10000, @ShipToAddressID int = rand() * 10000, @ShipMethodID int = (rand() * 5) + 1; INSERT INTO @od SELECT OrderQty, ProductID, SpecialOfferID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID= cast((rand()*106) + 1 as int); while (@i < 20) begin; EXEC Sales.usp_InsertSalesOrder_ondisk @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @ShipMethodID, @od; set @i += 1 end"
워크로드 실행
대규모로 테스트하기 위해 100개의 연결을 사용하여 1,000만 개의 판매 주문을 삽입합니다. 이 테스트는 로그에 대한 적절한 서버(예: 물리적 코어 8개, 논리 코어 16개) 및 기본 SSD 스토리지에서 합리적으로 수행됩니다. 테스트가 하드웨어에서 잘 수행되지 않는 경우 느리게 실행되는 테스트 문제 해결 섹션을 살펴보세요. 이 테스트의 스트레스 수준을 줄이려면 '-n' 매개 변수를 변경하여 연결 수를 줄입니다. 예를 들어 연결 수를 40으로 낮추려면 '-n100' 매개 변수를 '-n40'으로 변경합니다.
작업에 대한 성능 측정으로 작업을 실행한 후 ostress.exe에서 보고하는 경과 시간을 사용합니다.
메모리 최적화 테이블
메모리 최적화 테이블에서 작업을 실행하는 것부터 시작합니다. 다음 명령은 각각 5,000회의 반복을 위해 실행되는 100개의 스레드를 엽니다. 각 반복은 별도의 트랜잭션에 20개의 판매 주문을 삽입합니다. 데이터베이스가 삽입될 데이터를 생성하는 데 사용된다는 사실을 보완하기 위해 반복당 20개의 삽입이 있습니다. 이에 따라 총 20 * 5,000 * 100 = 10,000,000개의 판매 주문 삽입이 생성됩니다.
RML Cmd 프롬프트를 열고 다음 명령을 실행합니다.
복사 단추를 클릭하여 명령을 복사하고 RML 유틸리티 명령 프롬프트에 붙여넣습니다.
ostress.exe -n100 -r5000 -S. -E -dAdventureWorks2014 -q -Q"DECLARE @i int = 0, @od Sales.SalesOrderDetailType_inmem, @SalesOrderID int, @DueDate datetime2 = sysdatetime(), @CustomerID int = rand() * 8000, @BillToAddressID int = rand() * 10000, @ShipToAddressID int = rand() * 10000, @ShipMethodID int = (rand() * 5) + 1; INSERT INTO @od SELECT OrderQty, ProductID, SpecialOfferID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID= cast((rand()*106) + 1 as int); while (@i < 20) begin; EXEC Sales.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @ShipMethodID, @od; set @i += 1 end"
물리적 코어가 총 8개(논리적 코어 총 16개)인 테스트 서버에서 이 작업은 2분 5초가 소요되었습니다. 물리적 코어가 24개(논리적 코어 48개)인 두 번째 테스트 서버에서는 1분 0초가 소요되었습니다.
작업 관리자 등을 사용하여 워크로드가 실행되는 동안 CPU 사용률을 관찰합니다. CPU 사용률이 100%에 가까운 것을 볼 수 있습니다. 이 경우가 아니라면, 로그 IO 병목 현상이 있으니 느린 실행 테스트 문제 해결도 참조하십시오.
디스크 기반 테이블
다음 명령은 디스크 기반 테이블에서 워크로드를 실행합니다. 이 워크로드를 실행하는 데 시간이 걸릴 수 있습니다. 이는 주로 시스템의 래치 경합 때문입니다. 메모리 최적화 테이블은 래치가 없으므로 이 문제를 겪지 않습니다.
RML Cmd 프롬프트를 열고 다음 명령을 실행합니다.
복사 단추를 클릭하여 명령을 복사하고 RML 유틸리티 명령 프롬프트에 붙여넣습니다.
ostress.exe -n100 -r5000 -S. -E -dAdventureWorks2014 -q -Q"DECLARE @i int = 0, @od Sales.SalesOrderDetailType_ondisk, @SalesOrderID int, @DueDate datetime2 = sysdatetime(), @CustomerID int = rand() * 8000, @BillToAddressID int = rand() * 10000, @ShipToAddressID int = rand() * 10000, @ShipMethodID int = (rand() * 5) + 1; INSERT INTO @od SELECT OrderQty, ProductID, SpecialOfferID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID= cast((rand()*106) + 1 as int); while (@i < 20) begin; EXEC Sales.usp_InsertSalesOrder_ondisk @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @ShipMethodID, @od; set @i += 1 end"
물리적 코어가 총 8개(논리적 코어 총 16개)인 테스트 서버에서 이 작업은 41분 25초가 소요되었고, 물리적 코어가 24개(논리적 코어 48개)인 두 번째 테스트 서버에서는 52분 16초가 소요되었습니다.
이 테스트에서 메모리 최적화 테이블과 디스크 기반 테이블 간의 성능 차이의 주요 요인은 디스크 기반 테이블을 사용할 때 SQL Server가 CPU를 완전히 활용할 수 없다는 사실입니다. 그 이유는 래치 경합입니다. 동시 트랜잭션이 동일한 데이터 페이지에 쓰려고 합니다. 래치는 한 번에 하나의 트랜잭션만 페이지에 쓸 수 있도록 하는 데 사용됩니다. 메모리 내 OLTP 엔진은 래치를 사용하지 않으며 데이터 행이 페이지에 구성되지 않습니다. 따라서 동시 트랜잭션이 서로의 삽입을 차단하지 않기 때문에 SQL Server 에서 CPU를 완전히 활용할 수 있습니다.
작업 관리자 등을 사용하여 작업이 실행되는 동안 CPU 사용률을 관찰할 수 있습니다. 디스크 기반 테이블에서 CPU 사용률이 100%와는 거리가 먼 것을 볼 수 있습니다. 논리적 프로세서가 16개인 테스트 구성에서 사용률은 24% 정도입니다.
필요에 따라 성능 모니터를 사용하여 성능 카운터 '\SQL Server:Latches\Latch Waits/sec'를 사용하여 초당 래치 대기 수를 볼 수 있습니다.
데모 초기화
데모를 초기화하려면 RML Cmd 프롬프트를 열고 다음 명령을 실행합니다.
ostress.exe -S. -E -dAdventureWorks2014 -Q"EXEC Demo.usp_DemoReset"
하드웨어에 따라 실행하는 데 몇 분 정도 걸릴 수 있습니다.
데모 실행이 끝날 때마다 다시 설정하는 것이 좋습니다. 이 워크로드는 삽입 전용이므로 각 실행은 더 많은 메모리를 소비하며, 이에 따라 메모리 부족이 발생하지 않도록 다시 설정해야 합니다. 실행 후 사용된 메모리 양은 워크로드를 실행한 후 Memory 사용률 섹션에서 설명합니다.
느리게 실행되는 테스트 문제 해결
테스트 결과는 일반적으로 하드웨어와 테스트 실행에 사용되는 동시성 수준에 따라 달라집니다. 결과가 예상대로 되지 않는 경우 몇 가지 사항을 알아봐야 합니다.
동시 트랜잭션 수: 단일 스레드에서 작업을 실행할 때 메모리 내 OLTP를 사용한 성능 이점은 두 배보다 적을 수 있습니다. 래치 경합은 높은 수준의 동시성이 있는 경우에만 큰 문제입니다.
SQL Server에서 사용할 수 있는 코어 수가 적습니다. 즉, SQL에서 사용할 수 있는 코어 수만큼 동시 실행 트랜잭션만 있을 수 있으므로 시스템에 낮은 수준의 동시성이 있습니다.
- 증상: 디스크 기반 테이블에서 워크로드를 실행할 때 CPU 사용률이 높은 경우 동시성 부족을 가리키는 경합이 많지 않음을 의미합니다.
로그 드라이브의 속도: 로그 드라이브가 시스템의 트랜잭션 처리량 수준을 따라갈 수 없는 경우 워크로드는 로그 IO에서 병목 상태가 됩니다. 로깅은 메모리 내 OLTP를 사용하는 것이 더 효율적이지만 로그 IO가 병목 상태인 경우 잠재적인 성능 향상은 제한됩니다.
- 증상: 메모리 최적화 테이블에서 워크로드를 실행할 때 CPU 사용률이 100%에 가깝지 않거나 매우 급격히 증가하는 경우 로그 IO 병목 현상이 발생할 수 있습니다. 리소스 모니터를 열고 로그 드라이브의 큐 길이를 보고 확인할 수 있습니다.
샘플의 메모리 및 디스크 공간 사용률
아래에서는 샘플 데이터베이스에 대한 메모리 및 디스크 공간 사용률 측면에서 예상되는 사항에 대해 설명합니다. 또한 16개의 논리 코어가 있는 테스트 서버에서 본 결과도 보여 줍니다.
메모리 최적화 테이블의 메모리 사용률
데이터베이스의 전체 사용률
다음 쿼리를 사용하여 시스템에서 메모리 내 OLTP 의 총 메모리 사용률을 얻을 수 있습니다.
SELECT type
, name
, pages_kb/1024 AS pages_MB
FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%'
데이터베이스를 방금 만든 후의 스냅샷:
| 유형 | 이름 | pages_MB |
| MEMORYCLERK_XTP | 기본값 | 94 |
| MEMORYCLERK_XTP | DB_ID_5 | 877 |
| MEMORYCLERK_XTP | 기본값 | 0 |
| MEMORYCLERK_XTP | 기본값 | 0 |
기본 메모리 클럭은 시스템 차원의 메모리 구조를 포함하고 있으며 비교적 작습니다. 사용자 데이터베이스의 메모리 클럭(이 경우 ID가 5인 데이터베이스)은 약 900MB입니다.
테이블 당 메모리 사용률
다음 쿼리를 사용하여 개별 테이블 및 해당 인덱스의 메모리 사용률을 드릴다운할 수 있습니다.
SELECT object_name(t.object_id) AS [Table Name]
, memory_allocated_for_table_kb
, memory_allocated_for_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats dms JOIN sys.tables t
ON dms.object_id=t.object_id
WHERE t.type='U'
다음은 샘플의 새로 설치에 대한 이 쿼리의 결과를 보여줍니다.
| 테이블 이름 | 테이블에 할당된 메모리_kb | 인덱스를 위한 메모리 할당 (KB) |
| 특별 할인 상품_inmem | 64 | 3840 |
| 데모판매주문헤더시드 | 1984 | 5,504 |
| SalesOrderDetail_inmem | 15316 | 663552 |
| DemoSalesOrderDetailSeed (데모 판매 주문 세부정보 시드) | 64 | 10432 |
| 특별제공_inmem | 3 | 8192 |
| SalesOrderHeader_inmem | 7168 | 147456 |
| Product_inmem | 124 | 12352 |
테이블은 상당히 작습니다. SalesOrderHeader_inmem 약 7MB이고 SalesOrderDetail_inmem 크기는 약 15MB입니다.
여기서 눈에 띄는 점은 테이블 데이터의 크기와 비교하여 인덱스에 할당된 메모리의 크기입니다. 이는 샘플의 해시 인덱스가 더 큰 데이터 크기에 대해 미리 크기가 조정되기 때문입니다. 해시 인덱스의 크기는 고정되어 있으므로 해당 크기는 테이블의 데이터 크기에 따라 증가하지 않습니다.
워크로드를 실행한 후 메모리 사용률
1,000만 개의 판매 주문을 삽입한 후 전체 메모리 사용률은 다음과 유사합니다.
SELECT type
, name
, pages_kb/1024 AS pages_MB
FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%'
| 유형 | 이름 | pages_MB |
| MEMORYCLERK_XTP | 기본값 | 146 |
| MEMORYCLERK_XTP | DB_ID_5 | 7374 |
| MEMORYCLERK_XTP | 기본값 | 0 |
| MEMORYCLERK_XTP | 기본값 | 0 |
보듯이 SQL Server는 샘플 데이터베이스의 메모리 최적화 테이블 및 인덱스에 대해 8GB 미만의 비트를 사용하고 있습니다.
다음 예제를 실행한 후 테이블당 자세한 메모리 사용량을 확인합니다.
SELECT object_name(t.object_id) AS [Table Name]
, memory_allocated_for_table_kb
, memory_allocated_for_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats dms JOIN sys.tables t
ON dms.object_id=t.object_id
WHERE t.type='U'
| 테이블 이름 | 테이블에 할당된 메모리(kb) | 인덱스용 할당된 메모리_kb |
| SalesOrderDetail_inmem | 5113761 | 663552 |
| DemoSalesOrderDetailSeed (데모 판매 주문 세부정보 시드) | 64 | 10368 |
| 스페셜오퍼_inmem | 2 | 8192 |
| 판매주문헤더_inmem | 1575679 | 147456 |
| Product_inmem | 111 | 12032 |
| 특별 할인 상품_inmem | 64 | 3712 |
| 데모판매주문헤더시드 | 1984 | 5,504 |
총 6.5GB의 데이터를 볼 수 있습니다. 테이블 SalesOrderHeader_inmem 및 SalesOrderDetail_inmem 인덱스의 크기는 판매 주문을 삽입하기 전에 인덱스의 크기와 동일합니다. 두 테이블 모두 해시 인덱스를 사용하고 해시 인덱스가 정적이므로 인덱스 크기가 변경되지 않았습니다.
데모 초기화 후
저장 프로시저 Demo.usp_DemoReset 데모를 다시 설정하는 데 사용할 수 있습니다. SalesOrderHeader_inmem 및 SalesOrderDetail_inmem 테이블의 데이터를 삭제하고, 원래 테이블인 SalesOrderHeader 및 SalesOrderDetail에서 데이터를 다시 시드합니다.
테이블의 행이 삭제되었더라도 메모리가 즉시 회수되지는 않습니다. SQL Server는 필요에 따라 백그라운드에서 메모리 최적화 테이블의 삭제된 행에서 메모리를 회수합니다. 데모가 다시 설정된 직후에는 시스템에 트랜잭션 작업이 없으므로 삭제된 행의 메모리가 아직 회수되지 않은 것을 확인할 수 있습니다.
SELECT type
, name
, pages_kb/1024 AS pages_MB
FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%'
| 유형 | 이름 | pages_MB |
| MEMORYCLERK_XTP | 기본값 | 2261 |
| MEMORYCLERK_XTP | DB_ID_5 | 7396 |
| MEMORYCLERK_XTP | 기본값 | 0 |
| MEMORYCLERK_XTP | 기본값 | 0 |
트랜잭션 워크로드가 실행 중일 때 메모리가 회수될 것으로 예상됩니다.
데모 워크로드의 두 번째 실행을 시작하면 이전에 삭제된 행이 정리되므로 처음에 메모리 사용률이 감소하는 것을 볼 수 있습니다. 워크로드가 완료될 때까지 메모리 크기가 다시 증가합니다. 데모 재설정 후 1,000만 개의 행을 삽입한 후 메모리 사용률은 첫 번째 실행 후의 사용률과 매우 유사합니다. 다음은 그 예입니다.
SELECT type
, name
, pages_kb/1024 AS pages_MB
FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%'
| 유형 | 이름 | pages_MB |
| MEMORYCLERK_XTP | 기본값 | 1863 |
| MEMORYCLERK_XTP | DB_ID_5 | 7390 |
| MEMORYCLERK_XTP | 기본값 | 0 |
| MEMORYCLERK_XTP | 기본값 | 0 |
메모리 최적화 테이블의 디스크 사용률
지정된 시간에 데이터베이스의 검사점 파일에 대한 전체 디스크 크기는 쿼리를 사용하여 찾을 수 있습니다.
SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]
FROM sys.filegroups f JOIN sys.database_files df
ON f.data_space_id=df.data_space_id
WHERE f.type=N'FX'
초기 상태
샘플 파일 그룹 및 샘플 메모리 최적화 테이블이 처음에 만들어지면 많은 검사점 파일이 미리 만들어지고 시스템이 파일 채우기를 시작합니다. 미리 생성된 검사점 파일 수는 시스템의 논리 프로세서 수에 따라 달라집니다. 샘플은 처음에는 매우 작기 때문에 미리 만든 파일은 초기 생성 후 대부분 비어 있습니다.
다음은 논리 프로세서가 16개인 컴퓨터의 샘플에 대한 초기 디스크 크기입니다.
SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]
FROM sys.filegroups f JOIN sys.database_files df
ON f.data_space_id=df.data_space_id
WHERE f.type=N'FX'
| 디스크 크기(MB) |
| 2312 |
볼 수 있듯이 검사점 파일의 디스크 크기(2.3GB)와 30MB에 가까운 실제 데이터 크기 사이에는 큰 차이가 있습니다.
디스크 공간 사용률의 위치를 자세히 살펴보면 다음 쿼리를 사용할 수 있습니다. 이 쿼리에서 반환된 디스크의 크기는 상태가 5(BACKUP/HA에 필요), 6(TOMBSTONE으로 전환) 또는 7(TOMBSTONE)인 파일의 대략적인 크기입니다.
SELECT state_desc
, file_type_desc
, COUNT(*) AS [count]
, SUM(CASE
WHEN state = 5 AND file_type=0 THEN 128*1024*1024
WHEN state = 5 AND file_type=1 THEN 8*1024*1024
WHEN state IN (6,7) THEN 68*1024*1024
ELSE file_size_in_bytes
END) / 1024 / 1024 AS [on-disk size MB]
FROM sys.dm_db_xtp_checkpoint_files
GROUP BY state, state_desc, file_type, file_type_desc
ORDER BY state, file_type
샘플의 초기 상태의 경우 결과는 논리 프로세서가 16개인 서버에 대해 다음과 같이 표시됩니다.
| state_desc | 파일 유형 설명 | 개수 | 디스크 크기 MB |
| 미리 생성된 | 데이터 | 16 | 2048 |
| 미리 생성된 | 델타 | 16 | 128 |
| 공사 중 | 데이터 | 1 | 128 |
| 공사 중 | 델타 | 1 | 8 (여덟) |
보다시피 대부분의 공간은 미리 생성된 데이터 및 델타 파일에서 사용됩니다. SQL Server는 논리 프로세서당 한 쌍의(데이터, 델타) 파일을 미리 만들었습니다. 또한 데이터 파일의 크기는 128MB, 델타 파일은 8MB로 미리 크기가 지정되어 이러한 파일에 데이터를 더 효율적으로 삽입할 수 있습니다.
메모리 최적화 테이블의 실제 데이터는 단일 데이터 파일에 있습니다.
작업을 실행한 후
1,000만 개의 판매 주문을 삽입하는 단일 테스트 실행 후 전체 디스크 크기는 다음과 같습니다(16코어 테스트 서버의 경우).
SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]
FROM sys.filegroups f JOIN sys.database_files df
ON f.data_space_id=df.data_space_id
WHERE f.type=N'FX'
| 디스크 크기(MB) |
| 8828 |
디스크의 크기는 9GB에 가깝고 데이터의 메모리 내 크기에 가깝습니다.
다양한 상태에서 검사점 파일의 크기를 더 자세히 살펴봅니다.
SELECT state_desc
, file_type_desc
, COUNT(*) AS [count]
, SUM(CASE
WHEN state = 5 AND file_type=0 THEN 128*1024*1024
WHEN state = 5 AND file_type=1 THEN 8*1024*1024
WHEN state IN (6,7) THEN 68*1024*1024
ELSE file_size_in_bytes
END) / 1024 / 1024 AS [on-disk size MB]
FROM sys.dm_db_xtp_checkpoint_files
GROUP BY state, state_desc, file_type, file_type_desc
ORDER BY state, file_type
| state_desc | file_type_desc | 개수 | 디스크 크기 MB |
| 미리 생성된 | 데이터 | 16 | 2048 |
| 미리 생성된 | 델타 | 16 | 128 |
| 공사 중 | 데이터 | 1 | 128 |
| 공사 중 | 델타 | 1 | 8 (여덟) |
검사점이 닫히면 사용할 준비가 된 16쌍의 미리 만들어진 파일이 있습니다.
현재 검사점이 닫힐 때까지 사용되는 개발 중인 쌍이 하나 있습니다. 활성 검사점 파일과 함께 메모리 내 데이터의 6.5GB에 대해 약 6.5GB의 디스크 사용률을 제공합니다. 인덱스는 디스크에 유지되지 않으므로 디스크의 전체 크기가 이 경우 메모리 크기보다 작습니다.
데모 초기화 후
데모 초기화 후 시스템에 트랜잭션 워크로드가 없고 데이터베이스 검사점이 없는 경우 디스크 공간이 즉시 회수되지 않습니다. 검사점 파일을 다양한 단계를 통해 이동하고 결국 삭제하려면 검사점 파일 병합을 시작하고 가비지 수집을 시작하려면 여러 검사점 및 로그 잘림 이벤트가 발생해야 합니다. 이러한 작업은 시스템에 트랜잭션 워크로드가 있는 경우 자동으로 발생하지만 데모 시나리오에서처럼 시스템이 유휴 상태일 때는 그렇지 않고 일반 로그 백업을 수행합니다.
이 예제에서는 데모를 다시 설정한 후 다음과 같이 표시할 수 있습니다.
SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]
FROM sys.filegroups f JOIN sys.database_files df
ON f.data_space_id=df.data_space_id
WHERE f.type=N'FX'
| 디스크 크기(MB) |
| 11839 |
거의 12GB에서, 이것은 데모 재설정 전에 우리가 가진 9GB보다 훨씬 더 많은 것입니다. 이는 일부 검사점 파일 병합이 시작되었지만 일부 병합 대상은 아직 설치되지 않았으며 병합 원본 파일 중 일부는 아직 정리되지 않았기 때문입니다.
SELECT state_desc
, file_type_desc
, COUNT(*) AS [count]
, SUM(CASE
WHEN state = 5 AND file_type=0 THEN 128*1024*1024
WHEN state = 5 AND file_type=1 THEN 8*1024*1024
WHEN state IN (6,7) THEN 68*1024*1024
ELSE file_size_in_bytes
END) / 1024 / 1024 AS [on-disk size MB]
FROM sys.dm_db_xtp_checkpoint_files
GROUP BY state, state_desc, file_type, file_type_desc
ORDER BY state, file_type
| state_desc | 파일 유형 설명 | 개수 | 디스크 크기 MB |
| 미리 생성된 | 데이터 | 16 | 2048 |
| 미리 생성된 | 델타 | 16 | 128 |
| 활성 | 데이터 | 38 | 5152 |
| 활성 | 델타 | 38 | 1331 |
| 병합 대상 | 데이터 | 7 | 896 |
| 병합 대상 | 델타 | 7 | 56 |
| 병합된 소스 | 데이터 | 13 | 1,772 |
| 병합된 소스 | 델타 | 13 | 455 |
병합 대상이 설치되고 시스템에서 트랜잭션 작업이 수행되면 병합된 원본이 정리됩니다.
데모 워크로드를 두 번째로 실행한 후 데모 재설정 후 1,000만 개의 판매 주문을 삽입하면 워크로드의 첫 번째 실행 중에 생성된 파일이 정리된 것을 볼 수 있습니다. 작업이 실행되는 동안 위의 쿼리를 몇 차례 실행하는 경우 검사점 파일이 다양한 상태를 거치는 것을 확인할 수 있습니다.
워크로드의 두 번째 실행 후 1,000만 개의 판매 주문을 삽입하면 시스템이 동적이므로 첫 번째 실행 후와 동일하지는 않지만 디스크 사용률이 매우 유사합니다. 다음은 그 예입니다.
SELECT state_desc
, file_type_desc
, COUNT(*) AS [count]
, SUM(CASE
WHEN state = 5 AND file_type=0 THEN 128*1024*1024
WHEN state = 5 AND file_type=1 THEN 8*1024*1024
WHEN state IN (6,7) THEN 68*1024*1024
ELSE file_size_in_bytes
END) / 1024 / 1024 AS [on-disk size MB]
FROM sys.dm_db_xtp_checkpoint_files
GROUP BY state, state_desc, file_type, file_type_desc
ORDER BY state, file_type
| state_desc | 파일 유형 설명 | 개수 | 디스크 크기 MB |
| 미리 생성된 | 데이터 | 16 | 2048 |
| 미리 생성된 | 델타 | 16 | 128 |
| 공사 중 | 데이터 | 2 | 268 |
| 공사 중 | 델타 | 2 | 16 |
| 활성 | 데이터 | 41 | 5608 |
| 활성 | 델타 | 41 | 328 |
이 경우에는 'under construction' 상태의 검사점 파일 쌍이 두 개 있습니다. 즉, 작업의 높은 동시성 수준 때문에 여러 파일 쌍이 ‘under construction’ 상태로 이동했습니다. 여러 동시 스레드에서 같은 시간에 새로운 파일 쌍을 필요로 했으므로 파일 쌍이 'precreated'에서 ‘under construction’으로 이동했습니다.