My last post talked about how parameter sniffing caused 3 problems for a query, and how SQL Server 2019 fixes one of them - kinda - with adaptive memory grants.
However, the post finished up by talking about how much harder performance troubleshooting will be on 2019 because your query's memory grant is based on the last set of parameters used, not the current set.
This week, I'm demoing SQL Server 2019 features that I'm really excited about, and they all center around a theme we all know and love: parameter sniffing.
If you haven't seen me talk about parameter sniffing before, you'll probably wanna start with this session from SQLDay in Poland. This week, I'm going to be using the queries & discussion from that session as a starting point.
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.
If you're still in the dark about column store, you might wanna start by reading up on Niko Neugebauer and Joe Obbish's blogs about it.
SQL Server, thanks for coming today. Have a seat.
Yes, this might seem odd - there are a lot of us here.
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…
This trace flag is documented in KB #2413549, which says, "Using large amounts of memory can result in an inefficient plan in SQL Server." The details are a little light, so let's run a quick experiment with:
SQL Server 2017 CU 8 (14.0.3029.16)
VM with 4 cores, 32GB RAM, max memory set to 28GB
Stack Overflow database (circa March 2016, 90GB)
If You Hang Around Fetish Bars Long Enough...
You learn things about memory grants in execution plans.
Stuff like the things that usually need memory
I've already blogged about my dislike for ORMs from a production DBA performance tuning standpoint only. I get that they're useful to developers. I get it. But I'm focused on performance.
A quick recap of what I don't like about ORMs from that other blog post:
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.
Not Exactly The Catchiest Name
There's a TL;DR here, in case you don't feel like reading the whole darn thing.
Batch mode memory grant feedback works with stored procedures
It takes several runs to adjust upwards to a final number
It seems to only adjust downwards once (in this case by about 40%)
Sharing Is Caring
When everything is going well, and queries are behaving responsibly, one need hardly think about memory grants.
The problem becomes itself when queries start to over and under estimate their practical needs.
Second Hand Emotion
Queries ask for memory to do stuff. Memory is a shared resource.
You've been performance tuning queries and indexes for a few years, but lately, you've been running into problems you can't explain. Could it be RESOURCE_SEMAPHORE, THREADPOOL, or lock escalation? These problems only pop up under heavy load or concurrency, so they're very hard to detect in a development environment. In a very fast-paced session, I'll…
Where have you been all my life?
Whenever I'm teaching people about query tuning, two things come up almost without fail: parameter sniffing and tempdb spills.
They're not mutually exclusive -- some queries will seemingly always spill to tempdb, whether parameter sniffing is present of not, and some queries experiencing issues with parameter sniffing will never spill to tempdb.
Earlier this week, I showed you two nearly identical queries and their estimated plans:
They look pretty darned identical - so how much worse can one query be? Here's how much worse:
Your server is underpowered
That's an understatement. Your server sucks.
It has four cores in a single socket, data outpaces RAM by a country mile, the disks have whiskers, and the network card still has a phone jack in it.
What does memory do?
In SQL Server, just about everything. We cache our data and execution plans in it (along with some other system stuff), it gets used as scratch space for temporary objects, and of course queries use it for certain operations, most notably sorting and hashing. And of course, now Hekaton comes along to eat up more of our RAM.
I'm going to use the dbo.Users table in the StackOverflow demo database and run a pretty simple query: [crayon-6a452065829d7233591243/] First, hit Control-L in SSMS and get the estimated execution plan. Here it is: Click on the plan, and hover your mouse over the various operators. You'll notice that almost all of the fields are prefixed…
Exciting New Doodads When SP3 for 2012 dropped, we were all super excited by the new query tuning-centric features that were at our disposal. Now all we had to do was get people to install SP3! Great features like this make patching an easier sell. Now with SP2 for 2014 out, a lot of those…
Microsoft has been quietly making some amazing improvements for performance tuners in SQL Server 2012, 2014, and 2016. This week, we're going to introduce you to just how awesome they are. (They being the improvements, not Microsoft. You already knew they were awesome.)
Using the freely available StackOverflow database, let's start with a simple query - SELECT * FROM Users:
SQL Server 2012 SP3 has just been freshly released! Not only does this release contain bug fixes, it also contains a slew of performance tuning related features.
Many of these features are about memory grants. This is NOT the memory used in the buffer pool/ data cache by the query -- it's the memory also known as Query Workspace Memory. Start reading more here.
