A Fine Slice Of SQL Server
Instead 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 closedBert Wagner takes us through some of the more common execution plan operators:
Spools come in a variety of types, but most of them can be summarized as operators that store an intermediary result table in tempdb.
SQL Server often uses spools to process complex queries, transforming the data into a tempdb worktable to allow further data operations. The downside to this however is the need to write the data to disk in tempdb.
When I see a spool, I first often try to think if there is a way to rewrite the query to avoid the spool in the first place.
Spools are my bete noire. I love what Erik Darling calls them: SQL Server passive-aggressively telling you that you need an index without actually saying that you need an index.
At the end of the post, Bert calls out Hugo Kornelis’s operator list. Definitely check that out too, as Hugo is putting together a masterpiece in the original meaning of the term.
Comments closedErik Darling gives us multiverse indexing:
Missing index request column order is pretty basic.
Instead, we’re gonna add these:
CREATE INDEX ix_spaces
ON dbo.Posts(ParentId, Score);
CREATE INDEX ix_tabs
ON dbo.Posts(Score, ParentId);
SQL Server is capable of using both intelligently. This is something I generally don’t like to do, but if you have queries which absolutely need differently-ordered index columns, it can make sense to do this. Just don’t expect SQL Server’s missing index DMV to tell you which order they should be in.
Comments closedBert Wagner gives us some tips for reading execution plans in SQL Server:
Execution plans show the steps SQL Server takes to execute your query. Each icon in the graphical execution plan is known as an operator, and the most common way to read a plan is by starting with the top right most operator and following the arrows to the left.
When you reach a join or concatenation operator where multiple branches merge into one operator, you can proceed to the right-most operator of one of the lower branches and start the process of reading right to left again. In general, this can be summed up as reading a plan right to left, top to bottom.
Right-to-left, top-to-bottom gives you the flow of information and that’s quite important. To understand how the engine works, though, you also need to read left-to-right, as Brad Schulz’s outstanding one-act play demonstrates.
Comments closedErik Darling explains that abstraction can be the cause of performance woes in SQL Server:
There’s no “caching” of steps in a query. If you nest a view however-many-levels-deep, each step isn’t magically materialized.
Same goes for CTEs. If you string a bunch together and reference them multiple times, you’ll start to see some very repetitive branches in your query plans.
Now, there are tricks you can play to get what happens inside of one of these steps “fenced off”, but not to get the result set fully materialized.
It’s a logical separation, not a physical one.
In addition, as your query gets more and more complex, the optimizer eventually gives up and gives you what will likely be an ugly version of its implementation because there are too many potential solutions.
Comments closedErik Darling has a series on getting the highest value using CROSS APPLY. Part 1 covers the no-index route:
Let’s say you wanna get the highest thing. That’s easy enough as a concept.
Now let’s say you need to get the highest thing per user. That’s also easy enough to visualize.
There are a bunch of different ways to choose from to write it.
Part 2 covers the yes-index route:
In this round, row number had a tougher time than other ways to express the logic.
It just goes to show you, not every query is created equal in the eyes of the optimizer.
I don’t think I’m spoiling too much by saying that you really want a good index in place when using CROSS APPLY in this manner.
Erik Darling shows off a neat trick for inserting with parallelism into a table variable:
One of the many current downsides of @table variables is that modifying them inhibits parallelism, which is a problem #temp tables don’t have.
While updating and deleting from @table variables is fairly rare (I’ve seen it, but not too often), you at minimum need an insert to put some data in there.
No matter how big, bad, ugly, or costly your insert statement is, SQL Server can’t parallelize it.
That’s just what he wants you to think and then the trap goes off.
Comments closedErik Darling wants to pump your indexes up:
Now, there’s an Extended Event that… Used to work.
These days it just stares blankly at me. But since I’ve worked with this before, I know the problem.
It’s that Key Lookup — I’ll explain more in a minute.
Adaptive joins won’t do all the work for you, so Erik explains how you can set yourself up for success.
Comments closedGetting the actual execution plan, that is the plan with run-time statistics for a query from an application has always been a little difficult. It’s fine if you can get the query running in Management Studio and reproducing the behaviour from the app, but that can be difficult.
There’s the query_post_execution_showplan event in Extended Events, but that’s a pretty heavy event and not something that I’d like to run on a busy server.
No more! SQL 2019 adds a new plan-related function to get the last actual plan for a query: sys.dm_exec_query_plan_stats.
Read on to see how to configure this, as well as a demo.
Comments closed