Memory Grant Showing as 0 in monitor but 31GB in SSMS Execution Plan
SEarle86
Posts: 21 Bronze 1
I noticed a PLE drop on our production server and I narrowed it down to a query that had a 31GB memory grant:
I copied the code and ran it on a test server to do some investigation (the test server is the same spec as the production server)
The plan that was compiled on the test server was the same as production and the memory grant was the same (actual execution plan from SSMS below)
However, this memory grant was not reflected in the SQL Monitor view, instead displaying 0
why is the memory grant no reflecting on the test server, despite the SSMS plan confirming the amount?
Incidentally, I found the cause of the grant to be a %wildcard% search on a VARCHAR(MAX) column!
My monitor versions are
web: 12.1.46.6959
Base monitor: 12.1.46.6959
SQL Server 2019 CU18 on both production and test servers
SSMS version 18.12.1
I copied the code and ran it on a test server to do some investigation (the test server is the same spec as the production server)
The plan that was compiled on the test server was the same as production and the memory grant was the same (actual execution plan from SSMS below)
However, this memory grant was not reflected in the SQL Monitor view, instead displaying 0
why is the memory grant no reflecting on the test server, despite the SSMS plan confirming the amount?
Incidentally, I found the cause of the grant to be a %wildcard% search on a VARCHAR(MAX) column!
My monitor versions are
web: 12.1.46.6959
SQL Server 2019 CU18 on both production and test servers
SSMS version 18.12.1
Tagged:
Answers
As per the support ticket we have open with you.
After upgrading to SQL Monitor V14.1.48, you confirmed that the problem reported is now resolved.
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com