sp_estimate_data_compression_savings(Transact-SQL)

아티클
06/12/2024

적용 대상: SQL Server Azure SQL Database Azure SQL Managed Instance

요청된 개체의 현재 크기를 반환하고 요청된 압축 상태의 개체 크기를 예측합니다. 전체 테이블 또는 테이블 부분에 대해 압축을 평가할 수 있습니다. 여기에는 힙, 클러스터형 인덱스, 비클러스터형 인덱스, columnstore 인덱스, 인덱싱된 뷰, 테이블 및 인덱스 파티션이 포함됩니다. 행, 페이지, columnstore 또는 columnstore 보관 압축을 사용하여 개체를 압축할 수 있습니다. 테이블, 인덱스 또는 파티션이 이미 압축된 경우 이 절차를 사용하여 압축 없이 압축되거나 저장되는 경우 테이블, 인덱스 또는 파티션의 크기를 예측할 수 있습니다.

sys.sp_estimate_data_compression_savings 시스템 저장 프로시저는 Azure SQL Database 및 Azure SQL Managed Instance에서 사용할 수 있습니다.

SQL Server 2022(16.x)부터 xml 데이터 형식을 사용하여 열에서 행이 다른 XML 데이터를 압축하여 스토리지 및 메모리 요구 사항을 줄일 수 있습니다. 자세한 내용은 CREATE TABLE 및 CREATE INDEX를 참조하세요. sp_estimate_data_compression_savings 는 XML 압축 예상을 지원합니다.

참고 항목

압축 및 sp_estimate_data_compression_savings SQL Server의 모든 버전에서 사용할 수 없습니다. SQL Server 버전에서 지원되는 기능 목록은 SQL Server 2022의 버전과 지원하는 기능을 참조하세요.

요청된 압축 설정을 사용하는 경우 개체의 크기를 예측하기 위해 이 저장 프로시저는 원본 개체를 샘플링하고 이 데이터를 생성된 tempdb동일한 테이블 및 인덱스에 로드합니다. 그런 다음 생성된 tempdb 테이블 또는 인덱스가 요청된 설정으로 압축되고 예상 압축 절감액이 계산됩니다.

테이블, 인덱스 또는 파티션의 압축 상태를 변경하려면 ALTER TABLE 또는 ALTER INDEX 문을 사용합니다. 압축에 대한 일반적인 내용은 데이터 압축을 참조 하세요.

참고 항목

기존 데이터가 조각화된 경우 인덱스 다시 작성을 통해 압축을 사용하지 않고 크기를 줄일 수 있습니다. 인덱스의 경우 인덱스를 다시 작성하는 동안 채우기 인수가 적용됩니다. 이렇게 하면 인덱스의 크기가 증가할 수 있습니다.

Transact-SQL 구문 표기 규칙

구문

sp_estimate_data_compression_savings
    [ @schema_name = ] N'schema_name'
    , [ @object_name = ] N'object_name'
    , [ @index_id = ] index_id
    , [ @partition_number = ] partition_number
    , [ @data_compression = ] N'data_compression'
    [ , [ @xml_compression = ] xml_compression ]
[ ; ]

인수

[ @schema_name = ] N'schema_name'

테이블 또는 인덱싱된 뷰를 포함하는 데이터베이스 스키마의 이름입니다. @schema_name sysname이며 기본값은 없습니다. @schema_name NULL경우 현재 사용자의 기본 스키마가 사용됩니다.

[ @object_name = ] N'object_name'

인덱스가 있는 테이블 또는 인덱싱된 뷰의 이름입니다. @object_name 기본값이 없는 sysname입니다.

[ @index_id = ] index_id

인덱스의 ID입니다. @index_id int이며 다음 값 중 하나일 수 있습니다.

인덱스의 ID 번호
NULL
0object_id 힙인 경우

기본 테이블 또는 뷰의 모든 인덱스에 대한 정보를 반환하려면 .를 지정합니다 NULL. 지정NULL하는 경우 @partition_number 지정 NULL 해야 합니다.

[ @partition_number = ] partition_number

개체의 파티션 번호입니다. @partition_number int이며 다음 값 중 하나일 수 있습니다.

인덱스 또는 힙의 파티션 번호
NULL
1 분할되지 않은 인덱스 또는 힙의 경우

파티션을 지정하려면 $PARTITION 함수를 지정할 수도 있습니다. 소유 개체의 모든 파티션에 대한 정보를 반환하려면 .NULL

[ @data_compression = ] N'data_compression'

평가할 압축 유형을 지정합니다. @data_compression nvarchar(60)이며 다음 값 중 하나일 수 있습니다.

NONE
ROW
PAGE
COLUMNSTORE
COLUMNSTORE_ARCHIVE

SQL Server 2022(16.x) 이상 버전의 NULL 경우 가능한 값이기도 합니다. @xml_compression 경우 @data_compression NULL수 NULL 없습니다.

[ @xml_compression = ] xml_compression

