Continuation from the previous 52 parts, the whole series can be found at https://www.nikoport.com/columnstore/
This is a very small blog post which will focus on 1 essential change that happened in Service Pack 1 for SQL Server 2014. The only part that I can see is actually reporting one, but for any seasoned SQL Server professional, using performance metrics of the SET STATISTICS command this is an important one to know.
Before Service Pack 1 for SQL Server 2014, every read for Columnstore Indexes (Clustered & Nonclustered) was reported as a logical, physical or read-ahead read, whereby it is long known that Columnstore Indexes are stored in LOBs after being compressed with a number of different compressions.
After applying Service Pack 1 (redacted or the final one), execute any operation against a table, containing Columnstore Indexes, reporting Statistics on the processed amounts of information, like I did in the following statement for a Fact table from the ContosoRetailDW database, for which I created a Clustered Columnstore Index:
set statistics io on select sum([UnitPrice]) as Sales from dbo.FactOnlineSales
Let’s take a look at the reported result:
(1 row(s) affected) Table 'FactOnlineSales'. Scan count 2, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 3590, lob physical reads 0, lob read-ahead reads 2550. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Wait! Where are the logical reads??? Who did it? Why?
Hint: do not forge to scroll the reported statistics to the right, to check everything :)
Solution: we have lost the number of logical reads because we are reading compressed LOB data! In Service Pack 1 for SQL Server 2014 Microsoft has changed the way how they present the information changing from logical reads to lob logical reads, and from physical reads to lob physical reads and naturally from read-ahead reads to lob read-ahead reads !
Take a look at the picture! You can clearly see the logical reads being processed, when I am processing the same table I have being using in Demo’s for a couple of years, where no other SQL Server version have shown these results.
How can we be sure if this is a bug ? If you read the knowledge base article for Service Pack 1 for SQL Server 2014, you will be able to find an obscure Connect Item 3143194, which is not findable in Microsoft Connect to me, but which description is precise to mention that it contains an intended correction for the LOB reads.
From my side I totally agree with this change, and just wish that it would have been implemented for the RTM version of SQL Server, since it will confuse a number of people after applying Service Pack 1. This change is needed, because a lot of people have no idea about the way that Columnstore Indexes are using for the storage.
to be continued with Columnstore Indexes – part 54 (“Thoughts on upcoming improvements in SQL Server 2016”)