Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [memory-grant]

Memory granted by the server before or during query execution. Memory grants are typically used for sorting, hashing, and parallel buffers.

Filter by
Sorted by
Tagged with
0 votes
1 answer
48 views

How is Memory Grant Desired Memory calculated

I have the following query in the StackOverflow2010 database: SELECT Id, OwnerUserId, CreationDate, Body FROM Posts ORDER BY LastEditorDisplayName The execution plan shows me ...
SE1986's user avatar
  • 1,970
3 votes
0 answers
176 views

Why am I getting RESOURCE_SEMAPHORE timeouts when there should be plenty of memory available for grants?

We occasionally have queries time out due to RESOURCE_SEMAPHORE waits but there appears to be plenty of available memory for memory grants. For example, I'm able to see timeouts by executing two ...
Joe Obbish's user avatar
  • 32.2k
0 votes
1 answer
48 views

IsMemoryGrantFeedbackAdjusted missing in plan

Why would IsMemoryGrantFeedbackAdjusted and LastRequestedMemory properties be missing from our Actual Execition Plans? We're using 2019 RTM CU18 (Standard Edition) We realise memory grants are often ...
HeavenCore's user avatar
10 votes
1 answer
3k views

Is it possible for SQL Server to grant more memory to a query than is available to the instance

I was asked the other day what would happen if SQL Server wanted to run a single query that was granted more memory than is available to the instance. My initial thoughts were that I may see ...
SE1986's user avatar
  • 1,970
3 votes
1 answer
192 views

Curbing Wasteful Memory Grants - MSSQL 2017

I'm running an on-premise SQL Server 2017 Enterprise at the latest patch/CU level supporting vendor software, which will become important later on. Server Stats: Virtual Machine (Memory is Pinned) ...
John Eisbrener's user avatar
0 votes
1 answer
1k views

How is the size of the buffer pool (data cache) calculated?

What is the formula for calculating the maximum size of the buffer pool (data cache) for SQL Server (when there is only one instance of the machine). Say the machine has 1 GB of physical memory, what ...
xhr489's user avatar
  • 807
6 votes
1 answer
112 views

Why does estimated memory have a three phase shape?

The graph for this other question is for a 1 million row table. When drawn for 10,000 rows the graph has a rather different and more interesting shape. There's a plateau when the column length is ...
Michael Green's user avatar
1 vote
1 answer
371 views

Does the query still run even when SQL Server can't grant the requested memory for the query?

I was thinking about something like this today but I couldn't find this piece of information. When the SQL Server requests memory and the OS can't provide it, we get a RESOURCE_SEMAPHORE wait type. ...
Racer SQL's user avatar
  • 7,384
5 votes
1 answer
482 views

Large memory grant for joined tables

When I select columns from both joined tables a get a huge memory grant (529808). If I query each table columns separately it has a combined memory grant of just 8008. Why does the combined list of ...
Johannes Heesterman's user avatar
7 votes
2 answers
827 views

SQL Server / XML.query() causes massive memory grant

The following (on my systems, tested on SQL Server 2016 and 2019 std) produces a massive memory grant request (3327 MB) declare @wibble xml = '<wibble><wobble>1</wobble></wibble&...
uberbloke's user avatar
  • 108
3 votes
1 answer
856 views

SQL Server High CPU usage and RESOURCE_SEMAPHORE waits

Last week had an issue on one of SQL Servers, CPU started burning over 80 % (normal is 10-30 %) This lasted for about 2 hours until I manually failed over to secondary replica in AG (and this has ...
Aleksey Vitsko's user avatar
0 votes
1 answer
424 views

How to investigate memory grant warnings?

Saw this warning from query in plan cache <Warnings> <MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="2751552" GrantedMemory="2751552"...
user2368632's user avatar
  • 1,097
1 vote
2 answers
423 views

Why does TempDB spill happen even though statistics are correct?

I read a great article published by Brent Ozar and came up with some questions related to memory grant. I am unable to address my questions in the comment section of his article, so I thought to get ...
Rauf Asadov's user avatar
  • 1,273
1 vote
1 answer
170 views

Memory Grant Warning on what appears to be a better query?

OK, I was looking at potentially reducing this (as more of an exercise than a need mind you) and it seems that Im getting this warning on what appears to be a better performing query and not on the ...
Doug Coats's user avatar
4 votes
1 answer
5k views

SQL Server 2016 - excessive memory grant warning on poor performing query

I have a relatively large database of 550GB on a SQL Server 2016 EE instance which has a max memory limit of 112GB of the total 128GB RAM available to the OS. The database is at the latest ...
Fza's user avatar
  • 652
9 votes
1 answer
636 views

In SQL Server, How Does Parallelism Change Memory Grants?

I've heard conflicting things about memory grants for parallel select queries: Memory grants get multiplied by DOP Memory grants get divided by DOP Which is it?
Erik Darling's user avatar
2 votes
1 answer
491 views

Excessive memory grants from indexed foreign key cascade delete

Each day, stores can enter sales information into our OLTP application. The app calls a stored procedure in SQL to save this information. Based on the users's activity, the application sends a code ...
Paul Williams's user avatar
6 votes
1 answer
928 views

Execution plan doesn't show memory grant

I installed SQL Server 2019 on a VM on Azure (DS11). I am trying to illustrate adaptive query processing using this post here. The issue is that when I hit CTRL+L in SQL Server Management Studio to ...
5th's user avatar
  • 163
23 votes
2 answers
5k views

When does SQL Server warn about an Excessive Memory Grant?

What are the conditions that produce an "Excessive Grant" execution plan warning? The query memory grant detected "ExcessiveGrant", which may impact the reliability. Grant size: Initial 5128 KB, ...
Paul White's user avatar
  • 85.7k
4 votes
1 answer
2k views

Huge Memory Grant resulting in RESOURCE SEMAPHORE waits for other queries

Issue: I have few queries which is requesting huge memory grants(~7GB).These queries are run often and this is causing other queries to wait for memory.So i am seeing RESOURCE_SEMAPHORE wait type. ...
user9516827's user avatar
  • 1,315
8 votes
2 answers
4k views

Large memory grant requests

I have one query with multiple execution plans, the memory granted to one plan is huge comparing to the 2nd one based on this article https://blogs.msdn.microsoft.com/sql_server_team/addressing-...
sebeid's user avatar
  • 1,381
3 votes
1 answer
665 views

Why does CCI creation on an empty table request a huge memory grant?

On a QA server with very low memory, I experienced error 8545 when creating clustered columnstore indexes on new tables in SQL Server 2016: Error 8645: A time out occurred while waiting for memory ...
Joe Obbish's user avatar
  • 32.2k
46 votes
3 answers
12k views

Excessive sort memory grant

Why is this simple query granted so much memory? -- Demo table CREATE TABLE dbo.Test ( TID integer IDENTITY NOT NULL, FilterMe integer NOT NULL, SortMe integer NOT NULL, Unused ...
Paul White's user avatar
  • 85.7k