Data Science Lab

SQL Server 2019 에서 업그레이드된sp_estimate_data_compression_savings 프로시저 (컬럼스토어 압축율 예상)

·         Version : SQL Server 2019

SQL Server 2019 CTP 2.0에서 스토리지 엔진의 업그레이드 기능으로 columnstore 및 columnstore archive 압축에 대한 지원으로sp_estimate_data_compression_savings 프로시저 기능이 업데이트 되었다. 이 저장 프로시저는 SQL Server 2008 이후에 사용되었으며 테이블 및 인덱스 압축을 고려할때 사용할 수 있다. 프로시저에서는 schema_name, object_name, index_id, partition_number 및 data_compression의 5가지 매개 변수가 필요하다.

·         sp_estimate_data_compression_savings : https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-estimate-data-compression-savings-transact-sql?view=sql-server-2017

SQL Server 2019 이전에는 data_compression 매개변수가 ‘ROW’, ‘PAGE’ 또는 ‘NONE’ 이다. SQL Server 2019 CTP 2.0에서는 ‘COLUMNSTORE’ 및 ‘COLUMNSTORE_ARCHIVE’라는 두 가지 새로운 옵션을 추가 되었다. rowstore 테이블 및 인덱스의 경우 압축을 적용하는 방법은 원하는 오브젝트를 다시 작성하면서 압축을 한다. 아래 스크립트는 행 압축을 사용하여 힙 테이블을 다시 작성한다.

압축 비율은 테이블에 저장되는 데이터 유형에 크게 의존하므로 압축 공간 절약을 예상하는 유일한 좋은 방법은 실제로 데이터를 샘플링 압축하고 그 결과를 확인하는 것이다. 행 저장소 압축 유형에서 sp_estimate_data_comopression_savings이 작동하는 방식은 소스 객체 (5000 페이지 이상)의 샘플을 샘플링하고 tempdb에 샘플 객체를 만든다. 그런다음 원하는 압축 상태를 사용하여 샘플 객체가 다시 작성된다. 추정된 압축비는 원래 샘플 객체 크기를 압축된 샘플 개체 크기와 비교함으로써 계산된다. 이 비율은 원하는 압축 설정이 적용된 경우 전체 원본 개체의 예상 크기를 투영하는데 사용된다. 압축이 이미 적용된 소스 객체의 경우 data_compression 매개 변수 값 ‘NONE’을 제공하여 압축이 제거된 상태로 객체 크기를 추정하는데 사용할 수 있다.

columnstore  압축에서는 컬럼스토어 인덱스를 생성할때 일반적으로 columnstore 또는 columnstore archive 압축에 영향을 미친다. 행 저장소 원본 객체에 ‘COLUMNSTORE’ 또는 ‘COLUMNSTORE_ARCHIVE’라는 data_compression 매개 변수 값을 사용하면 동일한 행 저장소 객체를 다른 압축 상태와 비교하지 않고 행 저장소 객체를 동일한 columnstore 객체와 비교한다. 이 과정은 거의 동일하다. 소스 객체에서 샘플링 하여 tempdb 에 샘플 객체를 만들고 같은 데이터를 사용하여 동등한 columnstore 인덱스를 만들고, 소스 객체의 크기와 columnstore 인덱스의 크기를 비교하여 압축 비율을계산한다. 

아래 표는 압축 비율을 계산하는데 사용하는 다양한 참조 객체 유형이다. 이는 ‘COLUMNSTORE’ 및 ‘COLUMNSTORE_ARCHIVE’ data_compression 옵션 모두에 적용된다.

column store에서 rowstore 압축 상태로 다른 방향으로 추정할 수 있다. 이 옵션은 모든 행 저장소 옵션은 ‘ROW’, ‘PAGE’ 및 ‘NONE’에 적용된다.

기존 columnstore 원본 객체에 대해 ‘COLUMNSTORE’ 또는 ‘COLUMNSTORE”ARCHIVE’ 옵션을 사용하는 경우 절차는 이전과 동일하게 작동하고 샘플 columnstore 인덱스를 만들고 원하는 압축 상태로 해당 인덱스를 다시 작성한다.

