Pages

This resource shows page status of the SQL server.

In SQL Server, the page size is 8 KB. Therefore, SQL Server databases have 128 pages per MB. Each page starts with a 96 byte header that stores information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.

The icons of the listed performance counters in the Statistic Name column change color to reflect the listed counter's current state. Clicking any performance counter in the Statistic Name column takes you to the Performance Counter Details page for that counter.

If the value of a listed performance counter approaches or crosses the Warning threshold, a yellow band will appear for the listed counter allowing plotted values that cross the threshold to easily be visualized against the yellow band. If the value of a listed performance counter approaches or crosses the Critical threshold, a red band will appear for the listed counter allowing plotted values that cross the threshold to easily be visualized against the red band. Hovering over any time period within the chart will display detailed information about that time period in the tooltip. Hovering over a counter will give you detailed information about that performance counter.

Counter Expert Knowledge

Page Lookups/sec:

 

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.

Page Reads/sec

 

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.

Page Lookups/
Batch Request

 

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.

Page Writes/sec

 

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.

Page Faults/sec

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.

Page Splits/Batch Request

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.

Page Splits/sec

 

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.

Page Life Expectancy

 

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 in order 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.

Database Pages

 

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.

Free Memory

Note: This performance counter is only available in SQL Server 2012.

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

Free Pages

This performance counter displays the total number of pages on all free lists.

This is not main indicator of memory problems and could only used as signal of possible memory pressure.

Potential Issue: If this performance counter is critical and other memory counters are good, it is possible that there are no problems with memory.

Resolution: Check other memory counters. If they have critical values, you may try to install additional memory into SQL server.

Zooming

You can have the chart show a predetermined time period of data by clicking on any one of the three Zoom buttons in the head of the Zoom column. Alternatively, you can have the chart show a specific date range by dragging the mouse over an area of the chart. The time and date displayed at the top of the resource shows the amount of data currently loaded into the chart. This can be changed by clicking Edit from within the resource.

The Value from Last Poll column shows the most recent data available for the listed statistic.