Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take 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
35 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 ...
user avatar
  • 587
-3 votes
0 answers
35 views

Page Life Expectancy is dropping frequently

Server Spec: 64 GB Ram (R5.2xLarge) Support Team reported there is memory pressure on the SQL Server. Allocated 52 GB memory as min and max for SQL Instance Observed page life expectancy counter value ...
user avatar
  • 1
6 votes
1 answer
108 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 ...
user avatar
1 vote
1 answer
67 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. ...
user avatar
  • 7,100
5 votes
1 answer
182 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 ...
user avatar
7 votes
2 answers
645 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&...
user avatar
  • 108
3 votes
1 answer
365 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 ...
user avatar
0 votes
1 answer
81 views

How to investigate memory grant warnings?

Saw this warning from query in plan cache <Warnings> <MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="2751552" GrantedMemory="2751552"...
user avatar
1 vote
2 answers
108 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 ...
user avatar
  • 1,163
1 vote
1 answer
102 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 ...
user avatar
4 votes
1 answer
3k 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 ...
user avatar
  • 630
9 votes
1 answer
352 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?
user avatar
  • 33.5k
2 votes
1 answer
364 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 ...
user avatar
6 votes
1 answer
734 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 ...
user avatar
  • 163
22 votes
1 answer
4k 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, ...
user avatar
  • 69.4k
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. ...
user avatar
  • 1,285
7 votes
2 answers
3k 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-...
user avatar
  • 1,363
3 votes
1 answer
507 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 ...
user avatar
  • 30.3k
47 votes
3 answers
11k 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 ...
user avatar
  • 69.4k