I have had many calls with customers that essentially ended with your I/O subsystem cannot keep up with your workload. The obvious next question was how do we solve that? My answer was usually get a faster IO subsystem or do less IO! Now, SQL 2014 offers another option.
Let’s pretend for a moment that you cannot get faster IO and your problem is data warehouse style table and queries. The key here; not an OLTP workload. If this is the case Clustered Columnstore Index (CCI) in SQL 2014 may be worth investigating.
SQL Server 2012 introduced the non-clustered columnstore index which was met with great fanfare to everyone who tested and the few who were able to use it. A limitation of the non-clustered column store is it rendered the table read only. In SQL 2014 the Clustered Columnstore is updatable.
What you need to know,
To give you a sense of the data compression you may experience, the following is test telemetry data from a Microsoft application that came through the lab, and keep in mind the column data was relatively redundant so there was great opportunity for compression.
Looking at the SSMS table size report below, the extension on the table name indicates the level of compression based on size from a standard row store table to a Clustered Column Store Archive on a table with 1,111,997,989 rows and uncompressed size of 182GB.
*_RS = Row Store
*_RS_PC = Row Store Page Compressed
*_CCI = Clustered Column Store Index
*_CCI_Archive = Clustered Column Store Index with Archival_Compression
The following sample used a query test to get an idea of the potential performance for 1 execution of the query with a cold cache, and each query run on the same server with the same hardware configuration.
(For this test I used a 2 processor 16 core, 128 GB RAM with SQL allocated 112GB, 2 mirrored 10k RPM disks for data file)
Columnstore Indexes Described
Using Clustered Columnstore Index
SQL Server Columnstore Index FAQ
SQL Server Columnstore Performance Tuning
SQL Server 2014 and HP Sets Two World Records for Data Warehousing