Most people are familiar with the phrase, "this will kill two birds with one stone". If you're not, the phase refers to an approach that addresses two objectives in one action. (Unfortunately, the expression itself is rather unpleasant, as most of us don't want to throw stones at innocent animals!)
Today I'm going to cover some basics on two great features in SQL Server: the Columnstore index (available only in SQL Server Enterprise) and the SQL Query Store. Microsoft actually implemented the Columnstore index in SQL 2012 Enterprise, though they've enhanced it in the last two releases of SQL Server. Microsoft introduced the Query Store in SQL Server 2016. So, what are these features and why are they important? Well, I have a demo that will introduce both features and show how they can help us.
Before I go any further, I also cover this (and other SQL 2016 features) in my CODE Magazine article on new features SQL 2016.
As a basic introduction, the Columnstore index can help speed up queries that scan/aggregate over large amounts of data, and the Query Store tracks query executions, execution plans, and runtime statistics that you'd normally need to collect manually. Trust me when I say, these are great features.
For this demo, I'll be using the Microsoft Contoso Retail Data Warehouse demo database. Loosely speaking, Contoso DW is like "a really big AdventureWorks", with tables containing millions of rows. (The largest AdventureWorks table contains roughly 100,000 rows at most). You can download the Contoso DW database here: https://www.microsoft.com/en-us/download/details.aspx?id=18279. Contoso DW works very well when you want to test performance on queries against larger tables.
Contoso DW contains a standard data warehouse Fact table called FactOnLineSales, with 12.6 million rows. That's certainly not the largest data warehouse table in the world, but it's not child's play either.
Suppose I want to summarize product sales amount for 2009, and rank the products. I might query the fact table and join to the Product Dimension table and use a RANK function, like so:
Here's a partial result set of the top 10 rows, by Total Sales.
On my laptop (i7, 16 GB of RAM), the query takes anywhere from 3-4 seconds to run. That might not seem like the end of the world, but some users might expect near-instant results (the way you might see near-instant results when using Excel against an OLAP cube).
The only index I currently have on this table is a clustered index on a sales key. If I look at the execution plan, SQL Server makes a suggestion to add a covering index to the table:
Now, just because SQL Server suggests an index, doesn't mean you should blindly create indexes on every "missing index" message. However, in this instance, SQL Server detects that we are filtering based on year, and using the Product Key and Sales Amount. So, SQL Server suggests a covering index, with the DateKey as the index key field. The reason we call this a "covering" index is because SQL Server will "bring along the non-key fields" we used in the query, "for the ride". That way, SQL Server doesn't need to use the table or the clustered index at all – the database engine can simply use the covering index for the query. Covering indexes are popular in certain data warehousing and reporting database scenarios, though they do come at a cost of the database engine maintaining them.
Note: Covering indexes have been around for a long time, so I haven't yet covered the Columnstore index and the Query Store.
So, I will add the covering index:
If I re-execute the same query I ran a moment ago (the one that aggregated the sales amount for each product), the query sometimes seems to run about a second faster, and I get a different execution plan, one that uses an Index Seek instead of an Index Scan (using the date key on the covering index to retrieve sales for 2009).
So, prior to the Columnstore Index, this could be one way to optimize this query in much older versions of SQL Server. It runs a little faster than the first one, and I get an execution plan with an Index Seek instead of an Index Scan.
However, there are some issues:
- The two execution operators "Index Seek" and "Hash Match (Aggregate)" both essentially operate "row by row". Imagine this in a table with hundreds of millions of rows.
- Related, think about the contents of a fact table: in this case, a single date key value and/or a single product key value might be repeated across hundreds of thousands of rows (remember, the fact table also has keys for geography, promotion, salesman, etc.) So, when the "Index Seek" and "Hash Match" work row by row, they are doing so over values that might be repeated across many other rows.
This is normally where I'd segue to the SQL Server Columnstore index, which offers a scenario to improve the performance of this query in amazing ways. But before I do that, let's go back in time. Let's go back to the year 2010, when Microsoft introduced an add-in for Excel known as PowerPivot. Many people probably remember seeing demos of PowerPivot for Excel, where a user could read millions of rows from an outside data source into Excel. PowerPivot would compress the data, and provide an engine to create Pivot Tables and Pivot Charts that performed at amazing speeds against the compressed data. PowerPivot used an in-memory technology that Microsoft termed "VertiPaq". This in-memory technology in PowerPivot would basically take duplicate business key/foreign key values and compress them down to a single vector. The in-memory technology would also scan/aggregate these values in parallel, in blocks of several hundred at a time. The bottom line is that Microsoft baked a large amount of performance enhancements into the VertiPaq in-memory feature for us to use, right out of the proverbial box.
Why am I taking this little stroll down memory lane? Because in SQL Server 2012, Microsoft implemented one of the most important features in the history of their database engine: the Columnstore index. The index is really an index in name only: it is a way to take a SQL Server table and create a compressed, in-memory columnstore that compresses duplicate foreign key values down to single vector values. Microsoft also created a new buffer pool to read these compressed vector values in parallel, creating the potential for huge performance gains.
So, I'm going to create a columnstore index on the table, and I'll see how much better (and more efficiently) the query runs, versus the query that runs against the covering index. So, I'll create a duplicate copy of FactOnlineSales (I'll call it FactOnlineSalesDetail_NCCS), and I'll create a columnstore index on the duplicated table – that way I won't interfere with the original table and the covering index in any way.
Next, I'll create a columnstore index on the new table:
Note several things:
- I've specified several foreign key columns, as well as the Sales Amount. Remember that a columnstore index is not like a traditional row-store index. There is no "key". We are simply indicating which columns SQL Server should compress and place in an in-memory columnstore.
- To use the analogy of PowerPivot for Excel – when we create a columnstore index, we're telling SQL Server to essentially do the same thing that PowerPivot did when we imported 20 million rows into Excel using PowerPivot
So, I'll re-run the query, this time using the duplicated FactOnlineSalesDetail_NCCS table that contains the columnstore index.
This query runs instantly – in less than a second. And I can also say that even if the table had hundreds of millions of rows, it would still run at the proverbial "bat of an eyelash". We could look at the execution plan (and in a few moments, we will), but now it's time to cover the Query Store feature.
Imagine for a moment, that we ran both queries overnight: the query that used the regular FactOnlineSales table (with the covering index) and then the query that used the duplicated table with the Columnstore index. When we log in the following morning, we'd like to see the execution plan for both queries as they took place, as well as the execution statistics. In other words, we'd like to see the same statistics that we'd be able to see if we ran both queries interactively in SQL Management Studio, turned in TIME and IO Statistics, and viewed the execution plan right after executing the query.
Well, that's what the Query Store allows us to do – we can turn on (enable) Query Store for a database, which will trigger SQL Server to store query execution and plan statistics so that we can view them later.
So, I'm going to enable the Query Store on the Contoso database with the following command (and I'll also clear out any caching):
Then I'll run the two queries (and "pretend" that I ran them hours ago):
Now let's pretend they ran hours ago. According to what I said, the Query Store will capture the execution statistics. So how do I view them? Fortunately, that's quite easy. If I expand the Contoso DW database, I'll see a Query Store folder.
The Query Store has tremendous functionality and I'll try to cover much of it in subsequent blog posts. But for right now, I want to view execution statistics on the two queries, and specifically examine the execution operators for the columnstore index.
So I'll right-click on the Top Resource Consuming Queries and run that option. That gives me a chart like the one below, where I can see execution duration time (in milliseconds) for all queries that have been executed. In this instance, Query 1 was the query against the original table with the covering index, and Query 2 was against the table with the columnstore index. The numbers don't lie – the columnstore index outperformed the original table/covering index by a factor of almost 7 to 1!!!
I can change the metric to look at memory consumption instead. In this case, note that query #2 (the columnstore index query) used far more memory. This demonstrates clearly why the columnstore index represents "in-memory" technology – SQL Server loads the entire columnstore index in memory, and uses a completely different buffer pool with enhanced execution operators to process the index.
OK, so we have some graphs to view execution statistics – can we see the execution plan (and execution operators) associated with each execution? Yes, we can! If you click on the vertical bar for the query that used the columnstore index, you'll see the execution plan below.
The first thing we see is that SQL Server performed a columnstore index scan, and that represented nearly 100% of the cost of the query. You might be saying, "Wait a minute, the first query used a covering index and performed an index seek – so how can a columnstore index scan be faster?"
That's a legitimate question, and fortunately there's an answer. Even when the first query performed an index seek, it still executed "row by row". If I put the mouse over the columnstore index scan operator, I see a tooltip (like the one below), with one important setting: the Execution Mode is BATCH (as opposed to ROW, which is what we had with the first query using the covering index). That BATCH mode tells us that SQL Server is processing the compressed vectors (for any foreign key values that are duplicated, such as the product key and date key) in batches of almost 1,000, in parallel. So SQL Server is still able to process the columnstore index much more efficiently.
Additionally, if I place the mouse over the Hash Match (Aggregate) task, I also see that SQL Server is aggregating the columnstore index using Batch mode (although the operator itself represents such a tiny percent of the cost of the query)
Finally, you might be asking, "OK, so SQL Server compresses the values in the data, treats the values as vectors, and read them in blocks of almost a thousand values in parallel – but my query only wanted data for 2009. So is SQL Server scanning over the entire set of data?" Again, a good question. The answer is, "Not really". Fortunately for us, the new columnstore index buffer pool performs another function called "segment elimination". Basically, SQL Server will examine the vector values for the date key column in the columnstore index, and eliminate segments that are outside the scope of the year 2009.
I'll stop here. In subsequent blog posts I'll cover both the columnstore index and Query Store in more detail. Essentially, what we've seen here today is that the Columnstore index can significantly speed up queries that scan/aggregate over large amounts of data, and the Query Store will capture query executions and allow us to examine execution and performance statistics later.