SQL Server Data Compression Tests

What is the impact of "SQL Server Data Compression" on query performance for a typical large Table?

Using attached SQL, following results achieved:

/** 

Column Compression Tests 

SQL Server 2012 EE

20141112

Reference: 

http://msdn.microsoft.com/en-us/library/hh710070(v=sql.110).aspx

http://www.mssqltips.com/sqlservertip/3187/demonstrating-the-effects-of-using-data-compression-in-sql-server/

**/

/*** 1. Find Sample Data *******/

----4,567,526

--SELECT count(*) 

--FROM adventureworksstage.dbo.MetalTransactions

----5979889

SELECT count(*) 

from adventureworks.Metal_and_Process.Orders

--SELECT top 10 *

--from adventureworks.Metal_and_Process.Orders

SELECT *

into adventureworksstage..Orders_Heap_20141112

from adventureworks.Metal_and_Process.Orders

--SELECT *

--from adventureworksstage..Orders_Heap_20141112

--where [name] = 'Bazinga!'

/** 2. Run Query Without Compression Capture IO Stats **********/

use adventureworksStage

go

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS 

GO

 SET STATISTICS io ON 

 GO

 SET STATISTICS time ON

 GO

SELECT *

from adventureworksstage..Orders_Heap_20141112

where [name] like 'Delivery Required Loading %'

/*** 3. Stats and get AVG ******/

Table 'Orders_Heap_20141112'. Scan count 1, logical reads 16263, physical reads 0, read-ahead reads 21256, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 1172 ms,  elapsed time = 1928 ms.

Table 'Orders_Heap_20141112'. Scan count 1, logical reads 16263, physical reads 0, read-ahead reads 21256, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 1093 ms,  elapsed time = 1938 ms.

Table 'Orders_Heap_20141112'. Scan count 1, logical reads 16263, physical reads 0, read-ahead reads 21256, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 1109 ms,  elapsed time = 2016 ms.

Table 'Orders_Heap_20141112'. Scan count 1, logical reads 16263, physical reads 0, read-ahead reads 21256, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 1297 ms,  elapsed time = 1990 ms.

MEDIAN 

CPU time = 1167.75 (1.2 sec)

elapsed time = 1968 (2 sec)

logical reads = 16263

/** 4. Test Column Compression **/

SELECT top 10 *

into adventureworksstage..Orders_Heap_20141112_0

from adventureworksstage..Orders_Heap_20141112

ALTER TABLE adventureworksstage..Orders_Heap_20141112_0 REBUILD WITH ( DATA_COMPRESSION = ROW ) 

GO

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS 

GO

drop table adventureworksstage..Orders_Heap_20141112

drop table adventureworksstage..Orders_Heap_20141112_0