I want to know whether sqlserver has a memory bottleneck, how bad the free memory needs to be
Can you see which statements use more memory
thank you
Read some information do not understand, want to ask if there is a way
sqlserver 2019
2 Answers 2
Right Tool For...
...The Right Job
If you are trying to analyze your server as a whole, then the sp_PressureDetector by Erik Darling is a great tool that will give you in-depth information regarding Memory (and CPU) consumption and contention. Be sure to watch his videos on how the tool works and how to use it, at the bottom of the page.
...If You Don't Know The Job (Problem)
For a more general overview of things wrong with your server (Memory or not), the First Responder Kit from Brent Ozar's website will give you in-depth information as well. Particularly sp_Blitz and sp_BlitzFirst would be worth taking a look at. If Memory issues are your biggest problems, then they'll be higher up on the results list of these tools.
...If The Job Is Specific Queries
If you have a particular query or few queries that you are troubleshooting, can repeatedly run, and can get the actual execution plan for, then you can hover your mouse over the first operator node all the way to the left in the plan, called SELECT. It'll show you the amount of Memory Granted and any warnings if that amount is excessive (much of it went unused during execution):
In addition to what others have said above, I would suggest monitoring Windows Performance Counters over a period of time. These won't tell you the queries that are causing memory pressure, but they'll help you understand if you're experiencing memory pressure and when.
For memory, you should monitor:
Memory – Available MBytes
Memory Manager\Memory Grants Pending
Buffer Manager\Page Life Expectancy (PLE).
There are others but I'd start with these.
If Memory Grants Pending is consistently going above zero, then the server has more requests for memory than it can cope with.
PLE is a bit more contentious and harder to pinpoint. It's important to bear in mind that it is normal for PLE to drop sharply, particularly during maintenance windows, and then recover gradually. I only really care if PLE is consistently low for a prolonged period of time. What constitutes 'Low' will be different for every environment.
Finally, don't forget to monitor your wait stats. If you have any occurrences of RESOURCE_SEMAPHORE waits, then you have memory pressure.
The most important thing is to establish a baseline for what is 'good' for these stats and then monitor performance against that baseline. Always start by trying to tune your workload before throwing more hardware at the problem.