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
3
votes
1answer
407 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 ...
8
votes
1answer
132 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?
2
votes
1answer
74 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 ...
6
votes
1answer
308 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 ...
18
votes
1answer
2k 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, ...
3
votes
1answer
426 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. ...
7
votes
2answers
2k 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-...
3
votes
1answer
286 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 ...
45
votes
3answers
10k 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 ...