MS SQL Monitoring

Overview

Cavisson enables you to keep an eye on the performance of your database servers. Using the MS SQL Monitoring tool, you can optimize the performance of your applications and ensure the availability of your SQL servers. It provides an end-to-end monitoring of MS SQL queries with insights into various aspects, such as SQL Activity, Waits Statistics, Databases, and Support Services.

Key Features

  • Increased database performance
  • Quick detection of database outages, failures, and table corruption
  • Provides Query Execution Statistics and Query Plan
  • Prognostic analysis of storage requirements and index performance
  • Provides the status of MS SQL services
  • Ability to view data file and log file growth for databases.

MS SQL DB Monitor Pre-Requisites

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

MS SQL Server Authentication

To establish a connection with the MS SQL, you need to provide the connectivity credentials to the monitor program. After establishing a connection with your MS SQL database, the Cavisson machine agent executes a few queries to capture the required statistics for verifying the health status of your MS SQL.

  • SQL Server Authentication: If the machine agent is installed on the Linux platform, the SQL Server Authentication should be enabled for provided user credentials.
  • Windows Authentication: If the machine agent is installed on the 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 the 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.

  • MS SQL Server Stats
  • MS SQL IO Stats
  • MS SQL Performance Stats

MS SQL Server Stats

MetricTypeDescription
MS SQL Active Cursors sample Number of active cursors.
MS SQL Active Temp Tables sample Number of temporary tables.
MS SQL Active Transactions sample Number of active transactions.
MS SQL 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.
MS SQL 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.
MS SQL Auto-Param Attempts/Sec rate Number of auto param attempts per second.
MS SQL Latch Wait Time (ms)/Sec rate Latch wait time (in milliseconds) per second for latch requests that had to wait.
MS SQL Wait Time (ms)/Sec rate Time (in millisecond) for the selected type of wait per second.
MS SQL Backup and Restore Throughput/Sec rate Read/write throughput for backup and restore operations of a database per second.
MS SQL Batch Requests/Sec rate Number of Transact-SQL command batches received per second.
MS SQL 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.
MS SQL Bulk Copy Rows/Sec rate Number of bulk copy rows per second.
MS SQL Bulk Copy Throughput (KB)/Sec rate Amount of data bulk copied (in kilobytes) per second.
MS SQL 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.
MS SQL 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.
MS SQL Cache Hit Ratio sample Ratio between cache hits and lookups.
MS SQL Cache Objects sample Number of cache objects in the cache.
MS SQL Cache Objects In Use sample Number of cache object in use.
MS SQL Cache Pages sample Number of 8-kilobyte (KB) pages used by cache objects.
MS SQL Cached Cursors sample Number of cursors of a given type in the cache.
MS SQL 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.
MS SQL CLR Execution Time (mus) sample Total execution time in CLR (microseconds).
MS SQL Connection Memory (KB) sample Connection memory in kilobytes.
MS SQL Lob Readahead/Sec rate Number of lob pages per second on which readahead was issued.
MS SQL Pull In Row/Sec rate Number of column values per second that were pulled in-row from off-row.
MS SQL Push Off Row/Sec rate Number of column values per second that were pushed from in-row to off-row.
MS SQL Cursor Memory Usage (KB) sample Amount of memory consumed by cursors in kilobytes (KB).
MS SQL Cursor Requests/Sec rate Number of SQL cursor requests received by server per second.
MS SQL Cursor Worktable Usage sample Number of worktables used by cursors.
MS SQL Data File Size (KB) cumulative Cumulative size (in kilobytes) of all the data files in the database.
MS SQL Database Pages sample Number of database pages.
MS SQL DBCC Logical Scan Bytes/Sec rate Number of logical read scan bytes per second for database console commands (DBCC).
MS SQL 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.
MS SQL 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.
MS SQL 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.
MS SQL Errors/Sec rate Number of errors per second.
MS SQL Event Notifications Delayed Drop sample Number of event notifications waiting to be dropped by a system thread.
MS SQL Extent Deallocations/Sec rate Number of extents deallocated per second in all databases in this instance of SQL Server.
MS SQL Extent Allocated/Sec rate Number of extents allocated per second in all databases in this instance of SQL Server.
MS SQL 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.
MS SQL Failed Auto-Params/Sec rate Number of failed auto params per second.
MS SQL 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.
MS SQL 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.
MS SQL Forced Parameterizations/Sec rate Number of forced parameterizations per second.
MS SQL Forwarded Records/Sec rate Number of records per second fetched through forwarded record pointers.
MS SQL Free List Stalls/Sec rate Number of free list stalls per second.
MS SQL 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.
MS SQL 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.
MS SQL 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.
MS SQL Full Scans/Sec rate Number of unrestricted full scans per second. These can be either base-table or full-index scans.
MS SQL 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.
MS SQL HTTP Authenticated Requests/Sec rate Number of http authenticated requests per second.
MS SQL 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.
MS SQL Latch Waits/Sec rate Number of latch requests per second that could not be granted immediately.
MS SQL 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.
MS SQL LobHandle Created/Sec rate Number of temporary lobs handle created per second.
MS SQL LobHandle Destroyed/Sec rate Number of temporary lobs handle destroyed per second.
MS SQL LobSS Provider Create/Sec rate Number of lob Storage Service Providers (LobSSP) created per second. One worktable created per LobSSP.
MS SQL LobSS Provider Destroy/Sec rate Number of lob SS provider destroyed per second.
MS SQL LobSS Provider Truncation/Sec rate Number of lob ss provider truncates per second.
MS SQL 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.
MS SQL 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.
MS SQL Lock Memory (KB) sample Total amount of dynamic memory the server is using for locks.
MS SQL 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.
MS SQL 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.
MS SQL Lock Requests/Sec rate Number of lock requests per second.
MS SQL Lock Timeouts (Without NOWAIT)/Sec rate Number of lock requests per second that timed out, but excluding requests for NOWAIT locks.
MS SQL Lock Timeouts/Sec rate Number of lock requests per second that timed out, including requests for NOWAIT locks.
MS SQL Lock Wait Time (ms) sample Total wait time (in milliseconds) for locks in the last second.
MS SQL Lock Waits/Sec rate Number of lock requests per second that required the caller to wait.
MS SQL Log Bytes Flushed/Sec rate Number of log bytes flushed per second.
MS SQL Log Cache Hit Ratio (Pct) sample Percentage of log cache reads satisfied from the log cache.
MS SQL Log Cache Reads/Sec rate Number of log cache reads per second.
MS SQL Log File Size (KB) cumulative Cumulative size (in kilobytes) of all the transaction log files in the database.
MS SQL Log File Used Size (KB) cumulative Cumulative used size of all the log files in the database.
MS SQL 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.
MS SQL Log Flush Waits/Sec rate Number of commits per second waiting for the log flush.
MS SQL Log Flushes/Sec rate Number of log flushes per second.
MS SQL Log Growths/Sec rate Number of times per second the transaction log for the database has been expanded.
MS SQL Log Shrinks/Sec rate Number of times per second log shrinks for this database.
MS SQL Log Truncations/Sec rate Number of times per second the transaction log has been shrunk.
MS SQL Logical Connections sample Number of logical connections to the system.
MS SQL Logins/Sec rate Total number of logins started per second. This does not include pooled connections.
MS SQL Logouts/Sec rate Total number of logout operations started per second.
MS SQL Longest Transaction Running Time (ms) sample Transaction longest running time in milliseconds.
MS SQL Mars Deadlocks/Sec rate Number of MARS deadlocks per second.
MS SQL 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.
MS SQL Memory Grants Outstanding/Sec rate Total number of processes per second that have successfully acquired a workspace memory grant.
MS SQL Memory Grants Pending sample Total number of processes waiting for a workspace memory grant.
MS SQL 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.
MS SQL Non-Atomic Yields/Sec rate Number of non-atomic yields per second.
MS SQL 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.
MS SQL Active Cursor Plans sample Number of active cursor plans.
MS SQL Deadlocks/Sec rate Number of lock requests per second that resulted in a deadlock.
MS SQL Current SuperLatches sample Number of latches that are currently SuperLatches.
MS SQL Optimizer Memory (KB) sample Total amount of dynamic memory the server is using for query optimization.
MS SQL 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.
MS SQL 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.
MS SQL Page Life Expectancy (Sec) sample Number of seconds a page will stay in the buffer pool without references.
MS SQL Page Lookups/Sec rate Number of requests per second to find a page in the buffer pool.
MS SQL Page Reads/Sec rate Number of physical database page reads that are issued per second.
MS SQL Page Splits/Sec rate Number of page splits per second that occur as the result of overflowing index pages.
MS SQL Page Writes/Sec rate Number of physical database page writes that are issued per second.
MS SQL 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.
MS SQL Pages Compressed/Sec rate Number of data pages that are compressed by using PAGE compression. Includes all objects in the instance of SQL Server.
MS SQL Log Used (Pct) sample Percentage of space in the log that is in use.
MS SQL 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.
MS SQL Processes Blocked sample Number of currently blocked processes.
MS SQL Range Scans/Sec rate Number of qualified range scans through indexes per second.
MS SQL Readahead Pages/Sec rate Number of read ahead pages per second.
MS SQL 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.
MS SQL Safe Auto-Params/Sec rate Number of safe auto params per second.
MS SQL Scan Point Revalidations/Sec rate Number of times per second that the scan point had to be revalidated to continue the scan.
MS SQL Skipped Ghosted Records/Sec rate Number of ghosted records per second skipped during scans.
MS SQL Snapshot Transactions sample Number of currently active transactions using the snapshot isolation level.
MS SQL SOAP Empty Requests/Sec rate Number of empty SOAP requests started per second.
MS SQL SOAP Method Invocations/Sec rate Number of SOAP method invocations per second.
MS SQL SOAP Session Initiate Requests/Sec rate Number of SOAP session initiate requests per second.
MS SQL SOAP Session Terminate Requests/Sec rate Number of SOAP session terminate requests per second.
MS SQL SOAP SQL Requests/Sec rate Number of SOAP SQL requests started per second.
MS SQL SOAP WSDL Requests/Sec rate Number of SOAP Web Service Description Language requests started per second.
MS SQL SQL Attentions/Sec sample Number of attentions per second. An attention is a request by the client to end the currently running request
MS SQL SQL Cache Memory (KB) sample Total amount of dynamic memory the server is using for the dynamic SQL cache.
MS SQL 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.
MS SQL SQL Re-Compilations/Sec rate Number of statement recompiles per second.
MS SQL Stored Procedures Invoked/Sec rate Number of stored procedures invoked per second.
MS SQL SuperLatch Demotions/Sec rate Number of SuperLatches per second that have been demoted to regular latches in the last second.
MS SQL SuperLatch Promotions/Sec rate Number of latches per second that have been promoted to SuperLatches in the last second.
MS SQL Table Lock Escalations/Sec rate Number of times locks on a table were escalated to the TABLE or HoBT granularity.
MS SQL Target Pages sample Ideal number of pages in the buffer pool.
MS SQL Target Server Memory (KB) sample Ideal amount of memory the server can consume in kilobytes.
MS SQL 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.
MS SQL 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.
MS SQL 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.
MS SQL Tasks Running sample Number of activation stored procedures that are currently running.
MS SQL Tasks Started/Sec rate Number of activation stored procedures started per second by all queue monitors in the instance.
MS SQL Temp Tables Created/Sec rate Number of temporary tables/table variables created per second.
MS SQL Temp Tables For Destruction sample Number of temporary tables/table variables waiting to be destroyed by the cleanup system thread.
MS SQL Total Latch Wait Time (ms) sample Total latch wait time in milliseconds.
MS SQL Total Server Memory (KB) sample Amount of memory the server has committed using the memory manager in kilobytes.
MS SQL Total Transactions sample Total number of transactions.
MS SQL Transactions/Sec rate Number of transaction per second.
MS SQL Unsafe Auto-Params/Sec rate Number of unsafe auto-params per second.
MS SQL 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.
MS SQL Update Snapshot Transactions sample Number of currently active transactions using the snapshot isolation level and have modified data.
MS SQL 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.
MS SQL 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.
MS SQL User Connections sample Number of user connections.
MS SQL 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.
MS SQL Version Store Size (KB) sample The amount of space (in kilobytes) in tempdb being used to store snapshot isolation level row versions.
MS SQL 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.
MS SQL Version Store Units sample The number of active allocation units in the snapshot isolation version store in tempdb.
MS SQL 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.
MS SQL 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.
MS SQL 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.
MS SQL 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.
MS SQL 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.)
MS SQL Write Transactions/Sec rate Number of write transactions per second.

MS SQL IO Stats

IO stats works based on file id and data id.

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

MS SQL Performance Stats

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