MS SQL Monitoring

Overview

This page provides the capability for profiling MS SQL. Cavisson machine agent establishes a connection with MS SQL database server. After establishing the connection with database, monitor executes some queries to capture the required stats to get the health status of MS SQL. To make connection with MS SQL, user needs to provide connectivity credential to the monitor program, so that it can make connection to the database.

MS SQL DB Monitor Pre-Requisites

  1. Communication from machine on which the agent is installed to DB Server.
  2. Supported MSSQL Versions: 2005, 2008, 2012, 2014, and 2016.

MS SQL Server Authentication

  • SQL Server Authentication: If machine agent is installed in Linux platform then SQL Server Authentication should be enabled for provided user credentials.
  • Windows Authentication: If machine agent is installed in Windows platform then SQL Server Authentication or Window Authentication will work for monitoring.

Metric Description

There are system related metrics and application related metrics supported by Cavisson. All system related metrics will also be available for MS SQL server. All System, Process, and Network related metrics are standard for Windows environment. For details, please refer Cavisson Product Suite – Datasheet. Here, we are describing only MS SQL server metrics.
Following are some key metrics that will capture the health status of MS SQL Server.

  • MSSQL Server Stats
  • MSSQL IO Stats
  • MSSQL Performance Stats

MSSQL Server Stats

