약 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