This performance counter returns the number of requests to find a page in the buffer pool.

(Page lookups/sec) / (Batch Requests/sec) > 100.

Potential Issue: When the ratio of page lookups to batch requests is much greater than 100, this is an indication that while query plans are looking up data in the buffer pool, these plans are inefficient.

Resolution: Identify queries with the highest amount of logical I/O's and tune them.

This performance counter returns the number of physical database page reads issued.

80 - 90 physical database page reads per second is normal.

Potential Issue: Returned values that are high could indicate indexing or memory constraint.

Resolution: Attempt to tune the application so that fewer I/O operations are required. For example, perhaps I/O operations would be reduced if there were the appropriate indexes, or if the database design were de-normalized. If the applications cannot be tuned, you need to acquire disk devices with more capacity. Because physical I/O operations are expensive, you may be able to minimize the cost either by using a larger data cache, intelligent indexes, more efficient queries, or by changing the database design.

This performance counter displays the number of page splits per second that occur as the result of overflowing index pages.

(Page lookups/sec) / (Batch Requests/sec) > 100.

Potential Issue: When the ratio of page lookups to batch requests is much greater than 100, this is an indication that while query plans are looking up data in the buffer pool, these plans are inefficient.

Resolution: Identify queries with the highest amount of logical I/O's and tune them.

This performance counter returns the number of physical database page writes issued.

80 - 90 physical database page writes per second is normal.

Potential Issue: If the returned values are high, you should check the Lazy Writer/sec monitor.

This performance counter returns the average number of pages faulted per second.

This performance counter gives an idea of how many times information being requested is not where the application expects it to be. The information must either be retrieved from another location in memory or from the pagefile. While a sustained value may indicate trouble, you should be more concerned with hard page faults that represent actual reads or writes to the disk. Disk access is much slower than RAM.

Potential Issue: Any measurement higher than zero delays response time and probably indicates that more RAM is needed.

Resolution: Add additional memory to your SQL server.

This performance counter displays the number of page splits per second that occur as the result of overflowing index pages.

The returned value needs to be low as possible.

Potential Issue: High values could mean poor table or index design.

Resolution: If the number of page splits is high, consider increasing the Fill Factor of your indexes. An increased Fill Factor helps to reduce page splits because there is more room in data pages before it fills up and a page split has to occur. Note that this counter also includes the new page allocations as well and does not necessarily pose a problem. The other place we can confirm the page splits that involve data or index rows moves are the fragmented indexes on page splits.

This performance counter returns the number of page splits per second.

The returned value for this monitor should be as low as possible.

Potential Issue: Returned values that are high can indicate the overflowing of index pages. A high value for this counter is not bad in situations where many new pages are being created, since it includes new page allocations.

Resolution: To avoid Page Splits, review the table and index design so as to reduce non-sequential inserts. You can also implement Fillfactor and Pad_Index to leave more empty space per page.

This performance counter returns the number of seconds a page stays in the buffer pool without references.

This performance monitor reports, on average, how long data pages are staying in the buffer.

Potential Issue: If this value gets below 300 seconds, this is a potential indication that your SQL Server could use more memory to boost performance. Consistently having pages that stay in memory for less than that amount of time may indicate the need for more memory.

Resolution: Add additional memory to your SQL server.

This metric tells you the number of database pages that are currently being occupied in the data cache.

The higher the buffer manager Database Pages is, the less room there is for SQL Server to cache more data pages. This means that SQL Server may have to free up data cache pages order to make room for pages being moved in from disk to the data cache, which can increase disk IO and hurt performance. There are no specific thresholds for this counter as each server is different. Instead, watch baseline values and look for sudden changes in the baseline value.

Potential Issue: If the value for this performance counter increases above its typical baseline value, this may indicate memory pressure for the SQL Server instance.

Resolution: Investigate buffer management and disk I/O.

The amount of memory available for SQL Server to use.

When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free memory. Maintaining this free memory prevents the operating system (OS) from paging. If less memory is free, SQL Server releases memory to the OS. If more memory is free, SQL Server may allocate more memory. SQL Server adds memory only when its workload requires more memory; a server at rest does not increase the size of its virtual address space. The returned value should be as high as possible.

Potential Issue: If Resource Semaphore does not find enough free memory, then it puts the current query into the waiting queue.

Resolution:

• Increase RAM
• Increase SQL Server’s Max Server Memory
• Consider OS requirements and other instances