You can set LPIM in Configuration Manager without editing the local security policy by hand. This post shows where the property sits, how to read it from PowerShell, and how to check the result with T-SQL after the restart. It also explains why a memory cap comes first.
Queries Waiting for Memory: Read the Grant Counters
Queries waiting for memory are counted by the Memory Grants Pending counter and listed by sys.dm_exec_query_memory_grants. A tested demo holds a sort grant of 41,848 KB with a blocked query and reads it from the view. The post explains how to spot a waiting request and what causes the wait.
Row Mode Memory Grant Feedback: It Needs Level 150
Row mode memory grant feedback needs compatibility level 150 or higher, while batch mode needs only 140. A tested demo runs a row mode sort and a batch mode sort at levels 140, 150 and 170. The row sort keeps its grant of 11,888 KB at level 140 and drops to 1,536 KB at 150.
Memory Grant Feedback Events: Capture Them in T-SQL
Memory grant feedback events list every query whose memory grant keeps changing, and the moment SQL Server gives up on it. This post creates the Extended Events session in T-SQL, watches it live in SSMS and reads it with one query that names the procedure.
SQL SERVER – MemoryGrantInfo Property Explanation
I have been writing about the Memory Grant Feedback feature. Today we will discuss various properties of MemoryGrantInfo in this blog post.
MemoryGrantInfo – What are Different Status of IsMemoryGrantFeedbackAdjusted? – Interview Question of the Week #253
Question: What are Different Status of IsMemoryGrantFeedbackAdjusted (MemoryGrantInfo Property)? Today we will see a consolidated answer to this question.
Stop Memory Grant Feedback: Database Setting and Query Hint
To stop memory grant feedback, use the hint or the setting that matches the mode of the plan. A tested demo shows that a row mode hint keeps a grant at 13,256 KB, while a batch mode hint changes nothing for a row mode sort. The post also corrects the setting names and shows the persistence switch.