Joey D’Antoni points out a potential big performance problem with clustered columnstore indexes:
In the last year or so, with a large customer who makes fairly heavy use of this pattern, I’ve noticed another concern. Sometimes, and I can’t figure out what exactly triggers it, the execution plan generated, will do a seek against the nonclustered index and then do a key lookup against the columnstore as seen below. This is bad for two reasons–first the key lookup is super expensive, and generally columnstores are very large, secondly this key lookup is in row execution mode rather than batch and drops the rest of the execution plan into row mode, thus slowing the query down even further.
Joey also has a UserVoice item as well, so check it out.
Tara Kizer has a warning for people eager to drop “unused” indexes:
About 10 years ago, I decided to drop an unused index on a table that had 2 billion rows. The database was around 7TB in size. We were having storage and performance issues. I thought I could help the system out if I were to drop the index.
4 days after I dropped the index, I got a call from our NOC that CPU utilization was pegged at 100% for an hour so they were reaching out to the on-call DBA to check it out. I logged in and saw a query using A LOT of CPU. Its WHERE clause matched the index I had dropped. The execution plan showed it was scanning the table.
It turned out that I only had 2 weeks of uptime, which didn’t include the 1st of the month. The query that was causing the high CPU utilization was a report that ran on the 1st of each month.
Tara has also provided us with a script to track these details over time, so check that out.
The authors conducted a series of microbenchmarks as follows:
scans with single predicates with varying selectivity to study the trade-off between the range scan of a B+ tree vs a columnstore scan
sort and group-by queries to study the benefit of the sort order supported by B+ trees (columnstores in SQL Server are not sorted).
update statements with varying numbers of updated rows to analyze the cost of updating the different index types
mixed workloads with different combinations of reads and updates
It’s interesting to read an academic paper covering the topic, particularly when you can confirm that it works well in practice too.
Bert Wagner violates Betteridge’s Law of Headlines:
When beginning to learn SQL, at some point you learn that indexes can be created to help improve the performance of queries.
Creating your first few indexes can be intimidating though, particularly when trying to understand what order to put your key columns in.
Today we’ll look at how row store indexes work to understand whether index column order matters.
Despite the flagrant violation, you should check out Bert’s post, as it’s a good one.
Eduardo Pivaral shows how to create a fairly simple indexed view:
Views help our query writing by simplifying writing the same sentences and/or aggregations over and over again, but it has a drawback, the views just store our query definition, but the performance is not improved by using them.
Since SQL Server 2008, the option to create an index over a view was introduced, of course, there are some limitations, but if your view can use them, the performance improvement could be great!
I will show you how to create a simple index over a view
I’ve used indexed views in the past, but they’re a much less common tool in the belt these days.
Ajay Dwivedi shares his rules of thumb for index maintenance on replicated databases:
Like any other DBA, I fell into the trap of using straight maintenance solution using Reorganize operation for Indexes with avg fragmentation with 30% or less with Index Rebuild for avg fragmentation greater than 30%.
Well above approach works fine in common scenarios, but can create problems for servers using transaction log based High Availability technologies, such as AlwaysOn Availability Groups, database mirroring, log shipping, and replication. Both index rebuild and reorganize introduce heavy transaction log activity and generate a large number of log records. This becomes an issue in case of node failover, server with limited storage, database file with restricted growth, wrong file auto growth setting, or database with high VLF counts.
The best option for servers with High Availability is to identify kind of server workload (OLTP/OLAP/mixed), fill factor (based on Page Splits/sec), fragmentation, underlying storage load (random/sequential), Index Scans vs Index Searches, job time frame (low activity outside business hours) etc. After calculating all the above factors, all we need is to have a robust Index Maintenance solution. This is where I find Ola Hallengren’s SQL Server Maintenance Solution a perfect fit.
Ajay uses Ola Hallengren’s solution and gives us the breakdown percentages he uses.
I had a lot of issues when I created my first one, and after discussing with some folks, they had the same issues. I searched for the best blog posts that I could find on the subject, and the one I LOVED the most was here: Arctic DBA. He broke it down so simply, that I finally created my own pseudo installer and I wanted to share it with all of you. Please, bear in mind, these code snippets may fail at anytime due to changes in Azure.
**IMPORTANT**
These next steps assume the following:
You have created/configured your Azure Automation Account and credential to use to execute this runbook.
Read on for a reasonably short Powershell script and a modified version of Ola Hallengren’s index maintenance procedures.
One way to “fix” a poor performing plan is to use an index hint. While we normally have no control over how SQL Server retrieves the data we requested, an index hint forces the query optimizer to use the index specified in the hint to retrieve the data (hence, it’s really more of a “command” than a “hint”).
Sometimes when I feel like I’m losing control I like using an index hint to show SQL Server who’s boss. I occasionally will also use index hints when debugging poor performing queries because it allows me to confirm whether using an alternate index would improve performance without having to overhaul my code or change any other settings.
About the only place I consistently use index hints is with filtered indexes, where the combination of parameter sniffing and inexactitude in filters will convince the optimizer that the filtered index isn’t helpful when it really is.
Drew Furgiuele says “Game over, man, game over!” to indexes:
The premise is simple: it will generate a series of DROP and then CREATE INDEX commands for every index. The process is a little more complex in practice, but at a high level it:
- Creates a special schema to house a temporary object,
- Creates a special stored procedure to run the code,
- Calls said stored procedure,
- Generates a bunch of PRINT statements that serve as the output (along with new line support for readability),
- Cleans up the stored procedure it generated,
- And finally deletes the schema it created.
Nifty.
Click through for the script, as well as a bonus Powershell script. Because hey, it’s only six lines of code.
The original question we wanted to answer was whether we would see a performance difference when a query used the index with all columns in the key, versus the index with most of the columns included in the leaf level. In our first set of tests there was no difference, but in our third and fourth tests there was. It ultimately depends on the query. We only looked at two variations – one had an additional predicate, the other had an ORDER BY – many more exist.
What developers and DBAs need to understand is that there are some great benefits to including columns in an index, but they will not always perform the same as indexes that have all columns in the key. It may be tempting to move columns that are not part of predicates and joins out of the key, and just include them, to reduce the overall size of the index. However, in some cases this requires more resources for query execution and may degrade performance. The degradation may be insignificant; it may not be…you will not know until you test. Therefore, when designing an index, it’s important to think about the columns after the leading one – and understand whether they need to be part of the key (e.g. because keeping the data ordered will provide benefit) or if they can serve their purpose as included columns.
Read the whole thing and be willing to test different approaches.
Kevin Feasel
2018-11-16
Columnstore, Indexing
No Comment