일반적으로 데이터 압축은 많은 공간과 많은 I/O (논리적 및 물리적)를 절약할 수 있지만 데이터를 압축하고 해제하는데 더 많은 CPU 리소스가 필요하다. 특히 업데이트가 많은 데이터의 경우 성능 문제가 발생할 수있다. 이것은 columnstore에서 두배이상의 오버헤드가 있다. 이러한 인덱스는 주로 읽기 데이터 용으로 설계되었으며 columnstore에 대한 빈번한  업데이트는 효율적이지 않은 구조이다. sp_estimate_data_compression_savings 작업 패턴을 면밀히 분석하면 데이터 및 응용프로그램에 적합한 인덱스 및 압축을 선택할 수 있다.

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/sql-server-2019-ctp-2-0-new-features-columnstore-support-for-sp_estimate_data_compression_savings/

2018-10-24 / Sungwook Kang / http://sqlmvp.kr

SQL Server, MSSQL, SQL 2019, Rowstore, column sotre, data compression, sp_estimate_data_compression_saving

SQL Server 2019 에서 추가된sys.dm_db_page_info, sys.fn_PageResCracker 기능으로 대기 관련 정보 확인

·         Version : SQL Server 2019

SQL Server 2019 CTP 2.0에서 추가된 스토리지 엔진의 새로운 기능으로 페이지 관련 대기를 볼 수 있는 sys.dm_db_page_info DMV가 추가 되었다. 이 기능은  기존의 DBCC로 확인할 수 있었던 tempdb 경합이나, 마지막 페이지 삽입 경합(las page insert contention) 및 페이지 수준의 블록킹 등을 확인할 수 있다.

sys.dm_db_page_info DMV는 데이터베이스 ID, 파일 ID, 페이지 ID 및 모드(LIMITED 또는 DETAILED)의 4가지 매개 변수를 사용한다. sys.dm_db_page_info는 object_id, index_id 및 partition_id 포함하여 페이지의 헤더 정보를 단일 행이 있는 테이블로 반환한다. 또한 sys.dm_exec_requests 또는 sys.sysprocesses DMV 함수와 조인하여 페이지 관련 경합이 있을때 이 정보를  확인할 수 있다. 아래 스크립트는 sys.dm_db_page_info,  sys.fn_PageResCracker를 사용하여 모든 활성 요청에 대한 페이지 관련 대기를 확인할 수 있다.

위 새로운 기능에도 몇 가지 제한사항은 있다. 우선 DBCC PAGE를 완전히 대체할 수 없다. DBCC PAGE는 헤더 정보와 모든 데이터 및 슬롯 배열을 포함하여 페이지의 전체 내용을 제공한다. 또한  현재 sys.dm_exec_requests및 sys.sysprocesses에 대한 조인만 지원된다.

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/sql-server-2019-ctp-2-0-new-features-introducing-the-page-cracker-aka-sys-dm_db_page_info/

2018-10-16 / Sungwook Kang / http://sqlmvp.kr

SQL Server, MSSQL, SQL 2019, Rowstore, Batch mode, Batch Mode on Rowstore, Intelligent Query Processing

SQL Server 2019에서 향상된  Rowstore batch mode

·         Version : SQL Server 2019

SQL Server 2019 Preview에서 소개된 내용으로 쿼리 처리 개선 사항으로 Intelligent Query Processing(QP) 기능중 Batch mode on Rowstore (Rowstore일괄처리 모드)에 대해서 소개한다. 이 기능은 쿼리처리에 사용할 수 있는 컬럼스토어 인덱스가 없는 경우 일괄처리 모드 실행의 이점을 제공한다.

일괄처리 모드는 주로 여러 행을 검색하고 행 전체에서 중요한 집계, 정렬 및 그룹화 작업을 수행하는 분석 쿼리를 대상으로 한다. 지금까지는 컬럼스토어 인덱스와 관련된 쿼리에는 배치모드가 예약되었다. 행 단위가 아니라 한번에 ~900행의 배치를 사용하여 스캔 및 계산을 수행하면 분석 유형의 쿼리에서 훨씬 효율적이다. 일괄 처리 모드를 사용하면 동일한 데이터에서 동일한 쿼리를 여러번 호출하는 행 모드보다 빠르게 쿼리를 실행 할 수 있다.

아래 예제는 행모드와 배치모드를 설명하기 위해 WideWorldImportersDW 데이터베이스를 사용한다.

먼저 행 모드로 실행한 결과이다. 실행 계획에서 아무거나 선택하여 속성을 확인해보면 행모드 인것을 확인할 수 있다.

연산자의 속성을 확인하기 위해서 클러스터된 인덱스 스캔 노드 (가장 오른쪽에 위치한 연산자)를 마우스 오른쪽 버튼으로 클릭한다.

실제 실행 모드는 “Row”이고 저장소는 “RowStore”이다. 쿼리 수행시간은 테스트 컴퓨터에서 약 10초 정도 실행되었다.

