System Monitor and Memory in SQL Server

Which performance counter (System Monitor) should I use to answer the questions:

-
total amount of memory currently used by SQL Server

-
total amount of memory used by a process

-
total amount of memory required by SQL Server for running processers efficiently.

I think that learning about the answers to these questions is the beginning of the evaluation of memory (global).

I like the way you are thinking. Too many people don't think about these things until they've been burned by them.

You've asked about three things. Some are more "1 answer works" kind of questions. Some are more "Lots of opinions exist.

I'll take a first stab at least. One good rule of thumb, though - don't believe what Task Manager thinks. There are a few reasons that is not so reliable. Lots of memory from different places and various versions do things a slight bit different there.

There are actually a few places to see "total amount of memory currently used by SQL Server" - In perfmon/sysmon you can see two counters - "Total Server Memory" and "Target Server Memory". Simply put, the Total Server Memory is how much SQL Server is using. The Target Server Memory is how much SQL Server - at that moment in time- would like to have based on its current load. You can find this counter Perfmon in SQLServer:Memory Manager perfmon object. If you are on a Default instance it should just be SQLServer at the parent object. If a named instance, it would be something like MSSQL$InstanceName. But Memory Manager is where you can be.

For the second question - you can see a few memory counters under the Process object - and you'll see various instances for the various processes. There are a few counters here - I'd normally stick with Private Bytes to just get a generic "how much is that process actively/actually using"

For the last question - "Total amount of memory required by SQL Server for running processes efficiently" - that is a tougher nut to crack. While mileage certainly varies, I tend to look for a few adages here when I go down this road:

• Memory is cheaper than licenses and just about everything else. You can get storage a little wrong. You can even be a bit under on procs. Memory under allocation is a sin you'll pay for for a long time - so I tend to be a bit aggressive in memory allocation where I can. (Generally).

• If target memory is normally much higher than total memory in the point I made above - that can be a rather clear sign (do note that SQL memory takes some time to "stabilize" after a service restart - when you first bring SQL up - target and total play a game - you'll see them each grow. Wait for some stability.

• How is Page Life Expectancy? This counter is in the SQLServer:Buffer Manager (really you should look at Buffer Nodes in case you have multiple NUMA nodes and see what all of your PLEs are for the various memory allocations in each NUMA node). Is it constantly up and down like it can't sit still? While workload and so many variables come into play - it's better to have a PLE that tends to go up or at least say stable. If it looks like a heart monitor (with a normal or fast heart rate), it could be a sign that you are having some memory pressure.

But I don't like tuning monitors only - you also have to ask "is this impacting our customers?" what's the experience like? Are folks complaining? Are queries going longer than baseline? Front end response times waiting too much on data calls? Do some SQL Server waits based monitoring - what are you watiing for mostly? Are they counters related to memory and potentially to IO?

(Of course each of those opens a whole new rabit hole.. What comes first "insufficient memory" or "bad queries?" - so take a holistic appraoch. Look at your queries. Look at overall tuning. But those counters will at least be a good start for you.

StackExchange Database Administrators Q#213514, answer score: 4