A Fine Slice Of SQL Server
Erik Darling shows us the nuances of using a new DMV in SQL Server 2019:
When dm_db_missing_index_group_stats_query got documented, I was really happy. After all, this has been a peeve of mine for ages.
“Wow look at all these missing index requests. Where’d they come from?”
So this is neat! And it’s better than nothing, but there are some quirks.
Read on for more detail.
Comments closedDeepthi Goguri explains what sorts of fragmentation can occur on an index in SQL Server:
Logical Fragmentation occurs when the logical order of the leaf level pages (logical order meaning the next key values in order) no longer the continuous page to the next physical data file page. Because of these pages which are out of order will affect the read ahead mechanism and the scan performance. Because of this logical fragmentation, read ahead have to do smaller read ahead reads.
If the logical fragmentation pages are already in the memory than the read ahead mechanism will not be affected in that case. Logical fragmentation will cause the problem for bigger indexes and not for the smaller ones usually (smaller indexes having pages 1000-5000 pages). You can monitor the amount of the index logical fragmentation by using the DMV sys.dm_db_index_physical_stats.
Read on to learn more about logical fragmentation, as well peers extent fragmentation and low page density.
Comments closedGail Shaw gives us an introduction to columnstore indexes:
Columnstores are… different.
The first, and I would say most important thing to realise about columnstore indexes is that they don’t have keys. These are not seekable indexes. Any access to a columnstore index is going to be a scan.
Instead of storing the rows together on a page, a columnstore index instead stores column values together. The rows in the table are divided into chunks of max a million rows, called a row group, and the columns are then stored separately, in what are called segments. A segment will only ever contain one column’s values.
Read the whole thing.
Comments closedErik Darling continues a multi-state indexing spree:
Index union is a little bit different from index intersection. Rather than joining two indexes together, their result sets are concatenated together.
Just like you’d see if you wrote a query with union or union all. Crazy, huh?
As with index intersection, the optimizer has a choice between concatenation and merge join concatenation, and lookups back to the clustered index are possible.
These I see even less commonly than index intersections—so often, the optimizer decides simply to scan one index and the solution is to break the queries out into two with UNION ALL.
Erik Darling makes a fairly rare multi-index sighting:
Notice! Both of our nonclustered indexes get used, and without a lookup are joined together.
Because the predicates are of the inequality variety, the join columns are not ordered after each seek. That makes a hash join the most likely join type.
I’ve always had this belief that there are probably more cases in which multi-index solutions are useful than the SQL Server optimizer gives us. This belief may be irrational.
Comments closedErik Darling has an interesting scenario for us:
This post isn’t meant to dissuade you from using EXISTS or NOT EXISTS when writing queries. In fact, most of the time I think they make a lot of sense.
But weird things can happen along the way, especially if you don’t have supporting indexes, or if supporting indexes aren’t chosen by the optimizer for various reasons.
In this post, I’m going to show you a query plan pattern that can occur in semi-join plans, and what you can do about it.
Click through for the problem and the solution. Me? I don’t like semi-joins on principle. Either join or don’t join; give me none of these cowardly half-measures. I’m not sure what to think about anti-semi-joins because I’m apparently anti semi-join for the purposes of this belabored joke, but I’m a bit suspicious of them as well.
Comments closedErik Darling wants you to embrace the healing power of AND:
This is one of my least favorite query patterns, because even with appropriate indexes, performance often isn’t very good without additional interventions.
Without indexes in place, or when “indexes aren’t used”, then the query plans will often look like one of these.
Maybe not always, but there are pretty common.
It’s something that I do wish the optimizer could be smarter about. One important thing to note in Erik’s demo: the OR clause is on two different columns, so SELECT x.Col1 FROM dbo.TblX x WHERE x.ID = 8 OR x.ID = 7 works fine, but WHERE x.ID = 8 OR x.SomethingElse = 14 is liable to cause performance issues on a large enough table.
Deepthi Goguri continues a series on physical join operators:
In the Part1 of decoding the physical join operators, we learned about the different types of physical operators: Nested loops, Merge joins and Hash joins. We have seen when they are useful and how to take advantage of each for the performance of our queries. We have also seen when they are useful and when they needs to be avoided.
In this part, we will know more about these operators and how the indexes really help these operator to perform better so the queries can execute faster.
Read on to see how to define indexes for each of the three physical operators.
Comments closedErik Darling has a two-fer here. First, window functions and parallelism:
When windowing functions don’t have a Partition By, the parallel zone ends much earlier on than it does with one.
That doesn’t mean it’s always slower, though. My general experience is the opposite, unless you have a good supporting index.
But “good supporting index” is for tomorrow. You’re just going to have to deal with that.
Second, columnstore behavior with respect to window functions:
Not only is the parallel version of the row mode plan a full second slower, but… look at that batch mode plan.
Look at it real close. There’s a sort before the Window Aggregate, despite reading from the same nonclustered index that the row mode plan uses.
But the row mode plan doesn’t have a Sort in it. Why?
Check out both posts.
Comments closedErik Darling tests an assertion:
After my smash hit double diamond post about index tuning, I got a question questioning my assertion that compressed indexes are also compressed in the buffer pool.
Well, this should be quick. A quick question. Eighty hours later.
Click through for the test.
Comments closed