This performance counter measures the current amount of memory that SQL Server is using.

If the value of this counter continues to grow larger, the server has not yet reached its steady state and is still trying to populate the cache and get pages loaded into memory. Performance may be somewhat slower if this value continually grows larger since more disk I/O is required. This behavior is normal. Eventually Total Server Memory should approximate Target Server Memory.

Potential Issue: If the Total Server Memory counter is greater than or equal to the Target Server Memory counter, this can indicate that your SQL Server may be under memory pressure.

Resolution: Installing additional memory into your SQL server should resolve the problem.

This monitor measures the total amount of dynamic memory the server can consume.

This performance counter tells you how much memory SQL Server would like to use to operate efficiently. Compare with Total Server Memory.

Potential Issue: If the Total Server Memory counter is greater than or equal to the Target Server Memory counter, this could indicate that your SQL Server may be under memory pressure.

Resolution: Installing additional memory into your SQL server should resolve the problem.

This performance counter shows the difference between the total amount of dynamic memory the server can consume and the current amount of memory that SQL Server is using.

Potential Issue: If this performance counter is greater than or equal to the Target Server Memory performance counter, this indicates that SQL Server may be under memory pressure.

Resolution: Installing additional memory into SQL server should resolve the problem.

This performance counter measures the total amount of dynamic memory the server is using for the Dynamic SQL cache.

Most memory used by SQL Server is allocated to the Buffer Pool, which is used to store data pages. SQL Server steals a proportion of this memory for use in caching query plans. The overall amount of memory available to SQL Server depends upon the amount of memory installed on the server, the architecture of the server, the version and edition of SQL Server and the amount of memory pressure being experienced by SQL Server. This pressure can be internal (SQL Server resources need memory) or external (operating system needs memory). SQL Server is designed to respond to memory pressure when necessary.

Potential Issue: Memory contention with the buffer pool.

Resolution: Increase memory available to SQL server.

This performance counter returns the total amount of dynamic memory the server is using for locks.

Lock pages in memory is used to prevent older versions of Windows and SQL from allowing Windows operating system page out of the buffer pool.

Potential Issue: Lock pages in memory determines which accounts can use a process to keep data in physical memory, which prevents the system from paging the data to virtual memory on disk. Exercising this privilege could significantly affect system performance by decreasing the amount of available RAM.

Resolution: Upgrade to Windows 2008 R2 or greater and SQL 2008 or greater.

This performance counter returns the total amount of memory currently granted to executing processes, such as Hash, Sort, Bulk Copy, and Index creation operations.

This performance counter tells you how much memory has currently been granted to running queries. If there is memory pressure because of workspace memory, this value should be at least 25% of the virtual memory available to SQL Server.

Potential Issue: If the memory pressure is severe, the server might return errors such as 701 or 8645.

Resolution: If this is the case, this might be a good reason to consider using SQL Server 64-bit.

This performance counter returns the total amount of dynamic memory the server is using for query optimization.

There are no thresholds associated with this performance counter.

This monitor returns the total amount of dynamic memory the server is using for maintaining connections.

SQL Server sets aside three packet buffers for every connection made from a client. Each buffer is sized according to the default network packet size specified by the sp_configure stored procedure. If the default network packet size is less than 8KB, the memory for these packets comes from SQL Server's buffer pool. If it is 8KB or larger, the memory is allocated from SQL Server's MemToLeave region. It is worth noting that the default network packet size for the .NET Framework Data Provider for SQL Server is 8KB, so the buffers associated with managed code client connections typically come from SQL Server's MemToLeave region. This contrasts with classic ADO applications, where the default packet size is 4KB, and the buffers are allocated form the SQL Server buffer pool.

This monitor returns the total number of processes waiting for a workspace memory grant.

Memory resources are required for each user request. If sufficient memory is not available, the user waits until there is adequate memory for the query to run.

Potential Issue: Returned values greater than zero for a sustained period of time is a very strong indicator of memory pressure.

Resolution: You should first examine the database design, queries, and indexes to ensure the system is properly tuned before installing additional RAM. There may be query inefficiencies in the instance that is causing excessive memory grant requirements. For example, large Sorts or Hashes that can be resolved by tuning the indexing or queries being executed.

