Memory Grants

Parameter Sniffing in SQL Server 2019: Air_Quote_Actual Plans

Last Updated May 26, 2020

Execution Plans, Memory Grants, Parameter Sniffing, SQL Server 2019

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…

Parameter Sniffing in SQL Server 2019: Adaptive Memory Grants

Last Updated May 26, 2020

Brent Ozar

Memory Grants, Parameter Sniffing, SQL Server 2019

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…

Batch Mode For Row Store: What Does It Help With?

Last Updated January 8, 2019

Erik Darling

Batch Mode, Memory Grants

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.…

We Need to Talk About the Warnings In Your Query Plans.

Last Updated January 8, 2019

Brent Ozar

Execution Plans, Memory Grants

SQL Server, thanks for coming today. Have a seat. Yes, this might seem odd – there are a lot of us here. And yes, we are locking the door behind you. We all care about you – all of us care very deeply – but we need to talk about some warning signs. Specifically, the…

What’s New in SQL Server 2019: Adaptive Memory Grants

Last Updated May 26, 2020

Brent Ozar

Execution Plans, Memory Grants, Parameter Sniffing, SQL Server 2019

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…

How Trace Flag 2335 Affects Memory Grants

Last Updated January 8, 2019

Brent Ozar

Memory Grants, SQL Server

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…

A Strange Place For A Memory Grant

Last Updated January 8, 2019

Erik Darling

Execution Plans, Memory Grants, SQL Server

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 Hash Join Hash Match Sort Parallelism Exchanges But there’s something that rarely gets discussed, even in the deepest, darkest, dankest dungeons. Let’s loosen the clamps and look at it. Keys…

Concurrency Week: How Entity Framework and NHibernate Can Cause Poison RESOURCE_SEMAPHORE Waits

Last Updated January 8, 2019

Tara Kizer

Development, Memory Grants

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: Implicit conversions due to nvarchar variables vs varchar columns…

Hash Join Memory Grant Factors

Last Updated January 8, 2019

Erik Darling

Execution Plans, Memory Grants

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…

Adaptive Joins, Memory Grant Feedback, and Stored Procedures

Last Updated May 26, 2020

Erik Darling

Adaptive Joins, Execution Plans, Memory Grants, Parameter Sniffing

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%) This isn’t…

Memory Grants: SQL Server’s Other Public Toilet

Last Updated May 26, 2020

Erik Darling

Execution Plans, Indexing, Memory Grants, Parameter Sniffing

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. What kind of stuff? Well, usually…

“But It Worked in Development!” – 3 Hard Performance Problems

Last Updated January 8, 2019

Brent Ozar

Development, Memory Grants

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…

SQL Server 2017 CU3 adds tempdb spill diagnostics in DMVs and Extended Events

Last Updated May 26, 2020

Erik Darling

Execution Plans, Memory Grants, Parameter Sniffing, SQL Server 2017

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…

How Much Can One Row Change A Query Plan? Part 2

Last Updated January 8, 2019

Brent Ozar

Execution Plans, Memory Grants

Earlier this week, I showed you two nearly identical queries and their estimated plans: One of these things is much like the other They look pretty darned identical – so how much worse can one query be? Here’s how much worse: Top 100: 51.0 seconds duration, 141.6 seconds CPU time Top 101: 160.0 seconds duration,…

When THREADPOOL Waits Lead To RESOURCE_SEMAPHORE Waits

Last Updated January 8, 2019

Erik Darling

Memory Grants, Wait Stats

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. Alright, so maybe it’s not that bad, but it’s bad enough that you run into…

Memory Grants and Data Size

Last Updated January 8, 2019

Erik Darling

Execution Plans, Memory Grants, SQL Server

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…

What’s the Difference Between Estimated and Actual Execution Plans?

Last Updated January 8, 2019

Brent Ozar

Execution Plans, Memory Grants, SQL Server

I’m going to use the dbo.Users table in the StackOverflow demo database and run a pretty simple query: Transact-SQL SELECT * FROM dbo.Users WHERE DisplayName LIKE 'Brent%'; 123 SELECT *FROM dbo.UsersWHERE DisplayName LIKE 'Brent%'; First, hit Control-L in SSMS and get the estimated execution plan. Here it is: Estimated execution plan: https://www.brentozar.com/pastetheplan/?id=H1TeRlZke Click on the…

Memory Grants Added to sp_BlitzCache and sp_BlitzFirst

Last Updated January 8, 2019

Erik Darling

First Responder Kit, Memory Grants, sp_BlitzCache, sp_BlitzFirst

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…

An Introduction to Query Memory

Last Updated May 26, 2020

Brent Ozar

Execution Plans, Memory Grants, Parameter Sniffing, SQL Server

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…

SQL Server 2012 SP3 Adds Memory Grant and Performance Features

Last Updated January 8, 2019

Kendra Little

Memory Grants, SQL Server

New ways to measure your queries 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…

Memory Grants

Save your cart?