On This Page

• Tool Parameters
• Cache
• Cache Lazy Write Flushes/sec (Alerts: 0|0)
• LogicalDisk
• LogicalDisk Disk Overwhelmed (Alerts: 7|3)
• LogicalDisk % Free Space (Alerts: 0|0)
• LogicalDisk Avg. Disk sec/Read (Alerts: 43|17)
• LogicalDisk Avg. Disk sec/Write (Alerts: 9|9)
• LogicalDisk % Idle Time (Alerts: 0|23)
• LogicalDisk Disk Transfers/sec (Stats only)
• LogicalDisk Bytes/Read (Alerts: 0|38)
• LogicalDisk Bytes/Write (Alerts: 0|32)
• LogicalDisk Avg. Disk Queue Length (Alerts: 0|14)
• LogicalDisk Current Disk Queue Length (Alerts: 0|5)
• LogicalDisk Disk Bytes/sec (Stats only)
• LogicalDisk Free Megabytes (Alerts: 0|0)
• LogicalDisk Avg. Disk sec/Transfer (Alerts: 25|20)
• Network Interface
• Network Interface % Network Utilization (Alerts: 0|0)
• Network Interface Output Queue Length (Alerts: 0|0)
• Network Interface % Network Utilization Sent (Alerts: 0|0)
• Network Interface % Network Utilization Received (Alerts: 0|0)
• Network Interface Packets Outbound Errors (Alerts: 0|0)
• Network Interface Bytes Total/sec (Stats only)
• Network Interface Current Bandwidth (Stats only)
• Network Interface Packets/sec (Stats only)
• Network Interface Packets Sent/sec (Stats only)
• Network Interface Packets Received/sec (Stats only)
• Paging File
• Paging File % Usage (Alerts: 0|0)
• PhysicalDisk
• PhysicalDisk Read Latency Analysis (Alerts: 43|17)
• PhysicalDisk Write Latency Analysis (Alerts: 9|9)
• PhysicalDisk Current Disk Queue Length (Alerts: 0|5)
• PhysicalDisk Avg. Disk Queue Length (Alerts: 0|14)
• PhysicalDisk Disk Bytes/sec (Stats only)
• Process
• Process % Privileged Time (Alerts: 1|10)
• Process % Processor Time (Alerts: 0|0)
• Processor
• Processor % Processor Time (Alerts: 48|47)
• Processor % Privileged Time (Alerts: 11|13)
• Processor % Interrupt Time (Alerts: 0|0)
• Processor % DPC Time (Alerts: 0|20)
• Processor % User Time (Stats only)
• Processor DPC Rate (Stats only)
• Server
• Server Pool Nonpaged Failures (Stats only)
• Server Pool Paged Failures (Stats only)
• SQLServer:Access Methods
• SQLServer:Access Methods Forwarded Records/sec (Alerts: 0|0)
• SQLServer:Access Methods FreeSpace Scans/sec (Alerts: 0|15)
• SQLServer:Access Methods Full Scans/sec (Stats only)
• SQLServer:Access Methods Page Splits/sec (Alerts: 0|1)
• SQLServer:Access Methods Scan Point Revalidations/sec (Alerts: 0|15)
• SQLServer:Access Methods Workfiles Created/sec (Alerts: 0|36)
• SQLServer:Access Methods Worktables Created/sec (Alerts: 0|0)
• SQLServer:Access Methods Index Searches/sec (Alerts: 0|39)
• SQLServer:Buffer Manager
• SQLServer:Buffer Manager Buffer cache hit ratio (Alerts: 0|18)
• SQLServer:Buffer Manager Free pages (Alerts: 0|39)
• SQLServer:Buffer Manager Lazy writes/sec (Alerts: 1| 0)
• SQLServer:Buffer Manager Page life expectancy (Alerts: 1| 0)
• SQLServer:Buffer Manager Page lookups/sec (Alerts: 0|39)
• SQLServer:Buffer Manager Page reads/sec (Alerts: 0|17)
• SQLServer:Buffer Manager Page writes/sec (Alerts: 0|34)
• SQLServer:General Statistics
• SQLServer:General Statistics Logins/sec (Alerts: 0|39)
• SQLServer:General Statistics Logouts/sec (Alerts: 0|39)
• SQLServer:General Statistics User Connections (Alerts: 0|7)
• SQLServer:Latches
• SQLServer:Latches Latch Waits/sec (Stats only)
• SQLServer:Latches\Total Latch Wait Time (ms) (Stats only)
• SQLServer:Locks
• SQLServer:Locks Lock Requests/sec (Stats only)
• SQLServer:Locks Lock Waits/sec (Stats only)
• SQLServer:Locks Lock Wait Time (ms) (Stats only)
• SQLServer:Locks Lock Timeouts/sec (Stats only)
• SQLServer:Locks Number of Deadlocks/sec (Stats only)
• SQLServer:Memory Manager
• SQLServer:Memory Manager Memory Grants Pending (Alerts: 0|0)
• SQLServer:SQL Statistics
• SQLServer:SQL Statistics Batch Requests/sec (Alerts: 0|39)
• SQLServer:SQL Statistics SQL Compilations/sec (Alerts: 0|39)
• SQLServer:SQL Statistics SQL Re-Compilations/sec (Alerts: 0|31)
• System
• System Processor Queue Length (Alerts: 0|0)
• System System Calls/sec (Stats only)
• Incomplete analyses
• Disclaimer

Cache Lazy Write Flushes/sec

Description: Lazy Write Flushes/sec is the rate at which the Lazy Writer thread has written to disk. Lazy Writing is the process of updating the disk after the page has been changed in memory, so that the application that changed the file does not have to wait for the disk write to be complete before proceeding. More than one page can be transferred by each write operation.

The lazy writer writes 20% of the dirty pages every second, but increases the number of lazy write flushes if it is unable to keep up with the rate of dirty pages.
This analysis checks for more than 100 lazy write flushes/second which might indicate that the lazy writer is falling behind in writing to the disk.

LogicalDisk Disk Overwhelmed

Description: This analysis is an attempt to determine if a logical disk is overwhelmed by making a complicated formula into a simple good (green), warning (yellow), or critical (red) status.

This analysis takes into consideration the workload of the disk queue, the size of the IO, and the response times to compute a good or bad condition in regards to if the disk is overwhelmed or not. If Avg Disk Queue Length is greater than 1 and response times are greater than 25 ms for IO sizes of 64 KB or smaller or 35 ms for IO sizes greater than 64 KB, then the disk is overwhelmed. The reasoning is that the disk has a nearly constant IO demand (Avg Disk Queue Length is a calculation of Transfers/sec and sec/Transfer) and the response times are higher than what it would take a 7200 RPM disk drive to return the appropriate IO sizes. This analysis requires \LogicalDisk(*)\Avg. Disk Queue Length, \LogicalDisk(*)\Avg. Disk Bytes/Transfer, and \LogicalDisk(*)\Avg. Disk sec/Transfer counters to be in the counter log. Instances of _Total are ignored because they are aggregates of all counter instances.

If the PAL generated counter of \LogicalDisk(*)\Disk Overwhelmed has a value of 1 (Warning), then it means that the Avg Disk Queue Length is greater than 1 and the response times (Avg. Disk sec/Transfer) are greater than 15 ms. If this counter has a value of 2 (Critical), then it means thatAvg Disk Queue Length is greater than 1 and the response times are greater than 25 ms for IO of 64 KB or smaller and 35 ms for IO sizes greater than 64 KB.