Compare with Memory Grants Outstanding. If the number of pending grants increases, try the following:

• Add more memory to SQL Server
• Add more physical memory to the server.
• Check for memory pressure. See and correct indexing if you experience “Out of memory” conditions.

This performance counter returns the total number of processes that have successfully acquired a workspace memory grant.

Look at Memory Grants Outstanding and Memory Grants Pending. If you see a long queue of pending grants as compared to outstanding grants, there is likely memory pressure because of query workspace memory. You can confirm this by checking the Granted Workspace Memory (KB) performance counter that tells you how much memory has currently been granted to running queries.

Potential Issue: A returned value that is high can indicate peak user activity. If there is memory pressure because of workspace memory, this value should be at least 25% of the virtual memory available to SQL Server. If the memory pressure is severe, the server might even return errors such as 701 or 8645.

Resolution: If severe, and using 32-bit, consider using SQL Server 64-bit. See Memory Grants Pending.

This performance counter displays the rate at which pages are read from or written to disk to resolve hard page faults.

This is a primary indicator of the kinds of faults that cause system-wide delays. This should be close to zero on a dedicated SQL Server. You will see spikes during backups and restores, but this is normal.

Potential Issue: High values causes hard page faults, which can cause SQL Server to use the page, as opposed to RAM.

Resolution: You may want to add additional RAM to stop the paging.

This metric is the ratio between Cache Hits and Lookups. Cache Hit Ratio measures how much the plan cache is being used.

A high percentage here means that your SQL Server is not building a new plan for every query it is executing and is working effectively and efficiently. A low percentage here means that, for some reason, the SQL Server is doing more work than it needs to. This metric needs to be considered alongside the plan cache reuse metric which looks at the spread of plan reuse through your cache.

Plan cache is memory used to store objects such as stored procedures, ad hoc and prepared Transact-SQL statements, and triggers. The plan cache is the component of SQL memory that stores query plans for re-use. When a query is issued against SQL, the optimizer attempts to re-use a cached plan if the traits of the query permit - but can only do so if that plan resides in cache, otherwise it needs to compile a new plan. This is not to say that every plan that is cached is re-used. Changes in schema, a query running parallel that may have run serially before, or changes in parameters may require a new plan to be compiled even if a similar plan exists in cache. Plan compilations are expensive though. Ideally this counter should be near 100%.

Potential Issue: The value of this counter should never fall below 90%. Low cache hit ratio (<20%) along with a sustained query execution rate (Batch Requests/sec) indicates that compiled plans are not being re-used. It should be noted that the hit ratio counter may be skewed by internal lookups performed.

Resolution: The amount of caching should be reduced by examining the workload to see if queries have been parameterized, or can be rewritten with stored procedures.

This is the amount of available physical memory on the server.

An acceptable output for this may vary widely based on how much physical memory is in the machine. If you have 2GB of RAM installed on the machine, it is common to see SQL Server use 1.7GB of RAM. If no other processes are running on your SQL Server, ensure you have at least 80MB available for Windows at any given time.

Potential Issue: Low values show that SQL server has lack of memory.

Resolution: Install additional memory.

This shows the percentage of the page file that is being utilized.

A Page File is simply a file on the hard drive that handles situations where the system wants to move or “page out” sections of memory. There are several situations that cause this, but the one you should be most concerned about is when the system is out of physical memory.

Potential Issue: Values greater than 70 percent indicate paging and lack of memory. If the system runs out of memory it can “borrow” some storage from the hard drive to release some memory until it needs that data again. The reason that this is bad is because hard drives are amazingly slow in comparison with solid-state memory access. Using the page file slows SQL Server a great deal.

Resolution: Install additional memory.

This is the number of work files created per second.

For example, work files could be used to store temporary results for hash joins and hash aggregates. The returned value should be less than 20. Tempdb work files are used in processing hash operations when the amount of data being processed is too large to fit into the available memory.

Potential Issue: High values can indicate thrash in the tempdb file as well as poorly coded queries.

Resolution: It is possible to reduce the value this monitor returns by making queries more efficient by adding/changing indexes. Adding additional memory also helps.