SQL Server 2019 에서 문자열 잘림에 대한 향상된 에러 메시지 반환

 

·         Version : SQL Server 2019

 

SQL Server 2019 CTP 2.0에서 문자열 잘림에 대한 에러메시지가 향상되었다. 일반적으로 ETL 작업 많이 겪는 문제중 하나가 “String or binary data would be truncated”이다. 오류 조건은 일치하는 데이터 유형 / 길이가 없는 소스와 대상간에 ETL 구현할 발생할 있다. 특히 대형 데이터 세트에서 가장 시간이 많이 걸리는 프로세스중 하나이다. 아래 스크립트는 크기가 충분하지 않는 열에 해당 열보다 데이터를 삽입하면 어떻게 되는지 확인하는 예제 스크립트이다.

DROP TABLE IF EXISTS [Sales].[SalesOrderHeaderTest]

GO

CREATE TABLE [Sales].[SalesOrderHeaderTest](

    [SalesOrderID] [INT] NOT NULL,

    [CustomerID] [INT] NOT NULL,

    [CreditCardApprovalCode] [nvarchar](13) NULL

)

GO

 

INSERT INTO [Sales].[SalesOrderHeaderTest]

SELECT [SalesOrderID], [CustomerID], [CreditCardApprovalCode]

FROM [Sales].[SalesOrderHeader]

GO

 

스크립트를 실행하면 아래와 같은 오류 메시지가 표시 되는데, 문제 해결에 크게 도움이 되지 않는다.

Msg 8152, Level 16, State 4, Line 10

String or binary data would be truncated.

The statement has been terminated.

 

SQL Server 2019 CTP 2.0에서는 추가 컨텍스트 정보와 함께 메시지를 나타낸다. 동일한 작업의 경우 오류 메시지는 아래과 같이 출력된다.

Msg 2628, Level 16, State 1, Line 14

String or binary data would be truncated in table 'AdventureWorks2016CTP3.Sales.SalesOrderHeaderTest', column 'CreditCardApprovalCode'. Truncated value: '1231736Vi8604'.

The statement has been terminated.

 

새로운 오류 메시지는 많은 컨텍스트를 제공하고 결과 (소스값 아님) 보여준다. 결과를 보면 문제가 발생한 부분이 ‘1213736Vi8604’ 시작한다는 것을 있다. 길이가 13 이다. 다시 소스로 돌아와 해당 레코드의 길이를 살펴보면 14 인것을 있다. 그러므로 데이터가 잘리는 오류가 발생한다. 해당 오류를 확인하고 적절한 데이터 길이로 컬럼을 변경해서 해결해야한다.

 SELECT [SalesOrderID], [CustomerID], [CreditCardApprovalCode], LEN([CreditCardApprovalCode])

FROM [Sales].[SalesOrderHeader]

WHERE CreditCardApprovalCode LIKE '1231736Vi8604%'

 

 

새로운 메시지는 SQL Server 2017 CU12 SQL Server 2016 SP2 CU에도 적용되지만 기본적으로 활성화 되지 않는다. 추적플래그 460 활성화하여 세션ID 또는 서버 레벨에서 메시지 ID 8152 2628 바꿀 필요가 있다. 현재로서는 SQL Server 2019 CTP 2.0에서도 동일한 추적 플래그 460 사용할 있어야 한다. 향후 SQL Server 2019 릴리스에서는 기본적으로 메시지 2628 8152 대체한다.

오류메시지가 반환할 있는 잘린 문자열에 대한 최대 크기는 100 까지만 표시된다. 아래 스크립트는 123자의 문자를 VARCHAR(120) 삽입하는 스크립트이다.

CREATE TABLE myTable (myString VARCHAR(120));

GO

INSERT INTO myTable

VALUES ('Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.')

GO

 

스크립트를 실행하면 문자여링 120자에서 잘리지만 실제 표시되는 오류는 처음100 까지만 표시된다.

Msg 2628, Level 16, State 1, Line 30

String or binary data would be truncated in table 'AdventureWorks2016CTP3.dbo.myTable', column 'myString'. Truncated value: 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore '.

 

 

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/string-or-binary-data-would-be-truncated-replacing-the-infamous-error-8152/

 

 

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

 

SQL Server, MSSQL, SQL 2019, TF 460, 문자 잘림 오류

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 테이블 인덱스의 경우 압축을 적용하는 방법은 원하는 오브젝트를 다시 작성하면서 압축을 한다. 아래 스크립트는 압축을 사용하여 테이블을 다시 작성한다.

ALTER TABLE Production.TransactionHistory REBUILD PARTITION = ALL

WITH (DATA_COMPRESSION = ROW);

GO

 

압축 비율은 테이블에 저장되는 데이터 유형에 크게 의존하므로 압축 공간 절약을 예상하는 유일한 좋은 방법은 실제로 데이터를 샘플링 압축하고 결과를 확인하는 것이다. 저장소 압축 유형에서 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 옵션 모두에 적용된다.

Source Object

Reference Object

Heap

Clustered columnstore index

Clustered index

Clustered columnstore index

Non-clustered index

Non-clustered columnstore index (including the key columns and any included columns of the provided non-clustered index, as well as the partition column of the table, if any)

Non-clustered columnstore index

Non-clustered columnstore index (including the same columns as the provided non-clustered columnstore index)

Clustered columnstore index

Clustered columnstore index

 

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

Source Object

Reference Object

Clustered columnstore index

Heap

Non-clustered columnstore index

Non-clustered index (including the columns contained in the non-clustered columnstore index as key columns, and the partition column of the table, if any, as an included column)

 

기존 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 사용하여 모든 활성 요청에 대한 페이지 관련 대기를 확인할 있다.

SELECT page_info.*

FROM sys.dm_exec_requests AS d 

CROSS APPLY sys.fn_PageResCracker (d.page_resource) AS r 

CROSS APPLY sys.dm_db_page_info(r.db_id, r.file_id, r.page_id, 1) AS page_info

 

새로운 기능에도 가지 제한사항은 있다. 우선 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 데이터베이스를 사용한다.

SELECT

        [Lineage Key],

        SUM([Quantity]) AS SUM_QTY,

        SUM([Unit Price]) AS SUM_BASE_PRICE,

        SUM([Unit Price]*(1+[Tax Rate])) AS SUM_DISC_PRICE,

        SUM(([Unit Price]+[Total Including Tax] )*(1+[Tax Rate])) AS SUM_CHARGE,

        AVG([Quantity]) AS AVG_QTY,

        AVG([Unit Price]) AS AVG_PRICE,

        COUNT(*) AS COUNT_ORDER

FROM Fact.[Sale]

WHERE [Invoice Date Key] >= DATEADD(dd, -73, '1998-12-01')

GROUP BY  [Lineage Key]

ORDER BY [Lineage Key];

 

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

 


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

 


실제 실행 모드는 “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

+ Recent posts