LogicalDisk % Free Space

Description: % Free Space is the percentage of total usable space on the selected logical disk drive that was free. Low to no free disk space can cause severe disk performance problems.This analysis checks for less than 10% free disk space (Warning alert) and less than 5% free disk space (Critical alert).

LogicalDisk Avg. Disk sec/Read

Description: Avg. Disk sec/Read is the average time, in seconds, of a read of data to the disk. This analysis determines if any of the logical disks are responding slowly.

The following thresholds are based on the access times of 5400 RPM disk drives. Hard drives that are faster than 5400 RPM such as 7200 RPM and solid state drives should have faster response times. Occasional spikes above 25 ms are normal.

If the response times are less than 0.015 (15 milliseconds), then the disk subsystem is keeping up with demand.

If the response times are greater than 0.025 (25 milliseconds), then the disk subsystem is likely overwhelmed.

Reference:
Ruling Out Disk-Bound Problems
http://technet.microsoft.com/en-us/library/5bcdd349-dcc6-43eb-9dc3-54175f7061ad.aspx

LogicalDisk Avg. Disk sec/Write

Description: Avg. Disk sec/Write is the average time, in seconds, of a read of data to the disk. This analysis determines if any of the logical disks are responding slowly.

The following thresholds are based on the access times of 5400 RPM disk drives. Hard drives that are faster than 5400 RPM such as 7200 RPM and solid state drives should have faster response times. Occasional spikes above 25 ms are normal.

If the response times are less than 0.015 (15 milliseconds), then the disk subsystem is keeping up with demand.

If the response times are greater than 0.025 (25 milliseconds), then the disk subsystem is likely overwhelmed.

Reference:
Ruling Out Disk-Bound Problems
http://technet.microsoft.com/en-us/library/5bcdd349-dcc6-43eb-9dc3-54175f7061ad.aspx

LogicalDisk % Idle Time

Description: % Idle Time reports the percentage of time during the sample interval that the disk was idle.

This analysis checks for a % Idle Time of less than 10. Zero (0) indicates that the disk contstanly has at least 1 outstanding I/O in the queue.

Reference:

• Ruling Out Disk-Bound Problems

LogicalDisk Disk Transfers/sec

Description: Disk Transfers/sec is the rate of read and write operations on the disk and is the number of IO operations per second (IOPS) according to the operating system. If hardware RAID is used, then keep in mind that the hardware IOPS will be different. For example, hardware RAID1 (mirror set) will have hardware IOPS of (1 x Read) + (2 x Write).

LogicalDisk Bytes/Read

Description: This analysis shows the size of logical disk reads per second. The size of an I/O request packets (IRP) can have a direct affect on the average response times from the disk. This analysis checks for I/O request sizes of 64 KB or larger. Correlate this analysis with the Avg. Disk Sec/Read and Avg. Disk Sec/Write analyses.

References:

• How to Speak SAN-ish

LogicalDisk Bytes/Write

Description: This analysis shows the size of logical disk writes per second. The size of an I/O request packets (IRP) can have a direct affect on the average response times from the disk. This analysis checks for I/O request sizes of 64 KB or larger. Correlate this analysis with the Avg. Disk Sec/Read and Avg. Disk Sec/Write analyses.

Reference:

• How to Speak SAN-ish

LogicalDisk Avg. Disk Queue Length

Description: Avg. Disk Queue Length is the average number of both read and write requests that were queued or "in-flight" for the selected disk during the sample interval.

This counter typically has a threshold of number of spindles + 2. Due to disk virtualization, it is difficult to determine the true number of physical spindles behind a logical disk or LUN, therefore this threshold is not a direct indicator of a disk performance problem.

This analysis uses a Warning alert for an average disk queue length greater than 2, but correlate this value with disk latency (Avg. Disk sec/Transfer).

References:

• How to Speak SAN-ish

LogicalDisk Current Disk Queue Length

Description: Current Disk Queue Length is the number of requests outstanding on the disk at the time the performance data is collected. It also includes requests in service at the time of the collection. This is a instantaneous snapshot, not an average over the time interval. Multi-spindle disk devices can have multiple requests that are active at one time, but other concurrent requests are awaiting service. This counter might reflect a transitory high or low queue length, but if there is a sustained load on the disk drive, it is likely that this will be consistently high. Requests experience delays proportional to the length of this queue minus the number of spindles on the disks.

This analysis checks if the number of I/O request packets (IRPs) in the disk queue are at 32 or higher. Many SAN vendors use 32 as a default setting for the Host Bus Adapter (HBA) which interfaces into the fibre channel network to connect to one or more SANs. If the queue depth (simultaneous in-flight I/O) is reached frequently, then the queue depth might need to be increased.

Reference:

• How to Speak SAN-ish

LogicalDisk Disk Bytes/sec

Description: Disk Bytes/sec is the rate bytes are transferred to or from the disk during write or read operations. A mirror pair (RAID1) 7200 RPM disk drives can deliver roughly 20 MB per second throughput.

LogicalDisk Free Megabytes

Description: Free Megabytes displays the unallocated space, in megabytes, on the disk drive in megabytes. One megabyte is equal to 1,048,576 bytes.

LogicalDisk Avg. Disk sec/Transfer

Description: Avg. Disk sec/Transfer is the time, in seconds, of the average disk transfer.

Network Interface % Network Utilization

Description: % Network Utilization doesn't exist as a normal performance counter, so this analysis uses multiplies \Network Interface(*)\Bytes Total/sec by 8 (to convert it to bits total/sec), divides it by \Network Interface(*)\Current Bandwidth, and multiplies the result by 100 to create a percentage. This analysis throws a warning alert when greater than 50 and throws a critical alert when greater than 80.

Network Interface Output Queue Length

Description:

Network Interface % Network Utilization Sent

Description: % Network Utilization Sent doesn't exist as a normal performance counter, so this analysis uses multiplies \Network Interface(*)\Bytes Sent/sec by 8 (to convert it to bits total/sec), divides it by \Network Interface(*)\Current Bandwidth, and multiplies the result by 100 to create a percentage. This analysis throws a warning alert when greater than 50 and throws a critical alert when greater than 80.

Network Interface % Network Utilization Received

Description: % Network Utilization Received doesn't exist as a normal performance counter, so this analysis uses multiplies \Network Interface(*)\Bytes Received/sec by 8 (to convert it to bits total/sec), divides it by \Network Interface(*)\Current Bandwidth, and multiplies the result by 100 to create a percentage. This analysis throws a warning alert when greater than 50 and throws a critical alert when greater than 80.

Network Interface Packets Outbound Errors

Description: Packets Outbound Errors is the number of outbound packets that could not be transmitted because of errors.

If errors are occuring during this analysis, network connectivity could be affected with a potential for random Outlook RPC dialog boxes. See http://technet.microsoft.com/en-us/library/aa997363.aspx and http://technet.microsoft.com/en-us/library/aa995850.asp for more information

Network Interface Bytes Total/sec

Description: Bytes Total/sec is the rate at which bytes are sent and received over each network adapter, including framing characters. Network Interface\Bytes Total/sec is a sum of Network Interface\Bytes Received/sec and Network Interface\Bytes Sent/sec.

Network Interface Current Bandwidth

