약 5m 행의 일반 로그 테이블이 있습니다.
이벤트 유형을 저장하는 “강력한 유형”필드와 이벤트와 관련된 데이터가 포함 된 “손실 된 유형”열이 있습니다. 즉, “손실 된 유형”열의 의미는 이벤트 유형에 따라 다릅니다.
이 열은 다음과 같이 정의됩니다.
USER_CHAR1 nvarchar(150) null,
USER_CHAR2 nvarchar(150) null,
USER_CHAR3 nvarchar(150) null,
USER_CHAR4 nvarchar(150) null,
USER_CHAR5 nvarchar(150) null,
USER_INTEGER1 int null,
USER_INTEGER2 int null,
USER_INTEGER3 int null,
USER_INTEGER4 int null,
USER_INTEGER5 int null,
USER_FLAG1 bit null,
USER_FLAG2 bit null,
USER_FLAG3 bit null,
USER_FLAG4 bit null,
USER_FLAG5 bit null,
USER_FLOAT1 float null,
USER_FLOAT2 float null,
USER_FLOAT3 float null,
USER_FLOAT4 float null,
USER_FLOAT5 float null
각 유형의 1 열과 2 열은 많이 사용되지만 3 번부터는 많은 이벤트 유형이 이러한 많은 정보를 제공합니다. 따라서 각 유형에서 3-5 열을로 표시하고 싶습니다 SPARSE
.
먼저 몇 가지 분석을 수행 한 결과 실제로 각 열의 데이터 중 80 % 이상이 데이터 null
이고 100 %가 데이터 인 것을 알았습니다 null
. 40 % 절감 임계 값 표 에 따르면 , SPARSE
그들에게 큰 승리가 될 것입니다.
그래서 나는 가서 SPARSE
각 그룹의 3-5 열에 적용 했습니다. 이제 내 테이블은에 의해보고 된 데이터 공간에서 약 1.8Gb를 차지 sp_spaceused
하지만 스파링하기 전에는 1Gb였습니다.
시도 dbcc cleantable
했지만 효과가 없습니다.
그런 다음 dbcc shrinkdatabase
아무 효과도 없습니다.
의아해, 나는 s를 제거 SPARSE
하고 반복했다 dbcc
. 테이블 크기는 1.8Gb로 유지되었습니다.
무엇을 제공합니까?
답변
열을 스파 스로 만든 후 클러스터형 인덱스를 다시 작성해야합니다. 삭제 된 열은 쿼리에 대해 sys.system_internals_partition_columns
또는 사용하여 확인할 수있을 때까지 데이터 페이지에 계속 존재합니다.DBCC PAGE
SET NOCOUNT ON;
CREATE TABLE Thing
(
ThingId int IDENTITY CONSTRAINT PK PRIMARY KEY,
USER_CHAR1 nvarchar(150) null,
USER_CHAR2 nvarchar(150) null,
USER_CHAR3 nvarchar(150) null,
USER_CHAR4 nvarchar(150) null,
USER_CHAR5 nvarchar(150) null
)
INSERT INTO Thing
SELECT REPLICATE('A',150),
CASE WHEN number % 5 = 1 THEN REPLICATE('A',150) END,
CASE WHEN number % 5 = 2 THEN REPLICATE('A',150) END,
CASE WHEN number % 5 = 3 THEN REPLICATE('A',150) END,
CASE WHEN number % 5 = 4 THEN REPLICATE('A',150) END
FROM master..spt_values
EXEC sp_spaceused 'Thing'
ALTER TABLE dbo.Thing ALTER COLUMN USER_CHAR2 ADD SPARSE
ALTER TABLE dbo.Thing ALTER COLUMN USER_CHAR3 ADD SPARSE
ALTER TABLE dbo.Thing ALTER COLUMN USER_CHAR4 ADD SPARSE
ALTER TABLE dbo.Thing ALTER COLUMN USER_CHAR5 ADD SPARSE
DECLARE @DynSQL NVARCHAR(MAX);
SELECT @DynSQL = 'DBCC TRACEON (3604);
DBCC PAGE(0, ' + LEFT(file_id,10) + ', ' + LEFT(page_id,10) + ', 3);
DBCC TRACEOFF(3604); '
FROM Thing
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
WHERE ThingId=76
EXEC(@DynSQL)
SELECT pc.*
FROM sys.system_internals_partition_columns pc
JOIN sys.partitions p on p.partition_id=pc.partition_id
WHERE p.object_id = object_id('Thing')
AND pc.is_dropped=1
EXEC sp_spaceused 'Thing'
ALTER INDEX PK ON Thing REBUILD;
SELECT @DynSQL = 'DBCC TRACEON (3604);
DBCC PAGE(0, ' + LEFT(file_id,10) + ', ' + LEFT(page_id,10) + ', 3);
DBCC TRACEOFF(3604); '
FROM Thing
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
WHERE ThingId=76
EXEC(@DynSQL)
SELECT pc.*
FROM sys.system_internals_partition_columns pc
JOIN sys.partitions p on p.partition_id=pc.partition_id
WHERE p.object_id = object_id('Thing')
AND pc.is_dropped=1
EXEC sp_spaceused 'Thing'
DROP TABLE Thing