적용 대상: SQL Server 2022(16.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance

XML 압축에 대한 절감액을 계산할지 여부를 지정합니다. @xml_compression 비트이며 다음 값 중 하나일 수 있습니다.

NULL(기본값)
0
1

@data_compression 경우 @xml_compression NULL수 NULL 없습니다.

반환 코드 값

0 (성공) 또는 1 (실패).

결과 집합

테이블, 인덱스 또는 파티션에 대한 현재 및 예상 크기를 제공하기 위해 다음 결과 집합이 반환됩니다.

열 이름	데이터 형식	설명
`object_name`	sysname	테이블 또는 인덱싱된 뷰의 이름입니다.
`schema_name`	sysname	테이블 또는 인덱싱된 뷰의 스키마입니다.
`index_id`	int	인덱스의 인덱스 ID입니다. `0` = 힙 `1` = 클러스터형 인덱스 `>1` = 비클러스터형 인덱스
`partition_number`	int	파티션 번호입니다. 분할되지 않은 테이블 또는 인덱스 값을 반환 `1` 합니다.
`size_with_current_compression_setting (KB)`	bigint	요청된 테이블, 인덱스 또는 파티션이 현재 존재하는 크기입니다.
`size_with_requested_compression_setting (KB)`	bigint	요청된 압축 설정을 사용하는 테이블, 인덱스 또는 파티션의 예상 크기입니다. 및 해당하는 경우 기존 채우기 비율과 조각화가 없다고 가정합니다.
`sample_size_with_current_compression_setting (KB)`	bigint	현재 압축 설정이 있는 샘플의 크기입니다. 이 크기에는 모든 조각화가 포함됩니다.
`sample_size_with_requested_compression_setting (KB)`	bigint	요청된 압축 설정을 사용하여 만든 샘플의 크기이며, 해당되는 경우 조각화가 없는 것으로 가정하고 기존 채우기 비율을 사용합니다.

설명

행, 페이지, columnstore, columnstore 보관 또는 XML 압축에 대해 테이블 또는 파티션을 사용하도록 설정할 때 발생할 수 있는 절감액을 예측하는 데 사용합니다 sp_estimate_data_compression_savings . 예를 들어 행의 평균 크기를 40% 줄일 수 있는 경우 개체의 크기를 40% 줄일 수 있습니다. 이는 채우기 비율과 행의 크기에 따라 달라지므로 공간을 절약하지 못할 수 있습니다. 예를 들어 길이가 8,000바이트인 행이 있고 크기가 40% 줄어도 데이터 페이지에서 한 행만 맞출 수 있습니다. 저축은 없습니다.

압축되지 않은 테이블 또는 인덱스에서 실행 sp_estimate_data_compression_savings 한 결과 크기가 증가한다는 것을 나타내면 많은 행이 데이터 형식의 거의 전체 전체 자릿수를 사용하고 압축된 형식에 필요한 작은 오버헤드를 추가하는 것이 압축을 절약하는 것보다 더 많은 것을 의미합니다. 이 드문 경우에서는 압축을 사용하도록 설정하지 마세요.

테이블을 압축할 수 있는 경우 테이블이 압축되지 않은 경우 행의 평균 크기를 예측하는 데 사용할 sp_estimate_data_compression_savings 수 있습니다.

이 작업 중에 테이블에서 의도 공유(IS) 잠금을 획득합니다. IS 잠금을 가져올 수 없는 경우 프로시저가 차단됩니다. 테이블은 기본 읽기 커밋된 격리 수준에서 검사됩니다.

요청된 압축 설정이 현재 압축 설정과 같으면 저장 프로시저는 원본 개체의 인덱스에 대한 기존 채우기 인수를 사용하여 데이터 조각화 없이 예상 크기를 반환합니다.

인덱스 또는 파티션 ID가 없으면 결과가 반환되지 않습니다.

사용 권한

SELECT 테이블 및 VIEW DEFINITION 테이블을 VIEW DATABASE STATE 포함하는 tempdb데이터베이스에 대한 사용 권한이 필요합니다.

제한 사항

SQL Server 2017(14.x) 및 이전 버전에서는 이 프로시저가 columnstore 인덱스에 적용되지 않았으므로 데이터 압축 매개 변수 COLUMNSTORE 및 COLUMNSTORE_ARCHIVE을 허용하지 않았습니다. SQL Server 2019(15.x) 이상 버전 및 Azure SQL Database 및 Azure SQL Managed Instance에서 columnstore 인덱스를 예측에 대한 원본 개체와 요청된 압축 형식으로 모두 사용할 수 있습니다.

메모리 최적화 TempDB 메타데이터를 사용하도록 설정하면 임시 테이블에서 columnstore 인덱스 만들기가 지원되지 않습니다. 이 제한 sp_estimate_data_compression_savings 사항으로 인해 메모리 최적화 TempDB 메타데이터를 COLUMNSTORE 사용하는 경우 데이터 압축 매개 변수와 COLUMNSTORE_ARCHIVE 함께 지원되지 않습니다.

columnstore 인덱스에 대한 고려 사항

SQL Server 2019(15.x)부터 Azure SQL Database 및 Azure SQL Managed Instance sp_estimate_compression_savings 에서 columnstore 및 columnstore 보관 압축을 모두 추정할 수 있습니다. 페이지 및 행 압축과 달리 columnstore 압축을 개체에 적용하려면 새 columnstore 인덱스를 만들어야 합니다. 이러한 이유로 이 프로시저의 옵션 및 COLUMNSTORE_ARCHIVE 옵션을 사용할 COLUMNSTORE 때 프로시저에 제공된 원본 개체의 형식에 따라 압축된 크기 예측에 사용되는 columnstore 인덱스의 형식이 결정됩니다. 다음 표에서는 @data_compression 매개 변수가 하나 또는 COLUMNSTORE_ARCHIVE로 설정된 경우 각 원본 개체 형식에 대한 압축 절감을 추정하는 데 사용되는 참조 개체를 COLUMNSTORE 보여 줍니다.

원본 개체	참조 개체
**힙	클러스터형 columnstore 인덱스
클러스터형 인덱스	클러스터형 columnstore 인덱스
비클러스터형 인덱스	비클러스터형 columnstore 인덱스(제공된 비클러스터형 인덱스의 키 열 및 포함된 열 및 테이블의 파티션 열(있는 경우)
비클러스터형 columnstore 인덱스	비클러스터형 columnstore 인덱스(제공된 비클러스터형 columnstore 인덱스와 동일한 열 포함)
클러스터형 columnstore 인덱스	클러스터형 columnstore 인덱스

참고 항목

rowstore 원본 개체(클러스터형 인덱스, 비클러스터형 인덱스 또는 힙)에서 columnstore 압축을 추정할 때 columnstore 인덱 sp_estimate_compression_savings 스에서 지원되지 않는 데이터 형식이 있는 원본 개체에 열이 있으면 오류가 발생합니다.

마찬가지로 @data_compression 매개 변수를 /&@data_compression 매개 변수로 ROWNONE설정하거나 PAGE 원본 개체가 columnstore 인덱스인 경우 다음 표에서는 사용된 참조 개체를 간략하게 설명합니다.

원본 개체	참조 개체
클러스터형 columnstore 인덱스	힙
비클러스터형 columnstore 인덱스	비클러스터형 인덱스(비클러스터형 columnstore 인덱스에 포함된 열을 키 열로, 테이블의 파티션 열(있는 경우 포함 열)을 포함)

참고 항목

columnstore 원본 개체에서 rowstore 압축(NONE, ROW 또는 PAGE)을 추정할 때는 rowstore(비클러스터형) 인덱스에서 지원되는 제한이므로 원본 인덱스에 32개 이상의 키 열이 포함되지 않도록 해야 합니다.

예제

이 문서의 Transact-SQL 코드 샘플은 Microsoft SQL Server 샘플 및 커뮤니티 프로젝트 홈페이지에서 다운로드할 수 있는 데이터베이스 또는 AdventureWorksDW2022 샘플 데이터베이스를 사용합니다AdventureWorks2022.

A. ROW 압축을 사용하여 절감액 예측

다음 예제에서는 압축을 사용하여 ROW 압축되는 경우 테이블의 Production.WorkOrderRouting 크기를 예측합니다.

EXEC sys.sp_estimate_data_compression_savings
     'Production', 'WorkOrderRouting', NULL, NULL, 'ROW';
GO

B. PAGE 및 XML 압축을 사용하여 절감액 예측

적용 대상: SQL Server 2022(16.x) 이상 버전

다음 예제에서는 압축을 사용하여 PAGE 압축되고 @xml_compression 값이 사용되는 경우 테이블의 크기를 Production.ProductModel 예측합니다.

EXEC sys.sp_estimate_data_compression_savings
     'Production', 'ProductModel', NULL, NULL, 'PAGE', 1;
GO

다음을 통해 공유

sp_estimate_data_compression_savings(Transact-SQL)

구문

인수

[ @schema_name = ] N'schema_name'

[ @object_name = ] N'object_name'

[ @index_id = ] index_id

[ @partition_number = ] partition_number

[ @data_compression = ] N'data_compression'

[ @xml_compression = ] xml_compression

반환 코드 값

결과 집합

설명

사용 권한

제한 사항

columnstore 인덱스에 대한 고려 사항

예제

A. ROW 압축을 사용하여 절감액 예측

B. PAGE 및 XML 압축을 사용하여 절감액 예측

피드백

피드백

추가 리소스

다음을 통해 공유

sp_estimate_data_compression_savings(Transact-SQL)

구문

인수

[ @schema_name = ] N'schema_name'

[ @object_name = ] N'object_name'

[ @index_id = ] index_id

[ @partition_number = ] partition_number

[ @data_compression = ] N'data_compression'

[ @xml_compression = ] xml_compression

반환 코드 값

결과 집합

설명

사용 권한

제한 사항

columnstore 인덱스에 대한 고려 사항

예제

A. ROW 압축을 사용하여 절감액 예측

B. PAGE 및 XML 압축을 사용하여 절감액 예측

관련 콘텐츠

피드백

피드백

추가 리소스