A Fine Slice Of SQL Server
To hold onto Shared locks, you’d need to use an isolation level escalation hint, like REPEATABLE READ.
I could do that here if I were a lazy cheater.
Instead, I’m going to show you a more common and interesting scenario.
This leaves the classes of non-lazy cheater and a lazy non-cheater (because non-lazy non-cheater sounds batty). Regardless of your answer, great post by Erik.
Comments closedAll performance spools are lazy. The spool’s worktable is gradually populated, a row at a time, as rows stream through the spool. (Eager spools, by contrast, consume all input from their child operator before returning any rows to their parent).
Performance spools always appear on the inner side (the lower input in graphical execution plans) of a nested loops join or apply operator. The general idea is to cache and replay results, saving repeated executions of inner-side operators wherever possible.
When a spool is able to replay cached results, this is known as a rewind. When the spool has to execute its child operators to obtain correct data, a rebind occurs.
You may find it helpful to think of a spool rebind as a cache miss, and a rewind as a cache hit.
Read the whole thing. I am admittedly the type of person Paul mentions at the end (I reflexively hiss when I have a query performing poorly and see a spool).
Comments closedAaron Bertrand walks us through a case where filtered indexes become unhelpful:
Again, focusing on the areas highlighted in orange: the statement has a parameter @0 (previously it had @1) but, more importantly, the clustered index is scanned now instead of the filtered index. This has impacts throughout the plan, including how many rows are both estimated to be read and actually read in order to return those 11 rows. You can see a much higher I/O cost (about 22X), the predicate is now listed explicitly in the tooltip, and you can see warnings about residual I/O (which just means a lot more rows were read than necessary). The root operator still has the warning about the unmatched index, so at least the plan gives you some clue that a filtered index exists that might be useful if you change the parameterization setting for the database (or add OPTION (RECOMPILE) to the statement):
There are still ways to make filtered indexes work with forced parameterization, such as index hints, but Aaron does a great job explaining why something which seems like it should just work doesn’t always.
Comments closedLukas Eder takes on the myth that COUNT(*) differs from COUNT(1):
Now that we know the theory behind these
COUNTexpressions, what’s the difference betweenCOUNT(*)andCOUNT(1). There is none, effectively. The1expression inCOUNT(1)evaluates a constant expression for each row in the group, and it can be proven that this constant expression will never evaluate toNULL, so effectively, we’re runningCOUNT(*), counting ALL the rows in the group again.There should be no difference, and parsers / optimisers should be able to recognise this and not do the extra work of checking every expression evaluation for
NULL-ness.
Interestingly, in one of the four major RDBMS platforms (not including DB2), there is a performance difference of about 10%.
Comments closedErin Stellato shows how you can mine the Query Store tables to learn more about your workload:
The query text and plan are stored in two separate tables, sys.query_store_query_text and sys.query_store_plan, respectively. The text is stored as a nvarchar(max) data type, and the plan is stored as varbinary(max). This means that the data can be mined, looking for patterns and explicit use of objects. Want to know what queries use an index? Look for it in the plans. Want to know what queries have a RECOMPILE hint on them? Look for it in the query text. Anything you want to find with regard to query text or plans is there, you just have to know how to query the data.
It’s hard to tune queries if you don’t know what’s running.
Comments closedGrant Fritchey shows off an improvement in SQL Server 2019:
I’ve always felt responsible for making such a big deal about the differences between estimated and actual plans. I implied in the first edition of the execution plans book (get the new, vastly improved, 3rd edition in digital form for free here, or you can pay for the print version) that these things were so radically different that the estimated plan was useless. This is false. All plans are estimated plans. However, actual plans have some added runtime metrics. It’s not that we’re going to get a completely different execution plan when we look at an actual plan, it’s just going to have those very valuable runtime metrics. The problem with getting those metrics is, you have to execute the query. However, this is no longer true in SQL Server 2019 (CTP 2.4 and greater) thanks to sys.dm_exec_query_plan_stats
Click through for an example, as well as what you need to do to enable this.
Comments closedErik Darling reminds you to check out the properties of execution plan elements:
I read a lot of posts about query plans, and I rarely see people bring up the properties tab.
And I get it. The F4 button is right next to the F5 button. If you hit the wrong one, you might ruin everything.
But hear me out, dear reader. I care about you. I want your query plan reading experience to be better.
Erik provides sound advice here.
Comments closedJeffry Schwartz takes us through an odd case:
Recently, a customer requested that we tune a query that took 13 seconds to return 11 rows. SQL Server 2017 suggested an index to improve performance, so we added it in a development environment. The improvement made the query run 647 seconds, almost 50 TIMES longer than the original! This naturally caused much consternation, so we decided to determine what and why it happened as well as how we could still achieve the original objective, i.e., make the query run faster. This article discusses what caused the original performance problem in addition to the new one that was caused by the introduction of an index, and illustrates how we were able to make the query run significantly faster than it did originally. We will cover reading query plans, examining the specific details of query plan operators, the effects of index statistics on missing index recommendations, using query plan XML to enable simpler query plan comparison, and the effects of using functions in where clauses.
Click through to understand how this could be the case.
Comments closedInstead of taking the value #”Sorted Rows”[Column2]{0} and storing it in the variable Column2 then adding Column2 four times, I’m adding the expression #”Sorted Rows”[Column2]{0} together four times. The query returns the same number as the previous query. However this query takes 20 seconds to run! Why?
Read on for the explanation.
Comments closedErik Darling didn’t warn us that there would be math:
Often when query tuning, I’ll try a change that I think makes sense, only to have it backfire.
It’s not that the query got slower, it’s that the results that came back were
wrongdifferent.Now, this can totally happen because of a bug in previously used logic, but that’s somewhat rare.
And
wrongdifferent results make testers nervous. Especially in production.
This is where knowledge of abstract math and logic (like De Morgan’s Laws, both of which I’ve used to tune queries in the past because I’m a nerd) can pay off
Comments closed