MetricTypeDescription
MSSQL Active Cursors sample Number of active cursors.
MSSQL Active Temp Tables sample Number of temporary tables.
MSSQL Active Transactions sample Number of active transactions.
MSSQL AU Cleanup Batches/Sec rate The number of batches per second that were completed successfully by the background task that cleans up deferred dropped allocation units.
MSSQL AU Cleanups/Sec rate The number of allocation units per second that were successfully dropped the background task that cleans up deferred dropped allocation units. Each allocation unit drop requires multiple batches.
MSSQL Auto-Param Attempts/Sec rate Number of auto param attempts per second.
MSSQL Latch Wait Time (ms)/Sec rate Latch wait time (in milliseconds) per second for latch requests that had to wait.
MSSQL Wait Time (ms)/Sec rate Time (in millisecond) for the selected type of wait per second.
MSSQL Backup and Restore Throughput/Sec rate Read/write throughput for backup and restore operations of a database per second.
MSSQL Batch Requests/Sec rate Number of Transact-SQL command batches received per second.
MSSQL Buffer Cache Hit Ratio (Pct) sample Indicates the percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses.
MSSQL Bulk Copy Rows/Sec rate Number of bulk copy rows per second.
MSSQL Bulk Copy Throughput (KB)/Sec rate Amount of data bulk copied (in kilobytes) per second.
MSSQL By-Reference Lob Create/Sec rate Number of large object (lob) values per second that were passed by reference. By-reference lobs are used in certain bulk operations to avoid the cost of passing them by value.
MSSQL By-Reference Lob Used/Sec rate Number of by-reference lob values per second that were used. By-reference lobs are used in certain bulk operations to avoid the cost of passing them by-value.
MSSQL Cache Hit Ratio sample Ratio between cache hits and lookups.
MSSQL Cache Objects sample Number of cache objects in the cache.
MSSQL Cache Objects In Use sample Number of cache object in use.
MSSQL Cache Pages sample Number of 8-kilobyte (KB) pages used by cache objects.
MSSQL Cached Cursors sample Number of cursors of a given type in the cache.
MSSQL Checkpoint Pages/Sec rate Number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.
MSSQL CLR Execution Time (mus) sample Total execution time in CLR (microseconds).
MSSQL Connection Memory (KB) sample Connection memory in kilobytes.
MSSQL Lob Readahead/Sec rate Number of lob pages per second on which readahead was issued.
MSSQL Pull In Row/Sec rate Number of column values per second that were pulled in-row from off-row.
MSSQL Push Off Row/Sec rate Number of column values per second that were pushed from in-row to off-row.
MSSQL Cursor Memory Usage (KB) sample Amount of memory consumed by cursors in kilobytes (KB).
MSSQL Cursor Requests/Sec rate Number of SQL cursor requests received by server per second.
MSSQL Cursor Worktable Usage sample Number of worktables used by cursors.
MSSQL Data File Size (KB) cumulative Cumulative size (in kilobytes) of all the data files in the database.
MSSQL Database Pages sample Number of database pages.
MSSQL DBCC Logical Scan Bytes/Sec rate Number of logical read scan bytes per second for database console commands (DBCC).
MSSQL Deferred Dropped Rowsets/Sec rate Number of rowsets created per second as a result of aborted online index build operations that are waiting to be dropped by the background task that cleans up deferred dropped rowsets.
MSSQL Dropped Rowset Cleanups/Sec rate Number of rowsets per second created as a result of aborted online index build operations that were successfully dropped by the background task that cleans up deferred dropped rowsets.
MSSQL Dropped Rowset Skipped/Sec rate Number of rowsets per second created as a result of aborted online index build operations that were skipped by the background task that cleans up deferred dropped rowsets created.
MSSQL Errors/Sec rate Number of errors per second.
MSSQL Event Notifications Delayed Drop sample Number of event notifications waiting to be dropped by a system thread.
MSSQL Extent Deallocations/Sec rate Number of extents deallocated per second in all databases in this instance of SQL Server.
MSSQL Extent Allocated/Sec rate Number of extents allocated per second in all databases in this instance of SQL Server.
MSSQL Failed AU Cleanup Batches/Sec rate The number of batches per second that failed and required retry, by the background task that cleans up deferred dropped allocation units. Failure could be due to lack of memory or disk space, hardware failure and other reasons.
MSSQL Failed Auto-Params/Sec rate Number of failed auto params per second.
MSSQL Failed Leaf Page Cookie/Sec rate Number of times per second that a leaf page cookie could not be used during an index search since changes happened on the leaf page. The cookie is used to speed up index search.
MSSQL Failed Tree Page Cookie/Sec rate Number of times per second that a tree page cookie could not be used during an index search since changes happened on the parent pages of those tree pages. The cookie is used to speed up index search.
MSSQL Forced Parameterizations/Sec rate Number of forced parameterizations per second.
MSSQL Forwarded Records/Sec rate Number of records per second fetched through forwarded record pointers.
MSSQL Free List Stalls/Sec rate Number of free list stalls per second.
MSSQL Free Space In Tempdb (KB) sample The amount of space (in kilobytes) available in tempdb. There must be enough free space to hold both the snapshot isolation level version store and all new temporary objects created in this instance of the Database Engine.
MSSQL FreeSpace Page Fetches/Sec rate Number of pages fetched per second by free space scans. These scans search for free space within pages already allocated to an allocation unit, to satisfy requests to insert or modify record fragments.
MSSQL FreeSpace Scans/Sec rate 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 fragment. Each scan may find multiple pages.
MSSQL Full Scans/Sec rate Number of unrestricted full scans per second. These can be either base-table or full-index scans.
MSSQL Granted Workspace Memory (KB) sample Total amount of memory currently granted to executing processes, such as hash, sort, bulk copy, and index creation operations in kilobytes.
MSSQL HTTP Authenticated Requests/Sec rate Number of http authenticated requests per second.
MSSQL Index Searches/Sec rate Number of index searches per second. These are used to start a range scan, reposition a range scan, revalidate a scan point, fetch a single index record, and search down the index to locate where to insert a new row.
MSSQL Latch Waits/Sec rate Number of latch requests per second that could not be granted immediately.
MSSQL Lazy Writes/Sec rate Number of buffers written per second by the buffer manager’s lazy writer. The lazy writer is a system process that flushes out batches of dirty, aged buffers (buffers that contain changes that must be written back to disk before the buffer can be reused for a different page) and makes them available to user processes. The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers.
MSSQL LobHandle Created/Sec rate Number of temporary lobs handle created per second.
MSSQL LobHandle Destroyed/Sec rate Number of temporary lobs handle destroyed per second.
MSSQL LobSS Provider Create/Sec rate Number of lob Storage Service Providers (LobSSP) created per second. One worktable created per LobSSP.
MSSQL LobSS Provider Destroy/Sec rate Number of lob SS provider destroyed per second.
MSSQL LobSS Provider Truncation/Sec rate Number of lob ss provider truncates per second.
MSSQL Lock Blocks sample Current number of lock blocks in use on the server (refreshed periodically). A lock block represents an individual locked resource, such as a table, page, or row.
MSSQL Lock Blocks Allocated sample Current number of allocated lock blocks. At server startup, the number of allocated lock blocks plus the number of allocated lock owner blocks depends on the SQL Server Locks configuration option. If more lock blocks are needed, the value increases.
MSSQL Lock Memory (KB) sample Total amount of dynamic memory the server is using for locks.
MSSQL Lock Owner Blocks sample Number of lock owner blocks currently in use on the server (refreshed periodically). A lock owner block represents the ownership of a lock on an object by an individual thread. Therefore, if three threads each have a shared (S) lock on a page, there will be three lock owner blocks.
MSSQL Lock Owner Blocks Allocated sample Current number of allocated lock owner blocks. At server startup, the number of allocated lock owner blocks and the number of allocated lock blocks depend on the SQL Server Locks configuration option. If more lock owner blocks are needed, the value increases dynamically.
MSSQL Lock Requests/Sec rate Number of lock requests per second.
MSSQL Lock Timeouts (Without NOWAIT)/Sec rate Number of lock requests per second that timed out, but excluding requests for NOWAIT locks.
MSSQL Lock Timeouts/Sec rate Number of lock requests per second that timed out, including requests for NOWAIT locks.
MSSQL Lock Wait Time (ms) sample Total wait time (in milliseconds) for locks in the last second.
MSSQL Lock Waits/Sec rate Number of lock requests per second that required the caller to wait.
MSSQL Log Bytes Flushed/Sec rate Number of log bytes flushed per second.
MSSQL Log Cache Hit Ratio (Pct) sample Percentage of log cache reads satisfied from the log cache.
MSSQL Log Cache Reads/Sec rate Number of log cache reads per second.
MSSQL Log File Size (KB) cumulative Cumulative size (in kilobytes) of all the transaction log files in the database.
MSSQL Log File Used Size (KB) cumulative Cumulative used size of all the log files in the database.
MSSQL Log Flush Wait Time (ms)/Sec rate Log flush wait time (in milliseconds) per second to flush the log. On an Always On secondary database, this value indicates the wait time for log records to be hardened to disk.
MSSQL Log Flush Waits/Sec rate Number of commits per second waiting for the log flush.
MSSQL Log Flushes/Sec rate Number of log flushes per second.
MSSQL Log Growths/Sec rate Number of times per second the transaction log for the database has been expanded.
MSSQL Log Shrinks/Sec rate Number of times per second log shrinks for this database.
MSSQL Log Truncations/Sec rate Number of times per second the transaction log has been shrunk.
MSSQL Logical Connections sample Number of logical connections to the system.
MSSQL Logins/Sec rate Total number of logins started per second. This does not include pooled connections.
MSSQL Logouts/Sec rate Total number of logout operations started per second.
MSSQL Longest Transaction Running Time (ms) sample Transaction longest running time in milliseconds.
MSSQL Mars Deadlocks/Sec rate Number of MARS deadlocks per second.
MSSQL Maximum Workspace Memory (KB) sample Maximum amount of memory available for executing processes, such as hash, sort, bulk copy, and index creation operations in kilobytes.
MSSQL Memory Grants Outstanding/Sec rate Total number of processes per second that have successfully acquired a workspace memory grant.
MSSQL Memory Grants Pending sample Total number of processes waiting for a workspace memory grant.
MSSQL Mixed Page Allocations/Sec rate Number of pages allocated per second from mixed extents. These could be used for storing the IAM pages and the first eight pages that are allocated to an allocation unit.
MSSQL Non-Atomic Yields/Sec rate Number of non-atomic yields per second.
MSSQL NonSnapshot Version Transactions sample Number of currently active transactions that are not using snapshot isolation level and have made data modifications that have generated row versions in the tempdb version store.
MSSQL Active Cursor Plans sample Number of active cursor plans.
MSSQL Deadlocks/Sec rate Number of lock requests per second that resulted in a deadlock.
MSSQL Current SuperLatches sample Number of latches that are currently SuperLatches.
MSSQL Optimizer Memory (KB) sample Total amount of dynamic memory the server is using for query optimization.
MSSQL Page Compression Attempts/Sec rate Number of pages evaluated for page-level compression. Includes pages that were not compressed because significant savings could be achieved. Includes all objects in the instance of SQL Server.
MSSQL Page Deallocations/Sec rate Number of pages deallocated per second in all databases in this instance of SQL Server. These include pages from mixed extents and uniform extents.
MSSQL Page Life Expectancy (Sec) sample Number of seconds a page will stay in the buffer pool without references.
MSSQL Page Lookups/Sec rate Number of requests per second to find a page in the buffer pool.
MSSQL Page Reads/Sec rate Number of physical database page reads that are issued per second.
MSSQL Page Splits/Sec rate Number of page splits per second that occur as the result of overflowing index pages.
MSSQL Page Writes/Sec rate Number of physical database page writes that are issued per second.
MSSQL Pages Allocated/Sec rate Number of pages allocated per second in all databases in this instance of SQL Server. These include pages allocations from both mixed extents and uniform extents.
MSSQL Pages Compressed/Sec rate Number of data pages that are compressed by using PAGE compression. Includes all objects in the instance of SQL Server.
MSSQL Log Used (Pct) sample Percentage of space in the log that is in use.
MSSQL Probe Scans/Sec rate Number of probe scans per second that are used to find at most one single qualified row in an index or base table directly.
MSSQL Processes Blocked sample Number of currently blocked processes.
MSSQL Range Scans/Sec rate Number of qualified range scans through indexes per second.
MSSQL Readahead Pages/Sec rate Number of read ahead pages per second.
MSSQL Replication Transactions/Sec rate Number of transactions per second read out of the transaction log of the publication database and delivered to the distribution database.
MSSQL Safe Auto-Params/Sec rate Number of safe auto params per second.
MSSQL Scan Point Revalidations/Sec rate Number of times per second that the scan point had to be revalidated to continue the scan.
MSSQL Skipped Ghosted Records/Sec rate Number of ghosted records per second skipped during scans.
MSSQL Snapshot Transactions sample Number of currently active transactions using the snapshot isolation level.
MSSQL SOAP Empty Requests/Sec rate Number of empty SOAP requests started per second.
MSSQL SOAP Method Invocations/Sec rate Number of SOAP method invocations per second.
MSSQL SOAP Session Initiate Requests/Sec rate Number of SOAP session initiate requests per second.
MSSQL SOAP Session Terminate Requests/Sec rate Number of SOAP session terminate requests per second.
MSSQL SOAP SQL Requests/Sec rate Number of SOAP SQL requests started per second.
MSSQL SOAP WSDL Requests/Sec rate Number of SOAP Web Service Description Language requests started per second.
MSSQL SQL Attentions/Sec sample Number of attentions per second. An attention is a request by the client to end the currently running request
MSSQL SQL Cache Memory (KB) sample Total amount of dynamic memory the server is using for the dynamic SQL cache.
MSSQL SQL Compilations/Sec rate Number of SQL compilations per second. Indicates the number of times the compile code path is entered. Includes compiles caused by statement-level recompilations in SQL Server. After SQL Server user activity is stable, this value reaches a steady state.
MSSQL SQL Re-Compilations/Sec rate Number of statement recompiles per second.
MSSQL Stored Procedures Invoked/Sec rate Number of stored procedures invoked per second.
MSSQL SuperLatch Demotions/Sec rate Number of SuperLatches per second that have been demoted to regular latches in the last second.
MSSQL SuperLatch Promotions/Sec rate Number of latches per second that have been promoted to SuperLatches in the last second.
MSSQL Table Lock Escalations/Sec rate Number of times locks on a table were escalated to the TABLE or HoBT granularity.
MSSQL Target Pages sample Ideal number of pages in the buffer pool.
MSSQL Target Server Memory (KB) sample Ideal amount of memory the server can consume in kilobytes.
MSSQL Task Limit Reached sample Total number of times that a queue monitor would have started a new task, but did not because the maximum number of tasks for the queue is already running.
MSSQL Task Limit Reached/Sec rate Number of times per second that a queue monitor would have started a new task, but did not because the maximum number of tasks for the queue is already running.
MSSQL Tasks Aborted/Sec rate Number of activation stored procedure tasks per second that end with an error, or are aborted by a queue monitor for failing to receive messages.
MSSQL Tasks Running sample Number of activation stored procedures that are currently running.
MSSQL Tasks Started/Sec rate Number of activation stored procedures started per second by all queue monitors in the instance.
MSSQL Temp Tables Created/Sec rate Number of temporary tables/table variables created per second.
MSSQL Temp Tables For Destruction sample Number of temporary tables/table variables waiting to be destroyed by the cleanup system thread.
MSSQL Total Latch Wait Time (ms) sample Total latch wait time in milliseconds.
MSSQL Total Server Memory (KB) sample Amount of memory the server has committed using the memory manager in kilobytes.
MSSQL Total Transactions sample Total number of transactions.
MSSQL Transactions/Sec rate Number of transaction per second.
MSSQL Unsafe Auto-Params/Sec rate Number of unsafe auto-params per second.
MSSQL Update Conflict Ratio (Pct) sample Percentage of those transactions using the snapshot isolation level that have encountered update conflicts within the last second. An update conflict occurs when a snapshot isolation level transaction attempts to modify a row that last was modified by another transaction that was not committed when the snapshot isolation level transaction started.
MSSQL Update Snapshot Transactions sample Number of currently active transactions using the snapshot isolation level and have modified data.
MSSQL Used Leaf Page Cookie/Sec rate Number of times per second a leaf page cookie is used successfully during an index search since no change happened on the leaf page. The cookie is used to speed up index search.
MSSQL Used Tree Page Cookie/Sec rate Number of times per second a tree page cookie is used successfully during an index search since no change happened on the parent page of the tree page. The cookie is used to speed up index search.
MSSQL User Connections sample Number of user connections.
MSSQL Version Cleanup Rate (KB/Sec) rate The rate (in kilobytes per second) at which row versions are removed from the snapshot isolation version store in tempdb.
MSSQL Version Store Size (KB) sample The amount of space (in kilobytes) in tempdb being used to store snapshot isolation level row versions.
MSSQL Version Generation Rate (KB/Sec) rate The rate (in kilobytes per second) at which new row versions are added to the snapshot isolation version store in tempdb.
MSSQL Version Store Units sample The number of active allocation units in the snapshot isolation version store in tempdb.
MSSQL Version Store Unit Creation/Sec rate Number of allocation units that have been created per second in the snapshot isolation store since the instance of the Database Engine was started.
MSSQL Version Store Unit Truncation/Sec rate Number of allocation units that have been removed per second from the snapshot isolation store since the instance of the Database Engine was started.
MSSQL Workfiles Created/Sec rate Number of work files created per second. For example, work files could be used to store temporary results for hash joins and hash aggregates.
MSSQL Worktables Created/Sec rate Number of work tables created per second. For example, work tables could be used to store temporary results for query spool, lob variables, XML variables, and cursors.
MSSQL Worktables From Cache Ratio sample Percentage of work tables created where the initial two pages of the work table were not allocated but were immediately available from the work table cache. (When a work table is dropped, two pages may remain allocated and they are returned to the work table cache. This increases performance.)
MSSQL Write Transactions/Sec rate Number of write transactions per second.

