Joins, parallel execution, memory grants, spills, and adaptive execution.
93 associated posts89 primary posts
Most of the time, you'll hear advice that you should select as few columns as possible so that SQL Server can do less work.
However, one of my clients hit a neat edge case scenario where SELECT * queries actually ran faster than picking a subset of columns.
Earlier in this series, we ran a query with ORDER BY, and we realized that it was CPU-intensive work that tripled the cost of the query: Now, let's run that query a bunch of times. In SSMS, you can add numbers after the GO, and SSMS will run your query a number of times. I'll…
In our last post, we ran a query with an ORDER BY, but we only got one column in the SELECT:
[crayon-6a707eef6df77303371146/]
The estimated cost was about $18 Query Bucks because SQL Server had to:
Scan the entire clustered index, yelling out the Id and LastAccessDate of each row
Sort that list by LastAccessDate
We must be getting pretty close to the official release of Microsoft SQL Server 2019. There are less than 80 days left in the year. (Interestingly, it's been almost 60 days since Release Candidate 1 shipped - that's unusually long for a delay between builds. Hmm.)
With the clock ticking down, now's a good time to take a 40-minute look at some of my favorite new features: deferred compilation for table variables, adaptive memory grants, adaptive joins, and air_quote_actual plans:
Up til now in the DBA Training Plan, we've been taking inventory of our servers, making sure the data's well-protected, and understanding the basics of how the data's stored in indexes inside those data files. Now, let's start looking at performance, and let's start by zooming really far out to think about how SQL Server runs a query.
The performance of scalar functions sucks hard. Let's see it in action using the Stack Overflow database - any size will work. I'll set things up first in case you want to follow along: [crayon-6a707eef6ee1e396918874/] I'm purposely setting my Cost Threshold for Parallelism to be low here because I want to demonstrate what happens when…
You've been writing queries for at least a year or two, and you're curious about what's happening under the hood when SQL Server runs a query. You want to know what you can do to improve your queries, tune your indexes, and change SQL Server's configuration settings so that your results will return faster.
The book Learn T-SQL Querying is for curious people like you.
This week, I've been writing about how SQL Server 2019's bringing a few new features to mitigate parameter sniffing, but they're more complex than they appear at first glance: adaptive memory grants, air_quote_actual plans, and adaptive joins. Today, let's talk about another common cause of wildly varying durations for a single query: user-defined functions.
What's the first thing you remember struggling with on SQL Server? Your first memory of, "Dang, I just can't figure this out," and then you had a Eureka moment later when it was so much more clear?"
I asked folks on Twitter, and the answers were great, so I had to round 'em up here to share 'em with you:
A while back, we talked you through a public whitepaper about how Microsoft was working on making user-defined functions go faster. Now that the preview of SQL Server 2019 is out, you can start getting your hands on Froid, the performance-boosting feature. Here's the documentation on it - let's see how it works. Using the…
Red Skies At Night I know it's hard to believe, but I still see a lot of people using cursors when they shouldn't. Other times, there's some scary dungeon part of the code that someone wrote eons ago that no one wants to go anywhere near to fix. Sometimes there's a decent reason, something like:…
SQL Server 2019
Introduces something pretty cool: the ability for row store indexes to be processed in batch mode. Batch mode is, of course, the preferred processing realm of column store indexes.
What Is Skewed Parallelism?
When queries go parallel, some assumptions get made:
There's enough work to keep multiple threads busy
Each thread will get an equal amount of work to do
When you run a query, SQL Server guesses how much memory you're going to need for things like sorts and joins. As your query starts, it gets an allocation of workspace memory, then starts work. Sometimes SQL Server underestimates the work you're about to do, and doesn't grant you enough memory. Say you're working with…
Innocent Enough
I was writing another query, and became enamored with the fact that HAVING will accept IS NULL or IS NOT NULL as a predicate.
What I ended up writing as an example was this query:
[crayon-6a707eef72504120105681/]
Why this query?
In which I show why you can't trust "actual" execution plans for branching stored procedures, and you're better off using sp_BlitzCache to figure out what branches you need to tune:
https://www.youtube.com/watch?v=y7ypC9YZnTU
Bailing, Takes Me Away
Both of these queries will return zero rows, in the 2010 version of the Stack Overflow database.
[crayon-6a707eef74361116889864/]
Querying a couple of the larger tables, with no indexes on the join columns leads to hash join plans.
If you're wondering why I'm forcing the second plan serial, using the MAXDOP 1 hint...
Slicked Brad The apply operator remains one of my favorite tools, but much like CTEs and windowing functions, they're not totally magical unless you look at your indexes. Starting with a dead simple query on the Users table, it'd be easy to come up with an ideal index for this one thing. [crayon-6a707eef74c88392088238/] If you…
Buskets
Much like Sorts, Hash Joins require some amount of memory to operate efficiently -- without spilling, or spilling too much.
And to a similar degree, the number of rows and columns passed to the Hashing operator matter where the memory grant is concerned. This doesn't mean Hashing is bad, but you may need to take some extra steps when tuning queries that use them.
Snow Is Temporary
Cumulative Update 5 dropped for SQL Server 2017. Hooray.
Poor Taryn.
