Why is this an important SQL Server Performance Indicator?
Delta Bravo uses this counter to assess the degree of memory pressure the system is under. High memory pressure is a cost driver, necessitating additional resources before user experience is impacted.
Target Server Memory (KB) is the amount of memory that SQL Server is willing (potential) to allocate to the buffer pool under its current load. Total Server Memory (KB) is what SQL currently has allocated. The Total Server Memory is the current amount of memory currently assigned to SQL Server. Upon staring SQL Server its total memory will be low and it will grow throughout the warm-up period while SQL Server is bringing pages into its buffer pool and until it reaches a steady state. Once the steady state is reached, the Total Server Memory measurement should not decrease importantly as that would indicate that SQL Server is being forced to dynamically deallocate its memory due to system-level memory pressure.
If this counter is still growing the server has not yet reached its steady-state, and it is still trying to populate the cache and get pages loaded into memory. Performance will likely be somewhat slower during this time since more disk I/O is required at this stage. This behavior is normal. Eventually Total Server Memory should approximate Target Server Memory, keeping a ratio close to 1.
If the Total Server Memory value is significantly lower than the Target Server Memory value during normal SQL Server operation, it can mean that there’s memory pressure on the server so SQL Server cannot get as much memory as needed, or that the Maximum server memory option is set too low.
So when do I need to add more memory?
If Total Server Memory is less than Target Server Memory it can be a sign of memory pressure, but before going to the business asking for more money for more memory, evaluate some other counters to validate SQL is in memory contention.
Start with Page Life Expectancy, which should be well above the 300. This tells you how long pages are staying in the buffer pool, and a value of 300 equates to 5 minutes. If you have 120GB of buffer pool and it is churning over 5 minutes, that equates to 409.6 MB/sec sustained disk I/O for the system which is a lot of disk activity to have to sustain.
Examine Lazy Writes/sec, which tells you that number of times the buffer pool flushed dirty pages to disk outside of the CHECKPOINT process. This should be near zero. Also review Free Pages/sec and Free List Stalls/sec. You don’t want to see Free Pages bottom out which will result in a Free List Stall while the buffer pool has to free pages for usage. Lastly, look at Memory Grants Pending which will tell you if you have processes waiting on workspace memory to execute.
If these supporting counters exhibit excessive behavior, then it may be time to increase memory allocation.