MSSQL IO Stats

IO stats works based on file id and data id.

MetricTypeDescription
MSSQL Data File Avg Read Size (KB)sampleData file average read size in Kilobytes.
MSSQL Data File Avg Write Size (KB)sampleData file average write size in Kilobytes.
MSSQL Data File Bytes Read/SecrateData file bytes read per second.
MSSQL Data File Bytes Write/SecrateData file bytes write per second.
MSSQL Data File IO StallssampleData file IO stalls.
MSSQL Data File Read/SecrateNumber of read per second from data file.
MSSQL Data File Write/SecrateNumber of write per second into data file.
MSSQL Log File Avg Read Size (KB)sampleLog file average read size in Kilobytes.
MSSQL Log File Avg Write Size (KB)sampleLog file average write size in Kilobytes.
MSSQL Log File Bytes Read/SecrateLog file bytes read per second.
MSSQL Log File Bytes Write/SecrateLog file bytes write per second.
MSSQL Log File IO StallssampleLog file Io stalls.
MSSQL Log File Read/SecrateNumber of read per second from log file.
MSSQL Log File Write/SecrateNumber of write per second into log file.

MSSQL Performance Stats

MetricTypeDescription
MSSQL Query/MinsampleNumber of query executing per minute.
MSSQL Avg Query CPU Time (ms)sampleAverage query CPU time in milliseconds.
MSSQL Avg Query Elapsed Time (ms)sampleAverage query elapsed time in milliseconds.