SQL Server R2 Performance counter detailed list (i)

Original: SQL Server R2 Performance counter detailed list (i)

SQL Server Backup Device counters:

Can monitor the Microsoft SQL Server backup device for backup and restore operations

SQL Server Backup Device counters	Description
Device Throughput Bytes/sec	The throughput (in bytes per second) of the read and write operations used by a backup device to back up or restore the database. This counter exists only when a backup or restore operation is performed.

SQL Server Access Methods counters:Counters for monitoring how to access logical data in a database

SQL Server Access Methods counters	Description
AU Cleanup batches/sec	The number of batches successfully completed per second for background tasks that are responsible for clearing deferred deletion of allocation units.
AU cleanups/sec	The number of allocation units successfully deleted per second by the background task responsible for clearing the deferred deletion allocation unit. Deleting each allocation unit requires multiple batches.
By-reference Lob Create Count	The Count of large object (LOB) values that are passed by reference. LOBs passed by reference will be used in some bulk operations to avoid the overhead required to pass these objects by value.
By-reference Lob Use Count	The count of LOB values passed by reference that were used. LOBs passed by reference will be used in some bulk operations to avoid the overhead required to pass these objects by value.
Count Lob Readahead	Executes the count of the LOB pages that are read ahead of time.
Count Pull in Row	The Count of column values in the row that have been requested from outside the row. For more information about row overflow, see row overflow data exceeds 8 KB.
Count Push Off Row	The Count of column values that have been pushed from within the row to the outside of the row. For more information about row overflow, see row overflow data exceeds 8 KB.
Deferred Dropped Aus	Waits for the number of allocation units deleted by the background task that is responsible for clearing deferred deletion of the allocation unit.
Deferred Dropped Rowsets	Waits for the number of rowsets created by the online index build operation to be deleted by the background task that cleans up deferred deletion of the rowset.
DROPPED Rowset Cleanups/sec	The number of rowsets that were created by a background task that cleans up deferred deleted rowset per second successfully deleted due to the abort of the online index build operation.
Dropped Rowsets Skipped/sec	The number of rowsets that the background task that is responsible for clearing deferred deleted rowsets skips per second, as a result of the abort of the online index build operation.
Extent deallocations/sec	The number of extents freed per second in all databases in this instance of SQL Server.
Extents Allocated/sec	The number of extents allocated per second in all databases in this instance of SQL Server.
Failed AU Cleanup Batches/sec	The number of batches that failed per second and need to be retried by the background task responsible for clearing deferred deletion of the allocation unit. The failure may be due to insufficient memory or disk space, hardware failure, and other reasons.
Failed Leaf Page Cookie	The number of times a leaf-page cookie could not be used during an index search since a change occurred on the leaf-level page. Cookies are used to speed up index searches.
Failed Tree Page Cookie	The number of times that a tree page cookie could not be used during an index search since a change occurred on the parent page of the tree page. Cookies are used to speed up index searches.
Forwarded records/sec	The number of records fetched per second by a forward record pointer.
FreeSpace Page fetches/sec	Number of pages extracted per second scanned by free space. These scans search for free space within a page that has been assigned to an allocation unit to satisfy requests to insert or modify a record fragment.
FreeSpace scans/sec	The number of scans per second that are initiated to search for free space within a page that has been assigned to an allocation unit to insert or modify a record fragment. Multiple pages may be found per scan.
Full scans/sec	The number of full scans that are unrestricted per second. These scans can be either a base table scan or a full-text index scan.
Index searches/sec	Number of index searches per second. Index search is used to initiate a range scan, relocate a range scan, re-verify a scan point, extract a single index record, and search down the index to determine where the new row will be inserted.
Lobhandle Create Count	The temporary LOB count that was created.
Lobhandle Destroy Count	The temporary LOB count that was destroyed.
LOBSS Provider Create Count	The LOB Storage Service provider (LOBSSP) count that has been created. Create a worksheet for each LOBSSP.
LOBSS Provider Destroy Count	The LOBSSP count that has been destroyed.
LOBSS Provider truncation Count	The LOBSSP count that has been truncated.
Mixed page Allocations/sec	Number of pages allocated per second from the mixed area. These pages can be used to store IAM pages and the first eight pages assigned to an allocation unit.
Page Compression attempts/sec	The number of pages that are computed for page-level compression. Because space can be greatly reduced, uncompressed pages are included. Includes all objects in an instance of SQL Server. For information about specific objects, see sys.dm_db_index_operational_stats (Transact-SQL).
Page deallocations/sec	Number of pages freed per second in all databases in this instance of SQL Server. This includes pages from the mixed zone and the unified zone.
Page splits/sec	Page split scores per second due to overflow of index pages.
Pages allocated/sec	Number of pages allocated per second in all databases in this instance of SQL Server. These pages include pages that are assigned from the mixed zone and the unified zone.
Pages compressed/sec	Use page compression to compress the number of pages of data. Includes all objects in an instance of SQL Server. For information about specific objects, see sys.dm_db_index_operational_stats (Transact-SQL).
Probe scans/sec	The number of probe scans per second that are used to find up to one qualifying row directly in the index or base table.
Range scans/sec	The number of scans per second that are scoped by the index.
Scan Point Revalidations/sec	The number of times per second that the scan point must be re-verified to continue scanning.
Skipped ghosted records/sec	Number of ghost Records skipped per second during the scan.
Table Lock escalations/sec	The number of times the lock on the table was upgraded to table or HoBT granularity.
Used leaf page Cookie	The number of times the leaf-level page cookie was successfully used during an index search, since no changes have occurred on the leaf-level page. Cookies are used to speed up index searches.
Used Tree Page Cookie	The number of times the tree page cookie was successfully used during an index search since no change has occurred on the parent page of the tree page. Cookies are used to speed up index searches.
Workfiles created/sec	The number of working files created per second. For example, a working file can be used to store temporary results for hash joins and hash aggregations.
Worktables created/sec	The number of worksheets created per second. For example, worksheets can be used to store temporary results for query spooling, LOB variables, XML variables, and cursors.
Worktables from Cache Ratio	The percentage of created worksheets that are not allocated on the first two pages but can be used directly from the worksheet cache. (When the worksheet is deleted, the two pages may remain allocated and returned to the worksheet cache.) This will improve performance. ）

