Data Compression of MS SQL Server
DZone's Guide to
Data Compression of MS SQL Server
Join the DZone community and get the full member experience.
Join For FreeMaintain Application Performance with real-time monitoring and instrumentation for any application. Learn More!
Introduction
The performance of MS SQL Server depends on Disk I/O Efficiency. If the data is in MS SQL server is compressed the performance increase as a fewer pages is required.
Microsoft SQL Server 2008 Enterprise Edition supports the Data Compression mechanism that we discuss in this article.
Where we can Configure it
The data compression can be configured on Table, Clustered – Index, Non Clustered – Index, Index Views and Partition of Table or on Index.
Levels of Data Compression
The Data Compression can be implemented on two levels
1.
Row
2.
Page
Row level Data Compressions
The main objective is more rows can fit into a single data page.
1.
The overhead of metadata of the records is reduced.
2. Row-level data compression is turning fixed length data types into variable length data types, freeing up empty space.
Let's take an example to understand the point no-2
Suppose 120 are stored in an INTEGER data type. We all know that INTEGER data type takes 4 bytes to store, So the value 120 also take 4 bytes to store.
We also know that INTEGER value between 0 to 255 can store on 1 Bytes. So after compression 3 bytes release and our value 120 takes 1 byte to store.
3.
It also has the ability to ignore zero and null values, saving additional space.
Page level Data Compression
It begins with row-level data compression. With his it takes two additional compression features called prefix and dictionary compression.
Prefix compression
In this technique finds duplicate prefixes on a page for each column, and replaces each duplicate with a small reference number.
Dictionary compressionIn this technique finds duplicate values on a page, collects them into a dictionary stored after the page header but before the data rows, and replaces the duplicate values with their corresponding offsets in the dictionary.
Example of Data Compression In MS SQL Server
Step – 1 [ Create The Base Table ]
IF OBJECT_ID(N'dbo.tbl_EMPLOYEE', N'U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_EMPLOYEE];
END
GO
CREATE TABLE [dbo].[tbl_EMPLOYEE]
(
EMPID INT,
EMPNAME VARCHAR(50)
)
GO
Step – 2 [ Insert Some Records ]
INSERT INTO [dbo].[tbl_EMPLOYEE]
(EMPID, EMPNAME)
VALUES(1, 'Joydeep Das');
GO 5000
Step - 3 [ Check the Space used by Original Table ]
EXEC sp_spaceused tbl_EMPLOYEE
|
Name
|
Rows
|
Reserved
|
Data
|
Index_size
|
Unused
|
|
tbl_EMPLOYEE
|
5000
|
200KB
|
152KB
|
8KB
|
40KB
|
Step – 4 [ Now we Used Data Compression ROW ] ALTER TABLE [dbo].[tbl_EMPLOYEE] REBUILD WITH (DATA_COMPRESSION = ROW); GO Step – 5 [ Now Again Check the Space Used After Data Compression ROW ] EXEC sp_spaceused tbl_EMPLOYEE
|
Name
|
Rows
|
Reserved
|
Data
|
Index_size
|
Unused
|
|
tbl_EMPLOYEE
|
5000
|
144KB
|
120KB
|
8KB
|
16KB
|
Step – 6 [ Now we Used Data Compression PAGE ] ALTER TABLE [dbo].[tbl_EMPLOYEE] REBUILD WITH (DATA_COMPRESSION = PAGE); GO Step – 7 [ Now Again Check the Space Used After Data Compression PAGE ] EXEC sp_spaceused tbl_EMPLOYEE
|
Name
|
Rows
|
Reserved
|
Data
|
Index_size
|
Unused
|
|
tbl_EMPLOYEE
|
5000
|
80KB
|
64KB
|
8KB
|
8KB
|
Step – 8 [ Now We Comare all Again ]
Original
Without Compression
|
Name
|
Rows
|
Reserved
|
Data
|
Index_size
|
Unused
|
|
tbl_EMPLOYEE
|
5000
|
200KB
|
152KB
|
8KB
|
40KB
|
Compression ROW
|
Name
|
Rows
|
Reserved
|
Data
|
Index_size
|
Unused
|
|
tbl_EMPLOYEE
|
5000
|
144KB
|
120KB
|
8KB
|
16KB
|
Compression PAGE
|
Name
|
Rows
|
Reserved
|
Data
|
Index_size
|
Unused
|
|
tbl_EMPLOYEE
|
5000
|
80KB
|
64KB
|
8KB
|
8KB
|
|
Compression Type
|
Data
|
|
NO Compression (Original)
|
152KB
|
|
Compression ROW
|
120KB
|
|
Compression PAGE
|
64KB
|
Collect, analyze, and visualize performance data from mobile to mainframe with AutoPilot APM. Learn More!
Like This Article? Read More From DZone
Topics:
Published at DZone with permission of Joydeep Das , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}