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.
22
questions
3
votes
0
answers
80
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 ...
- 32k
0
votes
1
answer
41
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 ...
- 786
10
votes
1
answer
2k
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 ...
- 1,908
3
votes
1
answer
152
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)
...
- 9,342
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 ...
- 785
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 ...
- 24.7k
1
vote
1
answer
284
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.
...
- 7,364
5
votes
1
answer
432
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 ...
7
votes
2
answers
791
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&...
- 108
3
votes
1
answer
730
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 ...
- 5,452
0
votes
1
answer
265
views
How to investigate memory grant warnings?
Saw this warning from query in plan cache
<Warnings>
<MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="2751552" GrantedMemory="2751552"...
- 1,087
1
vote
2
answers
325
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 ...
- 1,253
1
vote
1
answer
152
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 ...
- 349
4
votes
1
answer
4k
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 ...
- 652
9
votes
1
answer
593
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?
- 38.7k
2
votes
1
answer
467
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 ...
- 1,384
6
votes
1
answer
898
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 ...
- 163
22
votes
1
answer
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, ...
- 82.9k
4
votes
1
answer
1k
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.
...
- 1,295
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-...
- 1,381
3
votes
1
answer
641
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 ...
- 32k
47
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 ...
- 82.9k