SQL Server Data Compression Page vs Row

SQL Server Page Data Compression vs Row Data Compression

                                                      

                                                                       Today one of my friend ask me about SQL Server Data Compression, After that I have discuss with him about SQL Server Data Compression such as what are the advantages and disadvantages, how it's working like that... For this article I'm going to discuss about SQL Server Data Compression ,how is working and advantages and disadvantages.

Ok! First we will look, what is SQL Server Data Compression?

                                                                                               Data Compression is available only in the Enterprise Edition of SQL Server(And of course developer edition also) You can use the data compression feature to help compress the data inside a database. And this will support  for tables and indexes only. In addition to saving space, data compression can help to improve performance of I/O because after apply data compression it will be less data pages.

Page compression algorithm is reduce data duplication method which means if some records will duplicate within the page then SQL Server remove such duplicate values within a data page by replacing some kind of special values.

And row compression use remove unused spaced(bytes) in column.At the same time you can not use PAGE and ROW compression both, but if page compression enabled then automatically includes row compression. 

Normal Method,

USE [MSDN]
GO
--Drop Table
IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblWithOutDataCompression]') AND [type] in (N'U'))
DROP TABLE [dbo].[tblWithOutDataCompression];
GO
--Create Table
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblWithOutDataCompression]') AND [type] in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblWithOutDataCompression](
[rowID] [bigint] NULL,
[colName] [varchar](2000) NULL,
[colcity] [varchar](2000) NULL,
[colAddress] [varchar](4000) NULL
) ON [PRIMARY]
END
GO
--Create Clustered Index
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE [object_id] = OBJECT_ID(N'[dbo].[tblWithOutDataCompression]') AND name = N'PK_ClusteredIndex_tblWithOutDataCompression')
CREATE CLUSTERED INDEX [PK_ClusteredIndex_tblWithOutDataCompression] ON [dbo].[tblWithOutDataCompression]
(
[rowID] ASC
) ON [PRIMARY]
GO
INSERT INTO [dbo].[tblWithOutDataCompression]([rowID],[colName],[colcity],[colAddress])
SELECT bucketid,cacheobjtype,objtype,'SQL SERVER DATA COMPRESSION' FROM sys.syscacheobjects
WHERE
(LEN([cacheobjtype])<2000)  
AND (LEN([objtype])<2000) 
ORDER BY bucketid;
GO
DBCC IND('MSDN',tblWithOutDataCompression,-1);
GO
EXEC sp_spaceused 'tblWithOutDataCompression';

GO

After execute above SQL Statement you will notice it will returns no of data pages created and table space allocation.

Row compression,

USE [MSDN]
GO
--Drop Table
IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblDataCompressionROW]') AND type in (N'U'))
DROP TABLE [dbo].[tblDataCompressionROW];
GO
--Create Table
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblDataCompressionROW]') AND [type] in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblDataCompressionROW](
[rowID] [bigint] NULL,
[colName] [varchar](2000) NULL,
[colcity] [varchar](2000) NULL,
[colAddress] [varchar](4000) NULL
) ON [PRIMARY]
END
GO
--Create Clustered Index
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE [object_id] = OBJECT_ID(N'[dbo].[tblDataCompressionROW]') AND name = N'PK_ClusteredIndex_tblDataCompressionROW')
CREATE CLUSTERED INDEX [PK_ClusteredIndex_tblDataCompressionROW] ON [dbo].[tblDataCompressionROW]
(
[rowID] ASC
)WITH (DATA_COMPRESSION = ROW) ON [PRIMARY]
GO
INSERT INTO [dbo].[tblDataCompressionROW]([rowID],[colName],[colcity],[colAddress])
SELECT bucketid,cacheobjtype,objtype,'SQL SERVER DATA COMPRESSION' FROM sys.syscacheobjects
WHERE
(LEN([cacheobjtype])<2000)  
AND (LEN([objtype])<2000) 
ORDER BY bucketid;
GO  
DBCC IND('MSDN',tblDataCompressionROW,-1);
GO
EXEC sp_spaceused 'tblDataCompressionROW';

GO

Well! After execute above row compression SQL Statement then you will noticed some small amount of number of data(8K) pages reduce. And allocation size also.(Note : if you have very less amount of data in above tables then you will not noticed any space gain.)

Page compression,

