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
- Communication from machine on which the agent is installed to DB Server.
- 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
| Metric | Type | Description |
|---|
| 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.
| Metric | Type | Description |
|---|---|---|
| MS SQL Data File Avg Read Size (KB) | sample | Data file average read size in Kilobytes. |
| MS SQL Data File Avg Write Size (KB) | sample | Data file average write size in Kilobytes. |
| MS SQL Data File Bytes Read/Sec | rate | Data file bytes read per second. |
| MS SQL Data File Bytes Write/Sec | rate | Data file bytes write per second. |
| MS SQL Data File IO Stalls | sample | Data file IO stalls. |
| MS SQL Data File Read/Sec | rate | Number of read per second from data file. |
| MS SQL Data File Write/Sec | rate | Number of write per second into data file. |
| MS SQL Log File Avg Read Size (KB) | sample | Log file average read size in Kilobytes. |
| MS SQL Log File Avg Write Size (KB) | sample | Log file average write size in Kilobytes. |
| MS SQL Log File Bytes Read/Sec | rate | Log file bytes read per second. |
| MS SQL Log File Bytes Write/Sec | rate | Log file bytes write per second. |
| MS SQL Log File IO Stalls | sample | Log file Io stalls. |
| MS SQL Log File Read/Sec | rate | Number of read per second from log file. |
| MS SQL Log File Write/Sec | rate | Number of write per second into log file. |
MS SQL Performance Stats
| Metric | Type | Description |
|---|---|---|
| MS SQL Query/Min | sample | Number of query executing per minute. |
| MS SQL Avg Query CPU Time (ms) | sample | Average query CPU time in milliseconds. |
| MS SQL Avg Query Elapsed Time (ms) | sample | Average query elapsed time in milliseconds. |
