Basic IO Test with SQL Server’s Performance Oriented Features

Microsoft has been releasing at-least 1 major performance boosting feature as part of its Enterprise Editions in recent years. Following are a few:

SQL Server 2008: Row level and Page level compressions which reduce the IO flow from the disk. This needs to be tested properly to make sure that the increased CPU load to decompress the data is really worth it as compared to the decreased IO load.

SQL Server 2012: ColumnStore Indexes which improves the performance of the queries drastically as it is based on the implementation of Column wise data storage instead of the traditional Row based storage of the data. The disadvantage was that the Table needs to be read-Only if they are supposed to have a ColumnStore indexes. However, there was still a work around by switching the partitions In and Out of these Read-Only tables

SQL Server 2014: Updateable ColumnStore Indexed Tables and also InMemory OLTP Engine to keep the data in Memory without the fear of them being pages out if it gets old.

As part of a comparative test I wanted to see on how these features behave with a very simple query for experimental purpose.

Note: The “AdventureWorks2014” DB was used for testing purpose. Table: “SalesOrderHeader” Deciding Column: “OrderDate”

All the 5 Scenarios are exclusive to each other and were run isolation.

Following query will be run under each scenario:

Select COUNT(*) FROM Sales.SalesOrderHeader SOH WITH (NOLOCK) Where OrderDate > '2013-01-01'

Scenario 1: The table is not having any Index on the deciding column

Table 'SalesOrderHeader'. Scan count 1, logical reads 688, physical reads 0, read-ahead reads 693, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Scenario 2: The table has Page compressions applied to it

Table 'SalesOrderHeader_Mod'. Scan count 1, logical reads 330, physical reads 0, read-ahead reads 344, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Scenario 3: The table has a NonClustered Index on the deciding column

Table 'SalesOrderHeader'. Scan count 1, logical reads 61, physical reads 0, read-ahead reads 59, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Scenario 4: The table has a ColumnStore NonClustered Index on the deciding column

Table 'SalesOrderHeader'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 21, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Scenario 5: The table is created InMemory hence the IO Cost in the execution plan can be seen as Nil as all the data is present in Memory itself.

Summary:

Test Case No	Scenario	IO Pages Read from the disk	Read – Ahead Reads
1	The table is not having any Index on the deciding column	688	693
2	The table has Page compressions applied to it	330	344
3	The table has a NonClustered Index on the deciding column	61	59
4	The table has a ColumnStore NonClustered Index on the deciding column	13	21
5	The table is created with InMemory Optimization and	0	0

Observation:

While working with some of the data, I did see that in some tests NonClustered Index was performing better than the ColumnStore Indexes which made me think twice and search on it more as to in which cases do ColumnStore Indexes perform much better any day. It did make me think because even the NonClustered Index just contain the columns like the ColumnStore Indexes then in which cases could one outweigh the other. I finally tumbled on the following blog post which said that “NonClustered indexes were still going to be more effective for specific types of queries (for example – singleton operations, smaller range scans, etc.).”

http://www.sqlskills.com/blogs/joe/comparing-query-performance-when-using-an-ideal-nonclustered-index-versus-columnstore-index/

ColumnStore Indexes perform way better that NonClustered Indexes when the data is huge and ColumnStore index execution leveraged parallelism as