Description: Current Bandwidth is an estimate of the current bandwidth of the network interface in bits per second (BPS). For interfaces that do not vary in bandwidth or for those where no accurate estimation can be made, this value is the nominal bandwidth.

Network Interface Packets/sec

Description: Packets/sec is the rate at which packets are sent and received on the network interface.

Network Interface Packets Sent/sec

Description: Packets Sent/sec is the rate at which packets are sent on the network interface.

Network Interface Packets Received/sec

Description: Packets Received/sec is the rate at which packets are received on the network interface.

Paging File % Usage

Description: The amount of the Page File instance in use in percent. See also Process\\Page File Bytes.

This analysis checks if the percentage of usage is greater than 70%.

Reference
How to determine the appropriate page file size for 64-bit versions of Windows Server 2003 or Windows XP
http://support.microsoft.com/kb/889654

PhysicalDisk Read Latency Analysis

Description: Avg. Disk sec/Read is the average time, in seconds, of a read of data to the disk. This analysis determines if any of the physical disks are responding slowly.

If the response times are greater than 0.015 (15 milliseconds), then the disk subsystem is keeping up with demand.

If the response times are greater than 0.025 (25 milliseconds), then the disk subsystem is likely overwhelmed.

Reference:
Ruling Out Disk-Bound Problems
http://technet.microsoft.com/en-us/library/5bcdd349-dcc6-43eb-9dc3-54175f7061ad.aspx

PhysicalDisk Write Latency Analysis

Description: Avg. Disk sec/Write is the average time, in seconds, of a write of data to the disk. This analysis determines if any of the physical disks are responding slowly.

If the response times are greater than 0.015 (15 milliseconds), then the disk subsystem is keeping up with demand.

If the response times are greater than 0.025 (25 milliseconds), then the disk subsystem is likely overwhelmed.

Reference:
Ruling Out Disk-Bound Problems
http://technet.microsoft.com/en-us/library/5bcdd349-dcc6-43eb-9dc3-54175f7061ad.aspx

PhysicalDisk Current Disk Queue Length

Description: Current Disk Queue Length is the number of requests outstanding on the disk at the time the performance data is collected. It also includes requests in service at the time of the collection. This is a instantaneous snapshot, not an average over the time interval. Multi-spindle disk devices can have multiple requests that are active at one time, but other concurrent requests are awaiting service. This counter might reflect a transitory high or low queue length, but if there is a sustained load on the disk drive, it is likely that this will be consistently high. Requests experience delays proportional to the length of this queue minus the number of spindles on the disks. For good performance, this difference should average less than two.

If the server is using an HBA (Host Bus Adapter: This is used to connect to a Storage Area Network SAN) and if the Current Disk Queue Length goes up to 32 frequently, then consider increasing the queue depth on the HBA to allow more concurrent I/O to the SAN. Please consult your SAN administrator before making any changes.

PhysicalDisk Avg. Disk Queue Length

Description: Avg. Disk Queue Length is the average number of both read and write requests that were queued for the selected disk during the sample interval.

PhysicalDisk Disk Bytes/sec

Description: Disk Bytes/sec is the rate bytes are transferred to or from the disk during write or read operations.

Process % Privileged Time

Description: \Process % Privileged Time / Process(sqlservr)\% Privileged Time

Description: % Privileged Time is the percentage of elapsed time that the process threads spent executing code in privileged mode. When a Windows system service is called, the service will often run in privileged mode to gain access to system-private data. Such data is protected from access by threads executing in user mode. Calls to the system can be explicit or implicit, such as page faults or interrupts. Unlike some early operating systems, Windows uses process boundaries for subsystem protection in addition to the traditional protection of user and privileged modes. Some work done by Windows on behalf of the application might appear in other subsystem processes in addition to the privileged time in the process.

Privileged or kernel mode is the processing mode that allows code to have direct access to all hardware and memory in the system. I/O operations and other system services run in privileged (kernel) mode; user applications run in user mode. Unless the processes are graphics-intensive or I/O-intensive such as file and print services, most applications should not be processing much work in kernel mode. Privileged mode corresponds to the percentage of time the processor spends on execution of Microsoft Windows kernel commands, such as processing of SQL Server I/O requests. If this counter is consistently high when the Physical Disk counters are high, consider installing a faster or more efficient disk subsystem.

Note: Different disk controllers and drivers use different amounts of kernel processing time. Efficient controllers and drivers use less privileged time, leaving more processing time available for user applications, increasing overall throughput.

Threshold:
Yellow: SQL Server is using more than 20% Privileged (kernel) mode CPU usage
Red: SQL Server is using more than 30% Privileged (kernel) mode CPU usage

Next Steps: The key piece to diagnosing high processor conditions is to determine the ratio of privileged mode to user mode CPU. The counter '\Processor\% Processor Time' is the sum of '\Processor\% Privileged Time' and '\Processor\% User Time'. If Privileged Time is pushing the %Processor Time higher then it is due to processes executing in kernel mode. If '% User Time' is causing the % Processor Time to be higher then it is likely a user mode process that is causing the pressure. If %Privileged Time is consistently high or shows high under load, it could be several issues. The most common reason for high %Privileged Time is disk pressure which can be measured by correlating this counter with Physical Disk reads / sec and Physical Disk writes / sec. If these are also high you may also see a high number of Page Latch Waits for SQL Server which can be measured by examining the sys.dm_os_wait_stats dynamic management view and the perfmon SQL Server:Wait Statistics perfmon counters. If SQL Server Memory Manager: Page Life Expectancy is also low try to address by reducing the number of queries that are performing a high number of logical reads by adding indexes, ensuring that statistics are up to date, and potentially rewriting the query. You could add more physical RAM to help raise Page Life Expectancy if it is low (lower than your baseline, or critical when under 300) although we only recommend adding memory as an absolute last resort. We first recommended addressing design and addressing poor indexing first. Adding physical RAM only masks the real issue. The other potential reasons for high privileged mode are related to out of date drivers, BIOS being out of date, failing components, processes that run in kernel mode such as anti-virus, and other potential issues.
Reference:
Monitoring CPU Usage
http://msdn.microsoft.com/en-us/library/ms178072.aspx
Ask the Performance Team http://blogs.technet.com/askperf/archive/2008/01/18/do-you-know-where-your-processor-spends-its-time.aspx
Clint Huffman's Windows Troubleshooting in the Field Blog http://blogs.technet.com/clinth/archive/2009/10/28/the-case-of-the-2-million-context-switches.aspx

Process % Processor Time

Description: This analysis checks all of the processes to determine if any of the processes are consuming a large amount of CPU.

If a user-mode processor bottleneck is suspected, then consider using a process profiler to analyze the functions causing the high CPU consumption. See How To: Identify Functions causing a High User-mode CPU Bottleneck for Server Applications in a Production Environment article in the references section for more information.

Role Specific

- \Process(MSExchangeMailboxAssistants*)\% Processor Time should be less than 5% of overll CPU

- \Process(msftefd*)\% Processor Time should be less than 10% of what the store process is consuming. Note: If indexing is running and overall CPU is greater than 80%, then msfte should backoff it's CPU usage if that threshold is hit.

References:
Measuring .NET Application Performance
http://msdn2.microsoft.com/en-us/library/ms998579.aspx

How To: Identify Functions causing a High User-mode CPU Bottleneck for Server Applications in a Production Environment http://www.codeplex.com/PerfTesting/Wiki/View.aspx?title=How%20To%3a%20Identify%20a%20Disk%20Performance%20Bottleneck%20Using%20SPA&referringTitle=How%20Tos

