I noticed a PLE drop on our production server and I narrowed it down to a query that had a 31GB memory grant:
![]( "Image: /hc/user_images/01JKDJKW7YYJD1YN08JPCR0QBZ.png")
![]( "Image: /hc/user_images/01JKDJKVQQQ3NQR1W85DVY4Y58.png")
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)
![]( "Image: /hc/user_images/01JKDJKW47S4ATEE5F2R651MFT.png")
However, this memory grant was not reflected in the SQL Monitor view, instead displaying 0
![]()
![]( "Image: /hc/user_images/01JKDJKVWK79YKTNNK8ZW7X1D9.png")
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