Justifying a memory upgrade, take 2

Question

Previously I asked a question on what metrics I should measure (e.g. before and after) to justify a memory upgrade. Perfmon was suggested.

I'd like to know which specific perfmon counters I should be measuring. So far I got:

PhysicalDisk/Avg. Disk Queue Length (for each drive)
PhysicalDisk/Avg. Disk Write Queue Length (for each drive)
PhysicalDisk/Avg. Disk Read Queue Length (for each drive)
Processor/Processor Time%
SQLServer:BufferManager/Buffer cache hit ratio

What other ones should I use?

score 4 · Answer 1 · edited Mar 07 '11 at 18:47

Those are probably not the best counters. The problem with disk I/O is that is it is not going to be that useful because disk I/O depends a lot more on database optimization unless the whole database was in memory. Operations like table scans and reporting / load scripts will overload your memory. A heavy I/O load will produce I/O on the log file, regardless of memory.

These disk counters are not the best for disk analysis either - queue length isn't always clear because it varies depending on your hardware layout. For example, 250 might not be a bad queue length for your disc, but maybe not if you have a large power SAN which can handle lots of parallel requests.

I rather go with the primary factors: Disk overloaded, I/O takes longer, seconds/read, seconds/wire - this is non-subjective. More primary data such as seconds/read gives a unique number not dependent on the hardware, and lower response times show what you are really looking for.

For memory, I would take:

SQL Memory Manager:Memory Grants Pending
SQL Buffer Manager: Page Life expecancy

The last gives you an idea of how fast pages are getting out again. You have to make sure, though, someone is not forcing this - table scans are perfect for that (as basically you will overload the cache with a table scan, unless the whole table fits in memory).

An overloaded disk does not necessarily make IOPS take longer. — Jim B, Apr 25 '10 at 23:42
It actually does. Not from the DISC point of view, but this is not measuerd. But from the OS point of view - it takes the OS Longer to get responses. Basically it tells you when the queue gets "critical" and has an impact on the IO response time. — TomTom, Apr 26 '10 at 05:55

score 3 · Accepted Answer · edited Apr 13 '17 at 12:14

First I would suggest reading my answer on how Windows memory works. After that let's talk about counters. From a pure performance monitoring perspective, server memory and SQL Server performance are unrelated primarily because SQL Server (if set up correctly) uses the ability to lock pages in memory to override the default Windows memory management scheme. Looking at just memory the previously mentioned counters are OK:

SQL Memory Manager: Memory Grants Pending

SQL Buffer Manager: Page Life expecancy

SQL Server Buffer Manager Object: Buffer Cache Hit Ratio

I would also add

SQLServer:Memory Manager: Total Server Memory (KB) - this shows you how much SQL Server is managing.

SQLServer:Memory Manager: Target Server Memory (KB) - this shows you how much SQL Server thinks it would like to have based on the number of buffers reserved by SQL Server when it is first started up. If total is less than target, it's not likely extra RAM will help. If target is greater you might benefit from more memory.

See also what should be monitored for baseline SQL Server performance.

Justifying a memory upgrade, take 2

2 Answers2