Looking at that data page, we can see that SQL has added a UNIQUIFIER column. Now this is standard SQL behaviour, SQL does this to all non-unique clustered indexes whether they are on a partitioned table or not.
But also look at the CreatedDate column. It’s after the ID column on the page. If this was a non-partitioned table, we would see that after ColA & ColB (the order the columns are on the table). This has happened because SQL has implicitly added the partitioning key into the index definition, which has changed the physical order of the data on the page.
Read the whole thing.
he benefits of having an index are well known, you can get the same results by reading a smaller amount of data so the improvement in performance can be from several minutes to seconds or even less.
That sounds awesome and it certainly is and there are people out there making a living of it, so it’s a huge deal for sure.
But it’s not always like that, and things can go wrong very easily and make all these shiny indexes just a pile of useless burden.
Let me show you some examples, where we can see our indexes in use, but also how they can be ignored by the query processor and become totally useless. I’m going to use the Microsoft sample database [WideWorldImporters] so you can follow along if you want.
Read on to learn more.
There is no “default” ordering that a query will fall back on outside of an ORDER BY clause.
- Results may come back in the order of the clustered index, or they may not
- Even if results come back in the order of the clustered index on one run of the query, they may not come back in the same order if you run it again
If you need results to come back in a specific order, you must be explicit about it in the ORDER BY clause of the query.
Click through for a demo proving these two points.
As you can see from above, none of the names gave a complete indication of what the index encompassed. The first one did indicate it was a Non Clustered Index so that was good, but includes the date which to me is not needed. At least I knew it was not a Clustered Index. The second index did note it is a “Covering Index” which gave some indication that many columns could be included I also know it was created with the Data Tuning Advisor due to the dta prefix. The third index was also created with dta but it was left with the default dta naming convention. Like the first one I know the date it was created but instead of the word Cover2, I know there are 16 key columns are noted by the “K#” and regular numbers tell me those are included columns. However, I still have no idea exactly what these numbers denote without looking deeper into the index. The last index is closer to what I like however the name only tells me one column name when in fact it encompasses five key columns and two included columns.
I absolutely love seeing lots and lots of “_dta_” indexes; it’s a sign that I have a long day ahead of me.
Do Disabled Indexes Affect Missing Index Recommendations?
I’m so glad you asked! Let’s take a look.
Read on to learn if Betteridge’s law of headlines holds.
Index fragmentation removal and prevention has long been a part of normal database maintenance operations, not only in SQL Server, but across many platforms. Index fragmentation affects performance for a lot of reasons, and most people talk about the effects of random small blocks of I/O that can happen physically to disk based storage as something to be avoided. The general concern around index fragmentation is that it affects the performance of scans through limiting the size of read-ahead I/Os. It’s based on this limited understanding of the problems that index fragmentation cause that some people have begun circulating the idea that index fragmentation doesn’t matter with Solid State Storage devices (SSDs) and that you can just ignore index fragmentation going forward.
However, that is not the case for a number of reasons. This article will explain and demonstrate one of those reasons: that index fragmentation can adversely impact execution plan choice for queries. This occurs because index fragmentation generally leads to an index having more pages (these extra pages come from page split operations, as described in this post on this site), and so the use of that index is deemed to have a higher cost by SQL Server’s query optimizer.
Let’s look at an example.
Check out the example, but definitely read the comments as there are some good conversations in there.
Just like in the root page and the intermediate pages, the FirstName and RowID columns are present.
Also in the leaf: CharCol, our included column appears! It was not in any of the other levels we inspected, because included columns only exist in the leaf of a nonclustered index.
Kendra does a great job of explaining the topic.
This group of DMVs records every scan and large key lookups. When the optimizer declares that there isn’t an index to support a query request it generally performs a scan. When this happens a row is created in the missing index DMV showing the table and columns that were scanned. If that exact same index is requested a second time, by the same query or another similar query, then the counters are increased by 1. That value will continue to grow if the workload continues to call for the index that doesn’t exist. It also records the cost of the query with the table scan and a suspected percentage improvement if only that missing index had existed. The below query calculated those values together to determine a value number.
Click through for sample scripts for this and the index usage stats DMV. The tricky part is to synthesize the results of these DMVs into the minimum number of viable indexes. Unlike the optimizer—which is only concerned with making the particular query that ran faster—you have knowledge of all of the queries in play and can find commonalities.
In the previous blog post In-Memory OLTP Indexes – Part 1: Recommendations, we gave you an update on the latest features of In-Memory OLTP technology. We also summarized the key characteristics of memory-optimized indexes and shared some guidelines and recommendations on how to best choose and configure an index for your memory-optimized table. At this point, if you haven’t read through the previous blog post, we strongly recommend you do so. In this blog post we continue onwards; take the learnings from the previous blog (Part 1) and using some sample examples, apply them in practice. The learnings from this blog post (Part 2) will be particularly useful if you are experiencing query performance issues with memory-optimized tables; either after migration from disk-based tables or in general, with your production workload leveraging memory-optimized tables.
To summarize this blog post covers the following:
Common mistakes and pitfalls to avoid when working with memory-optimized indexes.
Best practices to follow when configuring your memory-optimized indexes for optimal performance.
Troubleshooting and Mitigating your query performance issues with memory-optimized indexes.
Monitoring your query performance with memory-optimized indexes.
There’s a lot of detail in this post, and tuning these types of indexes isn’t quite the same as normal, disk-based indexes.
Larger numbers of indices create exponentially more query plan possibilities. When too many choices exist, the Optimizer will give up partway through and just pick the best plan thus far. As more indices are added the problem worsens and compilation times, i.e., processor times, increase to a point. This can be illustrated best by reviewing an actual customer example. In this case, one table had 144 indices attached to it and several others had between 20 and 130 indices. The queries were quite complex with as many as fifteen joins, many of which were outer joins. Query and index tuning were impossible because query performance was often counterintuitive and sometimes nonsensical. Adding an index that addressed a specific query need often made the query run worse one time and better the next. Note: Cached query plan issues, e.g., parameter sniffing or plan reuse were not problems in this case. The only solution was to tear down ENTIRE indexing structure and rebuild it with SQL Server’s guidance and nine days’ worth of production queries. Table 5 summarizes the results of the index restructuring project. The performance of 98 percent of the queries was comparable to or better than it was when the large numbers of indices were present.
Don’t be that company.