USE [MSDN]
GO
--Drop Table
IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblDataCompressionPAGE]') AND [type] in (N'U'))
DROP TABLE [dbo].[tblDataCompressionPAGE];
GO
--Create Table
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblDataCompressionPAGE]') AND [type] in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblDataCompressionPAGE](
[rowID] [bigint] NULL,
[colName] [varchar](2000) NULL,
[colcity] [varchar](2000) NULL,
[colAddress] [varchar](4000) NULL
) ON [PRIMARY]
END
GO
--Create Clustered Index
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE [object_id] = OBJECT_ID(N'[dbo].[tblDataCompressionPAGE]') AND name = N'PK_ClusteredIndex_tblDataCompressionPAGE')
CREATE CLUSTERED INDEX [PK_ClusteredIndex_tblDataCompressionPAGE] ON [dbo].[tblDataCompressionPAGE]
(
[rowID] ASC
)WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY]
GO
INSERT INTO [dbo].[tblDataCompressionPAGE]([rowID],[colName],[colcity],[colAddress])
SELECT bucketid,cacheobjtype,objtype,'SQL SERVER DATA COMPRESSION' FROM sys.syscacheobjects
WHERE
(LEN([cacheobjtype])<2000)  
AND (LEN([objtype])<2000) 
ORDER BY bucketid;
GO  
DBCC IND('MSDN',tblDataCompressionPAGE,-1);
GO
EXEC sp_spaceused 'tblDataCompressionPAGE';

GO

Hope After execute above page compression SQL Statement then you will noticed some significant amount of number of data(8K) pages reduce. And allocation size also.Following SQL Statements will help you to find more information about  Data Compression.

USE [MSDN]
GO
SELECT 
    t.[name] AS TableName,
    p.[rows] AS RowCounts,
    SUM(u.[total_pages]) AS TotalPages, 
    SUM(u.[used_pages]) AS UsedPages, 
    (SUM(u.[total_pages]) - SUM(u.[used_pages])) AS UnusedPages
FROM sys.tables t
INNER JOIN      
    sys.indexes i ON t.[object_id] = i.[object_id]
INNER JOIN 
    sys.partitions p ON i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id]
INNER JOIN 
    sys.allocation_units u ON p.[partition_id] = u.[container_id]
GROUP BY t.[name], p.[rows]
ORDER BY t.[name];
GO
EXEC sp_estimate_data_compression_savings 'dbo', 'tblWithOutDataCompression', NULL, NULL, 'ROW';
GO
EXEC sp_estimate_data_compression_savings 'dbo', 'tblWithOutDataCompression', NULL, NULL, 'PAGE';
GO

My personal view is this can be good move if you have more gain for disk IO. If you have limited CPU and memory then, this can be performance issue.Microsoft mention it will be extra CPU resources are required to compress and decompress the data but I hope it will affect tempdb also, depend on the table or index size.Best method is if you test this in your development or test server and then move to production database.
And after apply data compression then if you noticed not gain any page compression then better to remove it.
And I hope this should be monitoring job weekly/monthly/yearly because first 3 months you will notice good IO performance of data compression and after one year you will notice there is no gain, Because your table contains data (last 9 months) can not be compress then this can be disadvantage.
If you need more information hope this Microsoft white paper will help you. - Data Compression: Strategy, Capacity Planning and Best Practices   

     

Cheers...
and Most welcome your comments and ideas...                             

SQL Server Port(Default 1433)

SQL Server Port(Default 1433) 

                                                                       SQL Server Port! Well... some times most of developers Asking "What is SQL Server Port?" If you have multiple instances in same serve, some of developers saying I can't connect this database server(database server connection problems) And some of saying how can I get SQL Server port?(or How to find SQL Server port?). If you are Database Administrator in Software development company, then you will face above these questions most of the time.

First How can I view(or Find) SQL Server port in given Server?        

                                                                                     You can used several method to view this, first I'm going to explain, How to find this in graphical view as follows,

Programs ->  Microsoft SQL Server (2005,2008,2008 R2 or 2012) -> Configuration Tools -> SQL Server Configuration Manager -> SQL Server Network Configuration -> And then Select your SQL Server instance -> TCP/IP 

Hope now you can view or find SQL Server port, which is given SQL Server instances.

Any other Methods - Yes :)

Method 01,
                                Open Task Manager and then Go to the Services Tab as following Picture,

Inside the services tab you will notice PID according to SQL Server service. With using above PID you can view SQL Server port as follows,

 Method 02,
                   If you are not happy with above methods, then you can go with following T-SQL command,

DECLARE @tcp_port nvarchar(5)
EXEC xp_regread@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name = 'TcpPort',
@value = @tcp_port OUTPUT
SELECT @tcp_port

And if you already know the SQL Server port of given server then you can use SSMS to connect that as follows,

<Server or instance name>,<port>   Ex : MyServer,1433

Hope above Article will help you.

Cheers...
and Most welcome your comments and ideas...