Home / server / Questions / 134960
Accepted
AngryHacker
Asked: 2010-04-23 09:01:39 +0800 CST

Justifying a memory upgrade, take 2

  • 772

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?

sql-server windows-server-2003 64-bit performance-monitoring

2 Answers

  1. 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).

    • 4
  2. Best Answer

    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.

    • 3