Processor % Processor Time

Description: % Processor Time is the percentage of elapsed time that the processor spends to execute a non-Idle thread. It is calculated by measuring the duration of the idle thread is active in the sample interval, and subtracting that time from interval duration. This counter is the primary indicator of processor activity, and displays the average percentage of busy time observed during the sample interval. % Processor Time is the sum of % User Time and % Privileged Time unless there is hardware involvement in the form of interupts and/or DPCs.

This analysis creates a Warning alert for utilization greater than 50% on any processor and creates a critical alert for utilization greater than 80%.

If average processor utilization is high based on the thresholds witin this analysis, then check if it is high user mode CPU or high privileged mode. If high privileged mode CPU is suspected, then see the Privileged Mode CPU Analysis. If a user-mode processor bottleneck is suspected, then consider using a process profiler to analyze the functions causing the high CPU consumption. See How To: Identify Functions causing a High User-mode CPU Bottleneck for Server Applications in a Production Environment article in the references section for more information.
References:

• Measuring .NET Application Performance
• How To: Identify Functions causing a High User-mode CPU Bottleneck for Server Applications in a Production Environment

Processor % Privileged Time

Description: This counter indicates the percentage of time a thread runs in privileged mode also known as kernel mode. When your application calls operating system functions (for example to perform file or network I/O or to allocate memory), these operating system functions are executed in privileged mode.

High privileged mode CPU indicates that computer is spending too much time in system I/O versus real (user mode) work. % Privileged Time is the percentage of elapsed time that the process threads spent executing code in privileged mode. When a Windows system service in called, the service will often run in privileged mode to gain access to system-private data. Such data is protected from access by threads executing in user mode. Calls to the system can be explicit or implicit, such as page faults or interrupts. Unlike some early operating systems, Windows uses process boundaries for subsystem protection in addition to the traditional protection of user and privileged modes. Some work done by Windows on behalf of the application might appear in other subsystem processes in addition to the privileged time in the process.

This analysis throws a warning alert if privileged mode CPU is consuming more than 20% of total CPU and a critical alert if consuming More than 30% of total CPU.

Next steps
The CPU consumption might be caused by another busy resource such as network, memory, or disk I/O. High privileged mode CPU can also by caused by high amounts of Context Switches/second. See the High Context Switches/second analysis. The KernRate (KrView) tool can be used to profile the kernel to see what component is consuming the most kernel resources. To see more information about how KernRate can be used to analyze high priviledge mode CPU problems, see Mark Russinovich's blog entry in the references section below.

References:

• Measuring .NET Application Performance
• Mark's Blog : The Case of the System Process CPU Spikes

Processor % Interrupt Time

Description: % Interrupt Time is the time the processor spends receiving and servicing hardware interrupts during sample intervals. This value is an indirect indicator of the activity of devices that generate interrupts, such as the system clock, the mouse, disk drivers, data communication lines, network interface cards and other peripheral devices. These devices normally interrupt the processor when they have completed a task or require attention. Normal thread execution is suspended during interrupts. Most system clocks interrupt the processor every 10 milliseconds, creating a background of interrupt activity. A dramatic increase in this counter indicates potential hardware problems.

This analysis checks for % Interrupt Time greater than 30%. If this occurs, then consider updating devices drivers for hardware that correlates to this alert.

References:
Measuring .NET Application Performance
http://msdn2.microsoft.com/en-us/library/ms998579.aspx

Processor % DPC Time

Description: % DPC Time is the percentage of time that the processor spent receiving and servicing deferred procedure calls (DPCs) during the sample interval. DPCs are interrupts that run at a lower priority than standard interrupts. % DPC Time is a component of % Privileged Time because DPCs are executed in privileged mode. They are counted separately and are not a component of the interrupt counters. This counter displays the average busy time as a percentage of the sample time.

Processor % User Time

Description: % User Time is the percentage of elapsed time the processor spends in the user mode. User mode is a restricted processing mode designed for applications, environment subsystems, and integral subsystems. The alternative, privileged mode, is designed for operating system components and allows direct access to hardware and all memory. The operating system switches application threads to privileged mode to access operating system services. This counter displays the average busy time as a percentage of the sample time.

This analysis provides statistics only. Threads running on a processor will be in either user mode measured using % User Time or in priviledge/kernel mode measured using % Privileged Time. High % User Time indicates a high amount of application code is being executed. This is desirable versus too much time in privileged mode. See the Processor % Privileged Time analysis for more information.

Processor DPC Rate

Description: DPC Rate is the rate at which deferred procedure calls (DPCs) were added to the processors DPC queues between the timer ticks of the processor clock. DPCs are interrupts that run at alower priority than standard interrupts. Each processor has its own DPC queue. This counter measures the rate that DPCs were added to the queue, not the number of DPCs in the queue. This counter displays the last observed value only; it is not an average.

Server Pool Nonpaged Failures

Description: The number of times allocations from nonpaged pool have failed. Indicates that the computer's physical memory is too small.

Server Pool Paged Failures

Description: The number of times allocations from paged pool have failed. Indicates that the computer's physical memory or paging file are too small.

SQLServer:Access Methods Forwarded Records/sec

Description: SQLServer:Access Methods Forwarded Records/sec

Description: Rows with varchar columns on tables without a clustered index can experience expansion when varchar values are updated with a longer string. In the case where the row cannot fit in the existing page, the row migrates and access to the row will traverse a pointer. Forwarded records occur when a data record in a heap increases in size and the records current page does not have the space to store the size increase. The record is moved to a new location, becoming a forwarded record, and the forwarding record is left in the original location to point to the real location of the record. The forwarded record points back to the forwarding record in case its location ever needs to change again. Access Methods Forwarded Records/sec measures the number of records fetched through forwarded record pointers which are due to tables without a clustered index. A forwarded record is basically a pointer. For instance, if you start with a short row, and update the row creating a wider row, the row might not fit on the data page. A pointer is put in its location and the row is forwarded to another page. This is done as a performance optimization so that all the non-clustered indexes on the heap do not have to be altered with the new location of the heap record. If a table has lots of forwarded records, scanning the table can be very inefficient. Also, rows with varchar columns can experience expansion when varchar values are updated with a longer string. In the case where the row cannot fit in the existing page, the row migrates and access to the row will traverse a pointer. Forwarded Records only occurs on heaps which are tables without clustered indexes. Threshold: (Yellow) - This value should not be greater than 10% of the number of Batch Requests/Sec

Next Steps:
Look at code to determine where the short row is inserted followed by an update. Forwarded records can be avoided by:

Using default values so that an update does not result in a longer row that is the root cause of forwarded records.

Using Char instead of Varchar. Using Char creates a fixed length so that an update does not result in a longer row.

Evaluate clustered indexes for heap tables.

In cases where clustered indexes cannot be used, drop non-clustered indexes, build a clustered index to reorganize pages and rows, drop the clustered index, and then recreate non-clustered indexes.