SQL Server Buffer Manager object:

To monitor how SQL Server uses:

• Memory stores data pages, internal data structures, and process caches.
  
  
• Counters monitor the physical I/O when SQL Server reads and writes to a database page.
  
  

SQL Server Buffer Manager counters	Description
AWE Lookup Maps/sec	The number of times per second server requests, lookup and mapping of database pages in the buffer pool. After the database page is mapped, it becomes part of the server virtual address space.
AWE stolen Maps/sec	The number of times per second that buffers are fetched and mapped from the available list.
AWE unmap calls/sec	The number of times per second that the buffer was called to unmap. After the buffer is unmapped, it is excluded from the virtual server address space. You can unmap one or more buffers each time you call.
AWE unmap pages/sec	Number of SQL Server buffers to Unmap per second.
AWE Write Maps/sec	The number of times per second that must be mapped to a dirty buffer before it can be written to disk.
Buffer Cache hit Ratio	The percentage of pages found in the buffer cache that do not need to be read from disk. The ratio is the total number of cache hits compared to the total cache lookups since the last thousands of pages of access. After a long time, the ratio changes very little. Because it is much less expensive to read data from the cache than to read from disk, it is generally desirable that the ratio be higher. In general, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server.
Checkpoint pages/sec	The number of pages per second flushed to disk by checkpoints or other operations that require all dirty pages to be refreshed.
Database pages	Number of pages in the buffer pool that have database content.
Free list Stalls/sec	The number of requests per second that must wait for the available pages.
Free pages	The total number of pages for all available lists.
Lazy writes/sec	The number of buffers written per second by the buffer manager's lazy writer. The lazy writer is a system process used to batch flush dirty aging buffers that contain changed buffers, which must be rewritten back to disk to reuse buffers for other pages, and make them available to user processes. Lazy writers do not need to perform frequent checkpoints to create free buffers.
Page Life expectancy	The number of seconds that the page will stay in the buffer pool if it is not referenced.
Page lookups/sec	The number of requests per second required to find pages in the buffer pool.
Page reads/sec	The number of physical database page reads issued per second. This statistic shows the total number of physical page reads between all databases. Because of the overhead of physical I/O, you can minimize overhead by using methods such as larger data caches, smart indexes, more efficient queries, or changing database designs.
Page writes/sec	The number of physical database page writes performed per second.
Readahead pages/sec	Number of pages read per second for expected use.
Reserved Pages	Number of pages reserved by the buffer pool.
Stolen pages	The number of pages used for other server uses, including the process cache.
Target Pages	The ideal number of pages in the buffer pool.
Total Pages	Number of pages in the buffer pool (including database pages, available pages, and stolen pages).

SQL Server R2 Performance counter detailed list (i)