아래 실행 결과는 배치모드의 실행 계획이다. 쿼리를 호환성 수준을 150으로 변경하여 배치 모드 행 저장소를 활성화 한다. 일괄 처리 모드 처리는 스캔 작업의 일부로 집계를 수행하고 병렬처리를 다르게 처리하므로 쿼리 계획에 약간의 차이가 있음을알 수 있다.

클러스터된 인덱스 스캔의 속성을 살펴 보면 예상대로 Storage가 “RowStore” 이지만 실제 실행 모드가 “Batch”임을 알 수 있다. 그리고 쿼리실행 시간은 1/3 이하로 줄었다.

배치 모드는 다양한 케이스에서 성능 이점이 많지만  컬럼스토 인덱스 테이블에는 적합하지 않다.  배치 모드는 모든 분석 쿼리를 크게 향상 시킬 수 있자만 몇 행만 대상으로 하거나 중요한 집계를 수행하지 않는 쿼리는 큰 이익을 얻지 못한다.

각 하드웨어, 데이터에 따라 다르겟지만 일괄 처리 디자인은 많은 수의 행에 대해 집계가 필요한 쿼리의 경우 쿼리 속도와 효율성면에서 많은 이득을 얻을 수 있다.

2018-10-03 / Sungwook Kang / http://sqlmvp.kr

SQL Server, MSSQL, SQL 2019, Rowstore, Batch mode, Batch Mode on Rowstore, Intelligent Query Processing

SQL Server 2016부터 도입된 USE HINT를 사용한 추적 플래그 활성화

·         Version : SQL Server 2016 later

SQL Server에서는 서버의 특정 동작을 임시로 설정 하거나 중지 시킬때 사용하는 추적 플래그(Trace Flag)라는 기능이 있다. 추적 플래그는 주로 성능 문제를 진단하거나 저장 프로시저 또는 복잡한 컴퓨터 시스템을 디버깅하는데 사용된다.

·         DBCC TRACE ON – Trace Flags :  https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017

추적 플래그를 쿼리에서 사용할때 지금까지는 사용하려는 추적 플래그의 번호를 사용하였다.

SQL Server 2016 SP1에서는 USE HINT 라는  쿼리 힌트를 도입하여 사용자가 추적 플래그 번호를 기억할 필요 없이 추적 플래그 이름을 사용할 수 있게 되었다. 아래 두 쿼리는 동일한 기능의 추적 플래그를 실행 한다.  

SQL Server 2017 CU10에서는 QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n 이라는 몇 가지 새로운 USE 힌트를 도입했다. 여기서 n은 지원되는 데이터베이스 호환성 수준이다. 이렇게 하면 쿼리가 데이터베이스 호환성 수준으로 컴파일된 것처럼 쿼리 수준에서 쿼리 최적화 프로그램 동작이 강제로 수행된다. N에 대해 지원되는 값을 보려면 아래 링크를 참고 한다.

·         sys.dm_exec_valid_use_hints : https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-valid-use-hints-transact-sql?view=sql-server-2017

새로운 힌트는 특정 CE 모델만 강제하는 것은 아니며 데이터베이스 호환성 수준에서 기본적으로 활성화되는 쿼리 최적화 프로그램 수정을 비롯하여 특정 베이터베이스 호환성 수준의 쿼리 최적화 프로그램 동작과 동등하다.

아래 예제는 새로운 힌트를 사용하여 데이터베이스 호환성 수준이 SQL Server 2014수준(상단의 실행 계획)으로 유지되는 것처럼 해당 특정 쿼리를 컴파일 할 수 있었으며 SQL Server 2017 계획(하단의 실행 계획)과의 차이점을 확인할 수 있다.

등록 정보를 보면 사용된 CE 버전(120 vs 130)과 각 QueryTimeStats간의 차이점을 확인할 수 있다.

모든 힌트는 특정 행동을 강요하고 향후 업그레이드를 통해서 해결 될 수도 있다. Microsoft에서는 다른 옵션이 없는 경우에만 힌트를 적용하고 힌트가 있는 코드를 새로 업그레이드 할 때마다 다시 계획 할 것을 권장한다. 힌트를 통한 강제 적용은 최신 버전에서 향샹된 기능의 이점을 워크로드에서 제외할 수도 있다.

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/developers-choice-hinting-query-execution-model/

2018-10-01 / Sungwook Kang / http://sqlmvp.kr

SQL Server, MSSQL, 쿼리 힌트, USE 쿼리 힌트, 추적 플래그, Query Hint, Trace Flag, USE Hint