SQL Server R2 Performance counter detailed list (i)

Source: Internet
Author: User

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.