Learn to use the sys.dm_db_index_physical_stats dynamic management view (DMV) to find forwarded records. In the sys.dm_db_index_physical_stats DMV there is a column used called the forwarded_record_count which counts the number of records in a heap that have forward pointers to another data location. (This state occurs during an update, when there is not enough room to store the new row in the original location.)
Reference:
SQL Server Storage Engine http://blogs.msdn.com/sqlserverstorageengine/archive/2006/09/19/761437.aspx
Forwarding and forwarded records, and the back-pointer size http://www.sqlskills.com/BLOGS/PAUL/post/Forwarding-and-forwarded-records-and-the-back-pointer-size.aspx
sys.dm_db_index_physical_stats (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188917.aspx
SQL Server, Access Methods Object
http://msdn.microsoft.com/en-us/library/ms177426.aspx

SQLServer:Access Methods FreeSpace Scans/sec

Description: SQLServer:Access Methods FreeSpace Scans/sec
Description:
This counter represents inserts into a table with no physical ordering of the rows. A table with no ordering, without a clustered index, is known as a heap table. Inserts into heaps will require SQL Server to perform freespace scans to identify pages with free space to insert rows. A heap table also requires an additional, internal column called an uniquifier to be generated for each row inserted. Extra processing is required to define and store a heap table since SQL Server normally uses the clustered index as a storage mechanism for the table data. Freespace scans have an additional I/O expense for inserts and can possibly cause contention on the GAM, SGAM, and PFS pages when there are many connections inserting. It is usually recommended that you physically order the table rows by using a clustered index on the table. FreeSpace Scans/sec represents inserts into a table with no physical ordering of its rows which is called a heap. A heap table requires an additional column called an uniquifier to be generated for each row inserted. It is recommended that you physically order the table rows by using a clustered on the table for most tables. FreeSpace Scans /sec measures the number of scans per second that were initiated to search for free space within pages already allocated to an allocation unit to insert or modify record fragments. Each scan may find multiple pages. FreeSpace Scans are due to inserts into heaps that require SQL Server to perform freespace scans to identify pages with free space to insert rows. Freespace scans are an additional I/O expense for inserts and can possibly cause contention on the GAM, SGAM, and PFS pages when many spids are inserting. The solution is often to evaluate clustered index for base tables. One or more of the following symptoms may accompany poor performance during inserts to a large table on SQL Server:

Unexplained high CPU usage by SQL Server, sometimes up to 100%.

SQL Profiler or SHOWPLAN indicates that singleton inserts have wide variations in performance.

The number of reads is out of proportion to the number or size of the indexes and triggers on the table.

Sporadic timeouts.

The FreeSpace Scans/Sec counter from the SQL Server:Access Methods object in Performance Monitor is excessively high given all the factors involved in your specific environment. A common cause for these symptoms is that inserts to a heap (a table without a clustered index) are often slower than inserts to a table with a clustered index (a clustered table).
Threshold:
Yellow: A ratio (10%) of more than 1 freespace scan for every 10 Batch Requests/Sec
Next Steps: Microsoft recommends that you add a clustered index to the table and test the effect of the clustered index on performance. Reference:
PRB: Poor Performance on a Heap
http://support.microsoft.com/kb/297861
SQL Server, Access Methods Object
http://msdn.microsoft.com/en-us/library/ms177426.aspx

SQLServer:Access Methods Full Scans/sec

Description: SQLServer:Access Methods Full Scans/sec
Description: This counter monitors the number of full scans on base tables or indexes. High values indicate that we may be having performance issues due to table / index page scans. If we see high CPU and / or low Page Life Expectancy (PLE) then we need to investigate this counter; however, if full scans are on small tables we can ignore this counter. A few of the main causes of high Full Scans/sec are missing indexes, too many rows requested, queries with missing indexes, or too many rows requested will have a large number of logical reads and an increased CPU time. This analysis throws a Warning alert if the ratio of Index Searches/sec to Full Scans/sec is less than 1000 to 1 and if there are more than 1000 Index Searches/sec.

Threshold:
Yellow: A ratio of more than 1 full scan for every 1000 index searches. The value of Index Searches/sec and Full Scans/sec should be greater than 1000.

Formula: (AvgSQLServerAccessMethodsIndexSearchessecAll / AvgSQLServerAccessMethods_FullScanssec) < 1000

Next Steps: The main causes of high Full Scans/sec are:

Missing indexes

Too many rows requested Queries with missing indexes or too many rows requested will have a large number of logical reads and an increased CPU time. As mentioned, if there is a high level of Full Scans to Index Searches, then it is important to also check the following: See PERFMON SQLServer:Buffer Manager performance counters for memory pressure:

Page Life Expectancy

Checkpoint pages/sec

Lazy writes/sec A high number of scans can cause buffer pool pressure (as indicated with low PLE and a higher Lazy Writes / sec count). Memory pressure will quickly manifest into disk pressure, so also check: See PERFMON Physical Disk performance counters:

Disk sec/read

Disk sec/write Note: Identify disk bottlenecks by using Performance Counters, Profiler, sys.dm_io_virtual_file_stats and SHOWPLAN output. Also refer to the sys.dm_io_virtual_file_stats dynamic management view (DMV) to track io_stalls to help identify IO bottlenecks. To back up and support this information, compare the counters to sys.dm_os_wait_stats output. If you see high values in perfmon, you may also see high waits for the following:

ASYNC_IO_COMPLETION

IO_COMPLETION

PAGEIOLATCH_* (Data page I/O completion waits appear as PAGEIOLATCH_* waits) Reactively, SQL Profiler can be used to identify which SQL statements are causing scans. Use the scans event class and events scan:started and scan:completed. Include the object ID data column. Save the profiler trace to a file and then convert it to trace table. You can then search for the scans event. The scan:completed event provides the associated IO so that you can also search for high reads, writes, and duration.
Reference:
SQL Server, Access Methods Object http://msdn.microsoft.com/en-us/library/ms177426.aspx
SQL Server 2005 Waits and Queues http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
Wait Types and Correlation to Other Performance Info http://www.sqlmag.com/Files/09/40925/Webtable_01.doc

SQLServer:Access Methods Page Splits/sec

Description: SQLServer:Access Methods Page Splits/sec
Description: The number of page splits per second that occurs as the result of overflowing index pages. When a record is inserted into an index, it must be inserted in order. If the data page is full, the page splits in order to maintain the appropriate order. A high value for this counter may warrant the consideration of a lower fill factor. This value should be as low as possible. Heavily fragmented indexes may be the result of high page splits/sec.
Threshold:
Yellow: A ratio of more than 1 page split for every 20 batch requests Next Steps: If the number of page splits is high, consider increasing the fillfactor of your indexes. An increased fillfactor helps to reduce page splits by increasing the amount of free space on each page. Note: This counter also includes new page allocations, and does not mean there is an issue.
Reference:
SQL Server, Access Methods Object
http://msdn.microsoft.com/en-us/library/ms177426(v=SQL.105).aspx

SQLServer:Access Methods Scan Point Revalidations/sec

Description: \SQLServer:Access Methods\Scan Point Revalidations/sec

Description: Scan Point Revalidations occurs during range scans. When a range scan occurs there is an optimization process that occurs where the pages are marked as satisfied with the WHERE predicate that does the range scan. Instead of scanning through each and every row in the page, it does not keep an exclusive lock on those pages; instead it just keeps a mark on it and continues with rest of the scan. If one or more rows in the page are modified by update or a delete operation, the update or delete process will notify the scan to recheck the page to see if the page is still valid for the range scan. This recheck is called a Scan Point Revalidation. Scan Point Revalidations shows the contention between range scans and modifications to the same pages. This counter also pinpoints hotspots within the cluster table competing between reads and writes. Scan Point Revalidations are the number of times per second that the scan point had to be revalidated before the scan could be continued. If a page latch has to be released due to contention, the scan point must be revalidated when the scan resumes.

Threshold: Yellow: Greater than 10 per second Next Steps: It is important to correlate the Scan Count Revalidations/sec with the Range Scans/sec counter and Page Latch related counters. The higher the number of range scans on the same pages, the higher the number of scan point revalidations. High number of Scan Point Revalidations/sec indicates hot spots in the data, probably due to a poor choice of clustered index putting the most active rows on the same page. Consider reducing the number of range scans, isolating reporting and application use, and most importantly ensuring that the clustered index choice is the right one. Clustered indexes should be on columns that are sorted on, grouped on, used in joins, used in between queries, and in other operations where the order of the returned data is critical.
Reference:
SQL Server, Access Methods Object
http://msdn.microsoft.com/en-us/library/ms177426.aspx

SQLServer:Access Methods Workfiles Created/sec

Description: \SQLServer:Access Methods\Workfiles Created/sec

Description: Number of Workfiles created in the last second. Workfiles in TempDB are used in processing hash operations when the amount of data being processed is too big to fit into the available memory. Workfiles Created/Sec the number of work files created per second. Work files are similar to work tables but are created strictly by hashing operations. Work files are used to store temporary results for hash joins and hash aggregated when the amount of data being processed is too big to fit into the available SQL Server memory.
Threshold:
Yellow: A ratio of more than 1 workfile created for every 20 batch requests
Next Steps: Make queries more efficient by adding/changing indexes, adding additional memory, etc. Run expensive queries through the Database Tuning Advisor (DTA), add additional memory, and look for expensive queries and consider rewriting them.
Reference:
SQL Server, Access Methods Object
http://technet.microsoft.com/en-us/library/ms177426.aspx
Working with tempdb in SQL Server 2005
http://msdn.microsoft.com/en-us/library/cc966545.aspx
Troubleshooting Performance Problems in SQL Server 2008
http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/TShootPerfProbs2008.docx

SQLServer:Access Methods Worktables Created/sec

Description: \SQLServer:Access Methods\Worktables Created/sec

Description: Number of worktables created in the last second. The number of work tables created per second. Work tables are temporary objects and are used to store results for query spool, LOB variables, and cursors. Typically, this number is less than 200.
Threshold:
Yellow: This number should be less than 200. This will need to be baselined for accuracy.
Next Steps:
Look for expensive statements with high CPU, duration, and statements that run in parallel and tune them by adding indexes, reducing the volume of data being returned, and adding indexes where appropriate. Ensure that TempDB is not a bottleneck and is following best practices. If you determine that the throughput of your application has degraded because of contention in allocation structures, you can use the following techniques to minimize it. Evaluate your application and the query plans to see if you can minimize the creation of work tables and temporary tables. Monitor the perfmon counters as described in Monitoring contention caused by DML operations. Then, use SQL Profiler to correlate the values of these counters with the currently running queries. This helps you identify the queries that are causing the contention in allocation structures. Divide tempdb into multiple data files of equal size. These multiple files don’t necessarily be on different disks/spindles unless you are also encountering I/O bottlenecks as well. The general recommendation is to have one file per CPU because only one thread is active per CPU at one time. SQL Server allocates pages for tempdb objects in a round-robin fashion (also referred to as proportional fill) so that the latches on PFS and SGAM pages are distributed among multiple files. This is supported both in SQL Server 2000 and SQL Server 2005. There are improvements to the proportional fill algorithm in SQL Server 2005. Use TF-1118. Under this trace flag SQL Server allocates full extents to each tempdb object, thereby eliminating the contention on SGAM page. This is done at the expense of some waste of disk space in tempdb. This trace flag has been available since SQL Server 2000. With improvements in tempdb object caching in SQL Server 2005, there should be significantly less contention in allocation structures. If you see contention in SGAM pages, you may want to use this trace flag. Cached tempdb objects may not always be available. For example, cached tempdb objects are destroyed when the query plan with which they are associated is recompiled or removed from the procedure cache.
Reference:
SQL Server, Access Methods Object
http://technet.microsoft.com/en-us/library/ms177426.aspx
Working with tempdb in SQL Server 2005
http://msdn.microsoft.com/en-us/library/cc966545.aspx
Troubleshooting Performance Problems in SQL Server 2008
http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/TShootPerfProbs2008.docx

SQLServer:Access Methods Index Searches/sec

Description: SQLServer:Access Methods Index Searches/sec
Description: This counter measures the number of index searches per second. Index searches are used to start range scans, single index record fetches, and to reposition within an index. Index searches are preferable to index and table scans. For OLTP applications, optimize for more index searches and less scans preferably, 1 full scan for every 1000 index searches. Index and table scans are expensive I/O operations. The Access Methods Index Searches/sec is captured to compare to the Access Methods Full Scans/sec. Full Scans will lead to high logical reads which will deplete the Buffer Pool. A depleted Buffer Pool will lead to disk impact as SQL Server will not have memory available for queries. Additionally sudden decreases in Index Searches/Sec value may indicate an index is no longer being used.

Threshold:
Yellow: A ratio of more than 1 full scan for every 1000 index searches. The value of Index Searches/sec and Full Scans/sec should be greater than 1000. Formula: (AvgSQLServerAccessMethodsIndexSearchessecAll / AvgSQLServerAccessMethods_FullScanssec) < 1000 Next Steps: The main causes of high Full Scans/sec compare to Index Searches are:

Missing indexes

Too many rows requested Queries with missing indexes or too many rows requested will have a large number of logical reads and an increased CPU time. As mentioned, if there is a high level of Full Scans to Index Searches, then it is important to also check the following: See PERFMON SQLServer:Buffer Manager performance counters for memory pressure:

Page Life Expectancy

Checkpoint pages/sec

Lazy writes/sec A high number of scans can cause buffer pool pressure (as indicated with low PLE and a higher Lazy Writes / sec count). Memory pressure will quickly manifest into disk pressure, so also check: See PERFMON Physical Disk performance counters:

Disk sec/read

Disk sec/write Note: Identify disk bottlenecks by using Performance Counters, Profiler, sys.dm_io_virtual_file_stats and SHOWPLAN output. Also refer to the sys.dm_io_virtual_file_stats dynamic management view (DMV) to track io_stalls to help identify IO bottlenecks. To back up and support this information, compare the counters to sys.dm_os_wait_stats output. If you see high values in perfmon, you may also see high waits for the following:

ASYNC_IO_COMPLETION

IO_COMPLETION

PAGEIOLATCH_* (Data page I/O completion waits appear as PAGEIOLATCH_* waits) SQL Profiler can be used to identify which SQL statements are causing scans. Use the scans event class and events scan:started and scan:completed. Include the object ID data column. Save the profiler trace to a file and then convert it to trace table. You can then search for the scans event. The scan:completed event provides the associated IO so that you can also search for high reads, writes, and duration.

Reference:
SQL Server, Access Methods Object
http://msdn.microsoft.com/en-us/library/ms177426.aspx
SQL Server 2005 Waits and Queues
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
Wait Types and Correlation to Other Performance Info http://www.sqlmag.com/Files/09/40925/Webtable_01.doc

SQLServer:Buffer Manager Buffer cache hit ratio

Description: \SQLServer:Buffer Manager\Buffer cache hit ratio

Description: The Buffer Cache Hit Ratio measures the percentage of pages that were found in the buffer pool without having to incur a read from disk. This counter indicates how often SQL Server goes to the buffer, not the hard disk, to get data. The higher this ratio, the better. A high ratio, close to 100% indicates that SQL Server did not have to go to the hard disk often to fetch data, and performance overall is boosted. If the Buffer Cache Hit Ratio was 100% that would suggest that all of the pages are being accessed from cache and does not require trips to disk, because of the optimistic read ahead mechanism, this is not exactly the case. When a user session wants to read data from the database, it will read directly from the SQL Server buffer cache (a logical read), or, if the buffer cache does not have the data that is requested, the data will be read into the buffer cache from disk (a physical read) and then from the buffer cache. If the requested data is in the buffer cache, then it is called a 'buffer hit'. If the data is not in the buffer cache it is called a 'buffer miss'. The ratio of buffer hits to total buffer requests is called the buffer cache hit ratio as can be seen from the following:

Cache Hit Ratio = (Logical Reads - Physical Reads)/Logical Reads

A read from memory takes approximately 100 nanoseconds, while a read from disk takes about 8 milliseconds or more. 1 millisecond = 1,000,000 nanoseconds The important point about SQL Server read operations is that when selecting data from the database, the user will wait on the complete read operation including all of the physical reads. The time is takes to select from the database depends on how much data will be read and how long it takes for those reads to occur. Even with cache reads, the time it takes to read a large amount of data can be significant. With physical reads, the time will be even longer. There are a few considerations to be aware of regarding the Buffer Cache Hit Ratio counter. First, unlike many of the other counters available for monitoring SQL Server, this counter averages the Buffer Cache Hit Ratio from the time the instance of SQL Server was started. In other words, this counter is not a real-time measurement, but an average. Secondly, the buffer cache hit ratio may be skewed by the read ahead mechanism. Read Ahead Reads are pages that were read into cache while the query was processed. Because of the read ahead mechanism, you should not infer from a high buffer cache hit ratio that SQL Server is not suffering from memory pressure or at least could not benefit from additional memory.
Threshold:
Yellow: Less than 97 percent buffer cache hit ratio
Next Steps:
Run expensive queries through the Database Tuning Advisor (DTA), add additional memory, and look for queries with a high number of logical reads and consider tuning and potentially rewriting them.
Reference:
SQL Server, Access Methods Object
http://msdn.microsoft.com/en-us/library/ms177426.aspx

SQLServer:Buffer Manager Free pages

Description: \SQLServer:Buffer Manager\Free pages

Description: Total number of pages on all free lists. The more free pages that are available then the less often the lazy writer will have to fire keeping pages in the buffer pool longer.

Threshold:
Yellow: Less than 640 Free Pages

Next Steps: Compare the Buffer Manager\Free pages counter to the following:

Buffer Manager\Lazy Writes /sec

Buffer Manager\Page Life Expectancy

The higher the Buffer Manager\Free pages then the higher the Buffer Manager\Page Life Expectancy should be. If Buffer Manager\Free pages is low then the Buffer Manager\Lazy Writes /sec will be higher as the Lazy Writer will become active attempting to free the buffer cache as SQL Server will be under memory pressure.
Reference:
SQL Server, Access Methods Object
http://msdn.microsoft.com/en-us/library/ms177426.aspx

SQLServer:Buffer Manager Lazy writes/sec

Description: \SQLServer:Buffer Manager\Lazy writes/sec

Description: The Lazy Writes/sec counter records the number of buffers written each second by the buffer manager's lazy write process. This counter tracks how many times a second that the Lazy Writer process is moving dirty pages from the buffer to disk in order to free up buffer space. This process is where the dirty, aged buffers are removed from the buffer by a system process that frees the memory up for other uses. A dirty, aged buffer is one that has changes and needs to be written to the disk. High value on this counter possibly indicates I/O issues or even SQL Server memory problems. The Lazy writes / sec values should consistently be less than 20 for the average system. Generally speaking, this should not be a high value, say more than 20 per second or so. Ideally, it should be close to zero. If it is zero, this indicates that your SQL Server's buffer cache is plenty big and SQL Server doesn't have to free up dirty pages, instead waiting for this to occur during regular checkpoints. If this value is high, then a need for more memory is indicated.
Threshold:
Red: Greater than 20 Lazy Writes per second
Next Steps: Look for an increase in SQL Server: Buffer Manager: Checkpoint Pages/sec and SQL Server:Buffer Manager: Lazy Writes/sec performance object counters because SQL Server 2005 starts to flush pages out of the buffer pool cache under memory pressure.
Reference:
SQL Server, Access Methods Object
http://msdn.microsoft.com/en-us/library/ms177426.aspx

SQLServer:Buffer Manager Page life expectancy

Description: SQLServer_Buffer Manager Page Life Expectancy

Description: Number of seconds a page will stay in the buffer pool without references. This performance monitor counter tells you, on average, how long data pages are staying in the buffer. If this value gets below 300 seconds, this is a indication that SQL Server is doing too many logical reads putting pressure on the buffer pool or potentially that your SQL Server could use more memory in order to boost performance. Anything below 300 is a critical level. The Page life expectancy counter is considered one of the most critical counters for SQL Server. If Page life expectancy becomes low SQL Server will attempt physical reads from disk into the buffer pool to honor requests. Requests from physical disk will take considerably longer causing higher disk costs.
Threshold:
Red: Page life expectancy is less than 5 minutes (300 seconds)
Next Steps: If Buffer Manager\Page life expectancy is low then the Buffer Manager\Lazy Writes /sec will be higher as the Lazy Writer will become active attempting to free the buffer cache as SQL Server will be under memory pressure. Due to the disk impact of the physical reads incurred, the \Physical Disk \Avg. Disk sec/Read counter may also become a bottleneck as SQL Server is reading from disk instead of the buffer pull to honor requests. Look for an increase in SQL Server: Buffer Manager: Checkpoint Pages/sec and SQL Server:Buffer Manager: Lazy Writes/sec performance object counters because SQL Server 2005 / 2008 starts to flush pages out of the buffer pool cache under memory pressure. Run expensive queries through the Database Tuning Advisor (DTA), look for queries with a high number of logical reads and consider tuning and potentially rewriting them, and potentiall add additional memory if non-hardware options to not address the issue.
Reference:
SQL Server, Access Methods Object http://msdn.microsoft.com/en-us/library/ms177426.aspx

SQLServer:Buffer Manager Page lookups/sec

Description: SQLServer:Buffer Manager Page Lookups/sec
Description:
Number of requests to find a page in the buffer pool. When the ratio of page lookups to batch requests is greater than 100, this is an indication that while query plans are looking up data in the buffer pool, these plans are inefficient.
Threshold: Ratio of Page Lookups/sec to Batch Requests/sec < 100 to 1.
Warning: Page life expectancy is less than 5 minutes (300 seconds)
Next Steps: Page Lookups/sec is the number of requests to find a page in the buffer pool made per second. If this number is high as compared to the number of batch requests, this indicates a degree of inefficiency and a potential opportunity for tuning. Identify queries with the highest amount of logical I/O's and tune them.
Note: You can track the Page Lookups/sec and other counters through the sys.dm_os_performance_counters DMV which contains all the SQL Server instance object-related counters that you can find in perfmon.
Reference:
SQL Server, Buffer Manager Object http://msdn.microsoft.com/en-us/library/ms189628.aspx

SQLServer:Buffer Manager Page reads/sec

Description: SQLServer_Buffer Manager Page Reads_sec

Description: Number of physical database page reads issued per second. Number of physical database page reads issued. 80 – 90 per second is normal, anything that is above indicates indexing or memory constraint.
Threshold:
Informational: Page Reads/sec > 90
Next Steps: Attempt to tune the application so that fewer I/O operations are required. For example, perhaps I/O would be reduced if there were appropriate indexes or if the database design were denormalized. If the applications cannot be tuned, you will need to acquire disk devices with more capacity. Compare to the Memory: Pages/sec counter to see if there is paging while the SQL Server:Buffer Manager\Page reads/sec is high. Note: Before adjusting the fill factor, at a database level compare the SQL Server:Buffer Manager\Page reads/sec counter to the SQL Server:Buffer Manager\Page writes/sec counter, and use the fill factor option only if writes are a substantial fraction of reads (greater than 30 percent).
Reference:
SQL Server, Buffer Manager Object
http://msdn.microsoft.com/en-us/library/ms189628.aspx

SQLServer:Buffer Manager Page writes/sec

Description: SQLServer_Buffer Manager Page Writes_sec

Description: Number of physical database page writes issued per second. 80 – 90 per second is normal, anything above, check the lazy writer/sec and Checkpoint pages/sec counter, if these counters are relatively high then, this indicates a memory constraint.
Threshold:
Informational: Page Writes/sec > 90
Next Steps: Attempt to tune the application so that fewer I/O operations are required. For example, perhaps I/O would be reduced if there were appropriate indexes or if the database design were denormalized. If the applications cannot be tuned, you will need to acquire disk devices with more capacity. Compare to the Memory: Pages/sec counter to see if there is paging while the SQL Server:Buffer Manager\Page reads/sec is high. Note: Before adjusting the fill factor, at a database level compare the SQL Server:Buffer Manager\Page reads/sec counter to the SQL Server:Buffer Manager\Page writes/sec counter, and use the fill factor option only if writes are a substantial fraction of reads (greater than 30 percent).
Reference:
SQL Server, Buffer Manager Object
http://msdn.microsoft.com/en-us/library/ms189628.aspx

SQLServer:General Statistics Logins/sec

Description: Total number of logins started per second. Greater than 2 per second may indicate that applications are not correctly using connection pooling.

SQLServer:General Statistics Logouts/sec

Description: Total number of logouts started per second. Greater than 2 per second indicates that the application is not correctly using connection pooling.

SQLServer:General Statistics User Connections

Description: Number of users connected to the system. The number of users currently connected to the SQL Server.

SQLServer:Latches Latch Waits/sec

Description: Number of latch requests that could not be granted immediately and had to wait before being granted. In other words, these are the amount of latches, in a one second period that had to wait.

SQLServer:Latches\Total Latch Wait Time (ms)

Description: Total latch wait time (milliseconds) for latch requests that had to wait in the last second.

SQLServer:Locks Lock Requests/sec

Description: Number of new locks and lock conversions requested from the lock manager.

SQLServer:Locks Lock Waits/sec

Description: Number of lock requests that could not be satisfied immediately and required the caller to wait before being granted the lock.

SQLServer:Locks Lock Wait Time (ms)

Description: Total wait time (milliseconds) for locks in the last second.

SQLServer:Locks Lock Timeouts/sec

Description: Number of lock requests that timed out. This does not include requests for NOWAIT locks.

SQLServer:Locks Number of Deadlocks/sec

Description: Number of lock requests that resulted in a deadlock.

SQLServer:Memory Manager Memory Grants Pending

Description: Current number of processes waiting for a workspace memory grant. Memory Grants Pending records the number of connections that are waiting for memory before they can begin processing a memory intensive query such as a sort or hash operation. Connections that wait in this state for a long enough time will eventually get an 8645 error (A time out occurred while waiting for memory resources to execute the query. Rerun the query). A spid waiting in this state will have a waittype of 0x0040 (RESOURCE_SEMAPHORE) in sysprocesses. If this counter remains above zero for any significant amount of time then you will need to track down what queries are doing sorts/hashes and run them through Index Tuning Wizard to see if they can get a more efficient plan.

SQLServer:SQL Statistics Batch Requests/sec

Description: Number of SQL batch requests received by server. This counter measures the number of batch requests that SQL Server receives per second, and generally follows in step to how busy your server's CPUs are. Generally speaking, over 1000 batch requests per second indicates a very busy SQL Server, and could mean that if you are not already experiencing a CPU bottleneck, that you may very well soon. Of course, this is a relative number, and the bigger your hardware, the more batch requests per second SQL Server can handle. From a network bottleneck approach, a typical 100Mbs network card is only able to handle about 3000 batch requests per second. If you have a server that is this busy, you may need to have two or more network cards, or go to a 1Gbs network card. Note: Sometimes low batch requests/sec can be misleading. If there were a SQL statements/sec counter, this would be a more accurate measure of the amount of SQL Server activity. For example, an application may call only a few stored procedures yet each stored procedure does lot of work. In that case, we will see a low number for batch requests/sec but each stored procedure (one batch) will execute many SQL statements that drive CPU and other resources. As a result, many counter thresholds based on the number of batch requests/sec will seem to identify issues because the batch requests on such a server are unusually low for the level of activity on the server. We cannot conclude that a SQL Server is not active simply by looking at only batch requests/sec. Rather, you have to do more investigation before deciding there is no load on the server. If the average number of batch requests/sec is below 5 and other counters (such as SQL Server processor utilization) confirm the absence of significant activity, then there is not enough of a load to make any recommendations or identify issues regarding scalability.

SQLServer:SQL Statistics SQL Compilations/sec

Description: Number of SQL compilations.

SQLServer:SQL Statistics SQL Re-Compilations/sec

Description: Number of SQL re-compiles. This needs to be 0 as much as possible. A recompile can cause deadlocks and compile locks that are not compatible with any locking type.

System Processor Queue Length

Description: Processor Queue Length is the number of threads in the processor queue. Unlike the disk counters, this counter shows ready threads only, not threads that are running. There is a single queue for processor time even on computers with multiple processors. Therefore, if a computer has multiple processors, you need to divide this value by the number of processors servicing the workload. A sustained processor queue of less than 10 threads per processor is normally acceptable, dependent of the workload.

This analysis determines if the average processor queue length exceeds the number of processors. If so, then this could indicate a processor bottleneck. Use this analysis in correlation with Privileged Mode CPU Analysis and Excessive Processor Use by Process analysis.

Note: Due to the way in which this counter is collected, ignore this counter and alerts for it when collected from a virtual computer.

If there are more tasks ready to run than there are processors, threads queue up. The processor queue is the collection of threads that are ready but not able to be executed by the processor because another active thread is currently executing. A sustained or recurring queue of more threads than number of processors is a good indication of a processor bottleneck.

You can use this counter in conjunction with the \Processor\% Processor Time counter to determine if your application can benefit from more CPUs.

Reference:

• Measuring .NET Application Performance

System System Calls/sec

Description: System Calls/sec is the combined rate of calls to operating system service routines by all processes running on the computer. These routines perform all of the basic scheduling and synchronization of activities on the computer, and provide access to non-graphic devices, memory management, and name space management. This counter displays the difference between the values observed in the last two samples, divided by the duration of the sample interval.