Some of the important performance counters related to memory in SQL Server are:
SQL Server Cache Hit Ratios: A consistent value below 90% indicates that more physical memory is needed on the server.
Memory Object: Available Bytes: On a server dedicated to SQL Server, SQL Server attempts to maintain from 4-10MB of free physical memory. The remaining physical
Memory Object: Pages/Sec: Measures the number of pages per second that are paged out from
SQLServer:Memory Manager: Total Server Memory (KB):Tells you how much memory your SQL Server is currently using.
SQLServer:Memory Manager: Target Server Memory (KB):Tells you howmuch memory SQL Server would like to use to operate efficiently. If the SQLServer:Memory Manager: Total Server Memory (KB) counter is more or equal than the SQLServer:Memory Manager: Target Server Memory (KB) counter, this indicates that SQL Server may be under memory pressure and could use access to more physical memory.
SQL Server Buffer Manager Object: Cache Size (pages): Multiply this value by 8K to get the value of physical
Process: Working Set: shows the amount of memory used by process. If this number is consistently below Min Server Memory and Max Server Memory then SQL Server is configured to use too much memory.
When the system is running out of memory, it will have higher paging and disk I/O. You can measure the Memory: Page Faults/sec counter to make sure that the disk activity is not caused by paging. You can also monitor SQL Server:Buffer Manager:Page reads/sec and SQL Server:Buffer Manager:Page writes/sec performance counters. Check more at: http://msdn2.microsoft.com/en-us/library/ms175903.aspx.
There is a relationship between SQL Server disk I/O and CPU usage. Check the article Monitoring CPU Usage – http://msdn2.microsoft.com/en-us/library/ms178072.aspx for more details.
Following are other very useful articles for troubleshooting SQL Server performance.
SQL Server Memory Monitoring – http://msdn2.microsoft.com/en-us/library/ms176018.aspx. This article also contains the list of performance counters for monitoring SQL Server memory. The Process: Working Set counter shows the amount of memory that is used by a process.
Troubleshooting Performance Problems in SQL Server 2005 – http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx