Skip to content

Latest commit

 

History

History
314 lines (291 loc) · 43.5 KB

collector.mssql.md

File metadata and controls

314 lines (291 loc) · 43.5 KB

mssql collector

The mssql collector exposes metrics about the MSSQL server

Metric name prefix mssql
Classes Win32_PerfRawData_MSSQLSERVER_SQLServerAccessMethods
Win32_PerfRawData_MSSQLSERVER_SQLServerAvailabilityReplica
Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager
Win32_PerfRawData_MSSQLSERVER_SQLServerDatabaseReplica
Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases
Win32_PerfRawData_MSSQLSERVER_SQLServerGeneralStatistics
Win32_PerfRawData_MSSQLSERVER_SQLServerLocks
Win32_PerfRawData_MSSQLSERVER_SQLServerMemoryManager
Win32_PerfRawData_MSSQLSERVER_SQLServerSQLStatistics
Win32_PerfRawData_MSSQLSERVER_SQLServerSQLErrors
Win32_PerfRawData_MSSQLSERVER_SQLServerTransactions
Win32_PerfRawData_MSSQLSERVER_SQLServerWaitStatistics
Enabled by default? No

Flags

--collectors.mssql.classes-enabled

Comma-separated list of MSSQL WMI classes to use. Supported values are accessmethods, availreplica, bufman, databases, dbreplica, genstats, locks, memmgr, sqlstats, sqlerrors, transactions, and waitstats.

--collectors.mssql.class-print

If true, print available mssql WMI classes and exit. Only displays if the mssql collector is enabled.

Metrics

Name Description Type Labels
windows_mssql_collector_duration_seconds The time taken for each sub-collector to return gauge collector, mssql_instance
windows_mssql_collector_success 1 if sub-collector succeeded, 0 otherwise gauge collector, mssql_instance
windows_mssql_accessmethods_au_batch_cleanups The total number of batches that were completed successfully by the background task that cleans up deferred dropped allocation units counter mssql_instance
windows_mssql_accessmethods_au_cleanups The total number of allocation units that were successfully dropped the background task that cleans up deferred dropped allocation units. Each allocation unit drop requires multiple batches counter mssql_instance
windows_mssql_accessmethods_by_reference_lob_creates The total count of large object (lob) values that were passed by reference. By-reference lobs are used in certain bulk operations to avoid the cost of passing them by value counter mssql_instance
windows_mssql_accessmethods_by_reference_lob_uses The total count of by-reference lob values that were used. By-reference lobs are used in certain bulk operations to avoid the cost of passing them by-value counter mssql_instance
windows_mssql_accessmethods_lob_read_aheads The total count of lob pages on which readahead was issued counter mssql_instance
windows_mssql_accessmethods_column_value_pulls The total count of column values that were pulled in-row from off-row counter mssql_instance
windows_mssql_accessmethods_column_value_pushes The total count of column values that were pushed from in-row to off-row counter mssql_instance
windows_mssql_accessmethods_deferred_dropped_aus The total number of allocation units waiting to be dropped by the background task that cleans up deferred dropped allocation units gauge mssql_instance
windows_mssql_accessmethods_deferred_dropped_rowsets The number of rowsets created 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 gauge mssql_instance
windows_mssql_accessmethods_dropped_rowset_cleanups The 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 counter mssql_instance
windows_mssql_accessmethods_dropped_rowset_skips The 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 counter mssql_instance
windows_mssql_accessmethods_extent_deallocations Number of extents deallocated per second in all databases in this instance of SQL Server counter mssql_instance
windows_mssql_accessmethods_extent_allocations Number of extents allocated per second in all databases in this instance of SQL Server counter mssql_instance
windows_mssql_accessmethods_au_batch_cleanup_failures 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 counter mssql_instance
windows_mssql_accessmethods_leaf_page_cookie_failures The number of times 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 counter mssql_instance
windows_mssql_accessmethods_tree_page_cookie_failures The number of times 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 counter mssql_instance
windows_mssql_accessmethods_forwarded_records Number of records per second fetched through forwarded record pointers counter mssql_instance
windows_mssql_accessmethods_free_space_page_fetches 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 counter mssql_instance
windows_mssql_accessmethods_free_space_scans 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 counter mssql_instance
windows_mssql_accessmethods_full_scans Number of unrestricted full scans per second. These can be either base-table or full-index scans counter mssql_instance
windows_mssql_accessmethods_index_searches 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 counter mssql_instance
windows_mssql_accessmethods_insysxact_waits Number of times a reader needs to wait for a page because the InSysXact bit is set counter mssql_instance
windows_mssql_accessmethods_lob_handle_creates Count of temporary lobs created counter mssql_instance
windows_mssql_accessmethods_lob_handle_destroys Count of temporary lobs destroyed counter mssql_instance
windows_mssql_accessmethods_lob_ss_provider_creates Count of LOB Storage Service Providers (LobSSP) created. One worktable created per LobSSP counter mssql_instance
windows_mssql_accessmethods_lob_ss_provider_destroys Count of LobSSP destroyed counter mssql_instance
windows_mssql_accessmethods_lob_ss_provider_truncations Count of LobSSP truncated counter mssql_instance
windows_mssql_accessmethods_mixed_page_allocations 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 counter mssql_instance
windows_mssql_accessmethods_page_compression_attempts 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 counter mssql_instance
windows_mssql_accessmethods_page_deallocations Number of pages deallocated per second in all databases in this instance of SQL Server. These include pages from mixed extents and uniform extents counter mssql_instance
windows_mssql_accessmethods_page_allocations 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 counter mssql_instance
windows_mssql_accessmethods_page_compressions Number of data pages that are compressed by using PAGE compression. Includes all objects in the instance of SQL Server counter mssql_instance
windows_mssql_accessmethods_page_splits Number of page splits per second that occur as the result of overflowing index pages counter mssql_instance
windows_mssql_accessmethods_probe_scans Number of probe scans per second that are used to find at most one single qualified row in an index or base table directly counter mssql_instance
windows_mssql_accessmethods_range_scans Number of qualified range scans through indexes per second counter mssql_instance
windows_mssql_accessmethods_scan_point_revalidations Number of times per second that the scan point had to be revalidated to continue the scan counter mssql_instance
windows_mssql_accessmethods_ghost_record_skips Number of ghosted records per second skipped during scans counter mssql_instance
windows_mssql_accessmethods_table_lock_escalations Number of times locks on a table were escalated to the TABLE or HoBT granularity counter mssql_instance
windows_mssql_accessmethods_leaf_page_cookie_uses Number of times 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 counter mssql_instance
windows_mssql_accessmethods_tree_page_cookie_uses Number of times 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 counter mssql_instance
windows_mssql_accessmethods_workfile_creates Number of work files created per second. For example, work files could be used to store temporary results for hash joins and hash aggregates counter mssql_instance
windows_mssql_accessmethods_worktables_creates 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 counter mssql_instance
windows_mssql_accessmethods_worktables_from_cache_ratio 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 counter mssql_instance
windows_mssql_availreplica_received_from_replica_bytes Number of bytes received from the availability replica per second. Pings and status updates will generate network traffic even on databases with no user updates counter mssql_instance, replica
windows_mssql_availreplica_sent_to_replica_bytes Number of bytes sent to the remote availability replica per second. On the primary replica this is the number of bytes sent to the secondary replica. On the secondary replica this is the number of bytes sent to the primary replica counter mssql_instance, replica
windows_mssql_availreplica_sent_to_transport_bytes Actual number of bytes sent per second over the network to the remote availability replica. On the primary replica this is the number of bytes sent to the secondary replica. On the secondary replica this is the number of bytes sent to the primary replica counter mssql_instance, replica
windows_mssql_availreplica_initiated_flow_controls Time in milliseconds that log stream messages waited for send flow control, in the last second counter mssql_instance, replica
windows_mssql_availreplica_flow_control_wait_seconds Number of times flow-control initiated in the last second. Flow Control Time (ms/sec) divided by Flow Control/sec is the average time per wait counter mssql_instance, replica
windows_mssql_availreplica_receives_from_replica Number of Always On messages received from thereplica per second counter mssql_instance, replica
windows_mssql_availreplica_resent_messages Number of Always On messages resent in the last second counter mssql_instance, replica
windows_mssql_availreplica_sends_to_replica Number of Always On messages sent to this availability replica per second counter mssql_instance, replica
windows_mssql_availreplica_sends_to_transport Actual number of Always On messages sent per second over the network to the remote availability replica counter mssql_instance, replica
windows_mssql_bufman_background_writer_pages Number of pages flushed to enforce the recovery interval settings counter mssql_instance
windows_mssql_bufman_buffer_cache_hit_ratio 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 gauge mssql_instance
windows_mssql_bufman_checkpoint_pages Indicates the number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed counter mssql_instance
windows_mssql_bufman_database_pages Indicates the number of pages in the buffer pool with database content gauge mssql_instance
windows_mssql_bufman_extension_allocated_pages Total number of non-free cache pages in the buffer pool extension file gauge mssql_instance
windows_mssql_bufman_extension_free_pages Total number of free cache pages in the buffer pool extension file gauge mssql_instance
windows_mssql_bufman_extension_in_use_as_percentage Not yet documented gauge mssql_instance
windows_mssql_bufman_extension_outstanding_io Percentage of the buffer pool extension paging file occupied by buffer manager pages gauge mssql_instance
windows_mssql_bufman_extension_page_evictions Number of pages evicted from the buffer pool extension file per second counter mssql_instance
windows_mssql_bufman_extension_page_reads Number of pages read from the buffer pool extension file per second counter mssql_instance
windows_mssql_bufman_extension_page_unreferenced_seconds Average seconds a page will stay in the buffer pool extension without references to it gauge mssql_instance
windows_mssql_bufman_extension_page_writes Number of pages written to the buffer pool extension file per second counter mssql_instance
windows_mssql_bufman_free_list_stalls Indicates the number of requests per second that had to wait for a free page counter mssql_instance
windows_mssql_bufman_integral_controller_slope The slope that integral controller for the buffer pool last used, times -10 billion gauge mssql_instance
windows_mssql_bufman_lazywrites Indicates the number of buffers written per second by the buffer manager's lazy writer counter mssql_instance
windows_mssql_bufman_page_life_expectancy_seconds Indicates the number of seconds a page will stay in the buffer pool without references gauge mssql_instance
windows_mssql_bufman_page_lookups Indicates the number of requests per second to find a page in the buffer pool counter mssql_instance
windows_mssql_bufman_page_reads Indicates the number of physical database page reads that are issued per second counter mssql_instance
windows_mssql_bufman_page_writes Indicates the number of physical database page writes that are issued per second counter mssql_instance
windows_mssql_bufman_read_ahead_pages Indicates the number of pages read per second in anticipation of use counter mssql_instance
windows_mssql_bufman_read_ahead_issuing_seconds Time (microseconds) spent issuing readahead counter mssql_instance
windows_mssql_bufman_target_pages Ideal number of pages in the buffer pool gauge mssql_instance
windows_mssql_dbreplica_database_flow_control_wait_seconds Not yet documented gauge mssql_instance, replica
windows_mssql_dbreplica_database_initiated_flow_controls Not yet documented counter mssql_instance, replica
windows_mssql_dbreplica_received_file_bytes Not yet documented counter mssql_instance, replica
windows_mssql_dbreplica_group_commits Not yet documented counter mssql_instance, replica
windows_mssql_dbreplica_group_commit_stall_seconds Not yet documented counter mssql_instance, replica
windows_mssql_dbreplica_log_apply_pending_queue Not yet documented gauge mssql_instance, replica
windows_mssql_dbreplica_log_apply_ready_queue Not yet documented gauge mssql_instance, replica
windows_mssql_dbreplica_log_compressed_bytes Not yet documented counter mssql_instance, replica
windows_mssql_dbreplica_log_decompressed_bytes Not yet documented counter mssql_instance, replica
windows_mssql_dbreplica_log_received_bytes Not yet documented counter mssql_instance, replica
windows_mssql_dbreplica_log_compression_cachehits Not yet documented counter mssql_instance, replica
windows_mssql_dbreplica_log_compression_cachemisses Not yet documented counter mssql_instance, replica
windows_mssql_dbreplica_log_compressions Not yet documented counter mssql_instance, replica
windows_mssql_dbreplica_log_decompressions Not yet documented counter mssql_instance, replica
windows_mssql_dbreplica_log_remaining_for_undo The amount of log, in bytes, remaining to complete the undo phase gauge mssql_instance, replica
windows_mssql_dbreplica_log_send_queue Amount of log records in the log files of the primary database, in kilobytes, that haven't been sent to the secondary replica gauge mssql_instance, replica
windows_mssql_dbreplica_mirrored_write_transactions Number of transactions that were written to the primary database and then waited to commit until the log was sent to the secondary database, in the last second counter mssql_instance, replica
windows_mssql_dbreplica_recovery_queue_records Amount of log records in the log files of the secondary replica that have not been redone gauge mssql_instance, replica
windows_mssql_dbreplica_redo_blocks Number of times the redo thread was blocked on locks held by readers of the database counter mssql_instance, replica
windows_mssql_dbreplica_redo_remaining_bytes The amount of log, in kilobytes, remaining to be redone to finish the reverting phase gauge mssql_instance, replica
windows_mssql_dbreplica_redone_bytes Amount of log records redone on the secondary database in the last second counter mssql_instance, replica
windows_mssql_dbreplica_redones Not yet documented counter mssql_instance, replica
windows_mssql_dbreplica_total_log_requiring_undo Total kilobytes of log that must be undone gauge mssql_instance, replica
windows_mssql_dbreplica_transaction_delay_seconds Delay in waiting for unterminated commit acknowledgment for all the current transactions gauge mssql_instance, replica
windows_mssql_databases_active_transactions Number of active transactions for the database gauge mssql_instance, database
windows_mssql_databases_backup_restore_operations Read/write throughput for backup and restore operations of a database per second counter mssql_instance, database
windows_mssql_databases_bulk_copy_rows Number of rows bulk copied per second counter mssql_instance, database
windows_mssql_databases_bulk_copy_bytes Amount of data bulk copied (in kilobytes) per second counter mssql_instance, database
windows_mssql_databases_commit_table_entries he size (row count) of the in-memory portion of the commit table for the database gauge mssql_instance, database
windows_mssql_databases_data_files_size_bytes Cumulative size (in kilobytes) of all the data files in the database including any automatic growth. Monitoring this counter is useful, for example, for determining the correct size of tempdb gauge mssql_instance, database
windows_mssql_databases_dbcc_logical_scan_bytes Number of logical read scan bytes per second for database console commands (DBCC) counter mssql_instance, database
windows_mssql_databases_group_commit_stall_seconds Group stall time (microseconds) per second counter mssql_instance, database
windows_mssql_databases_log_flushed_bytes Total number of log bytes flushed counter mssql_instance, database
windows_mssql_databases_log_cache_hit_ratio Percentage of log cache reads satisfied from the log cache gauge mssql_instance, database
windows_mssql_databases_log_cache_reads Reads performed per second through the log manager cache counter mssql_instance, database
windows_mssql_databases_log_files_size_bytes Cumulative size (in kilobytes) of all the transaction log files in the database gauge mssql_instance, database
windows_mssql_databases_log_files_used_size_bytes The cumulative used size of all the log files in the database gauge mssql_instance, database
windows_mssql_databases_log_flushes Total wait time (in milliseconds) to flush the log. On an Always On secondary database, this value indicates the wait time for log records to be hardened to disk counter mssql_instance, database
windows_mssql_databases_log_flush_waits Number of commits per second waiting for the log flush counter mssql_instance, database
windows_mssql_databases_log_flush_wait_seconds Number of commits per second waiting for the log flush gauge mssql_instance, database
windows_mssql_databases_log_flush_write_seconds Time in milliseconds for performing writes of log flushes that were completed in the last second gauge mssql_instance, database
windows_mssql_databases_log_growths Total number of times the transaction log for the database has been expanded gauge mssql_instance, database
windows_mssql_databases_log_pool_cache_misses Number of requests for which the log block was not available in the log pool counter mssql_instance, database
windows_mssql_databases_log_pool_disk_reads Number of disk reads that the log pool issued to fetch log blocks counter mssql_instance, database
windows_mssql_databases_log_pool_hash_deletes Rate of raw hash entry deletes from the Log Pool counter mssql_instance, database
windows_mssql_databases_log_pool_hash_inserts Rate of raw hash entry inserts into the Log Pool counter mssql_instance, database
windows_mssql_databases_log_pool_invalid_hash_entries Rate of hash lookups failing due to being invalid counter mssql_instance, database
windows_mssql_databases_log_pool_log_scan_pushes Rate of Log block pushes by log scans, which may come from disk or memory counter mssql_instance, database
windows_mssql_databases_log_pool_log_writer_pushes Rate of Log block pushes by log writer thread counter mssql_instance, database
windows_mssql_databases_log_pool_empty_free_pool_pushes Rate of Log block push fails due to empty free pool counter mssql_instance, database
windows_mssql_databases_log_pool_low_memory_pushes Rate of Log block push fails due to being low on memory counter mssql_instance, database
windows_mssql_databases_log_pool_no_free_buffer_pushes Rate of Log block push fails due to free buffer unavailable counter mssql_instance, database
windows_mssql_databases_log_pool_req_behind_trunc Log pool cache misses due to block requested being behind truncation LSN counter mssql_instance, database
windows_mssql_databases_log_pool_requests_old_vlf Log Pool requests that were not in the last VLF of the log counter mssql_instance, database
windows_mssql_databases_log_pool_requests The number of log-block requests processed by the log pool counter mssql_instance, database
windows_mssql_databases_log_pool_total_active_log_bytes Current total active log stored in the shared cache buffer manager in bytes gauge mssql_instance, database
windows_mssql_databases_log_pool_total_shared_pool_bytes Current total memory usage of the shared cache buffer manager in bytes gauge mssql_instance, database
windows_mssql_databases_log_shrinks Total number of log shrinks for this database gauge mssql_instance, database
windows_mssql_databases_log_truncations The number of times the transaction log has been truncated (in Simple Recovery Model) gauge mssql_instance, database
windows_mssql_databases_log_used_percent Percentage of space in the log that is in use gauge mssql_instance, database
windows_mssql_databases_pending_repl_transactions Number of transactions in the transaction log of the publication database marked for replication, but not yet delivered to the distribution database gauge mssql_instance, database
windows_mssql_databases_repl_transactions Number of transactions per second read out of the transaction log of the publication database and delivered to the distribution database counter mssql_instance, database
windows_mssql_databases_shrink_data_movement_bytes Amount of data being moved per second by autoshrink operations, or DBCC SHRINKDATABASE or DBCC SHRINKFILE statements counter mssql_instance, database
windows_mssql_databases_tracked_transactions Number of committed transactions recorded in the commit table for the database counter mssql_instance, database
windows_mssql_databases_transactions Number of transactions started for the database per second counter mssql_instance, database
windows_mssql_databases_write_transactions Number of transactions that wrote to the database and committed, in the last second counter mssql_instance, database
windows_mssql_databases_xtp_controller_dlc_fetch_latency_seconds Average latency in microseconds between log blocks entering the Direct Log Consumer and being retrieved by the XTP controller, per second gauge mssql_instance, database
windows_mssql_databases_xtp_controller_dlc_peak_latency_seconds The largest recorded latency, in microseconds, of a fetch from the Direct Log Consumer by the XTP controller gauge mssql_instance, database
windows_mssql_databases_xtp_controller_log_processed_bytes The amount of log bytes processed by the XTP controller thread, per second counter mssql_instance, database
windows_mssql_databases_xtp_memory_used_bytes The amount of memory used by XTP in the database gauge mssql_instance, database
windows_mssql_genstats_active_temp_tables Number of temporary tables/table variables in use gauge mssql_instance
windows_mssql_genstats_connection_resets Total number of logins started from the connection pool counter mssql_instance
windows_mssql_genstats_event_notifications_delayed_drop Number of event notifications waiting to be dropped by a system thread gauge mssql_instance
windows_mssql_genstats_http_authenticated_requests Number of authenticated HTTP requests started per second gauge mssql_instance
windows_mssql_genstats_logical_connections Number of logical connections to the system gauge mssql_instance
windows_mssql_genstats_logins Total number of logins started per second. This does not include pooled connections counter mssql_instance
windows_mssql_genstats_logouts Total number of logout operations started per second counter mssql_instance
windows_mssql_genstats_mars_deadlocks Number of MARS deadlocks detected gauge mssql_instance
windows_mssql_genstats_non_atomic_yields Number of non-atomic yields per second counter mssql_instance
windows_mssql_genstats_blocked_processes Number of currently blocked processes gauge mssql_instance
windows_mssql_genstats_soap_empty_requests Number of empty SOAP requests started per second gauge mssql_instance
windows_mssql_genstats_soap_method_invocations Number of SOAP method invocations started per second gauge mssql_instance
windows_mssql_genstats_soap_session_initiate_requests Number of SOAP Session initiate requests started per second gauge mssql_instance
windows_mssql_genstats_soap_session_terminate_requests Number of SOAP Session terminate requests started per second gauge mssql_instance
windows_mssql_genstats_soapsql_requests Number of SOAP SQL requests started per second gauge mssql_instance
windows_mssql_genstats_soapwsdl_requests Number of SOAP Web Service Description Language requests started per second gauge mssql_instance
windows_mssql_genstats_sql_trace_io_provider_lock_waits Number of waits for the File IO Provider lock per second gauge mssql_instance
windows_mssql_genstats_tempdb_recovery_unit_ids_generated Number of duplicate tempdb recovery unit id generated gauge mssql_instance
windows_mssql_genstats_tempdb_rowset_ids_generated Number of duplicate tempdb rowset id generated gauge mssql_instance
windows_mssql_genstats_temp_tables_creations Number of temporary tables/table variables created per second counter mssql_instance
windows_mssql_genstats_temp_tables_awaiting_destruction Number of temporary tables/table variables waiting to be destroyed by the cleanup system thread gauge mssql_instance
windows_mssql_genstats_trace_event_notification_queue_size Number of trace event notification instances waiting in the internal queue to be sent through Service Broker gauge mssql_instance
windows_mssql_genstats_transactions Number of transaction enlistments (local, DTC, bound all combined) gauge mssql_instance
windows_mssql_genstats_user_connections Counts the number of users currently connected to SQL Server gauge mssql_instance
windows_mssql_locks_average_wait_seconds Average amount of wait time (in milliseconds) for each lock request that resulted in a wait gauge mssql_instance, resource
windows_mssql_locks_lock_requests Number of new locks and lock conversions per second requested from the lock manager counter mssql_instance, resource
windows_mssql_locks_lock_timeouts Number of lock requests per second that timed out, including requests for NOWAIT locks counter mssql_instance, resource
windows_mssql_locks_lock_timeouts_excluding_NOWAIT Number of lock requests per second that timed out, but excluding requests for NOWAIT locks counter mssql_instance, resource
windows_mssql_locks_lock_waits Total wait time (in milliseconds) for locks in the last second counter mssql_instance, resource
windows_mssql_locks_lock_wait_seconds Number of lock requests per second that required the caller to wait gauge mssql_instance, resource
windows_mssql_locks_deadlocks Number of lock requests per second that resulted in a deadlock counter mssql_instance, resource
windows_mssql_memmgr_connection_memory_bytes Specifies the total amount of dynamic memory the server is using for maintaining connections gauge mssql_instance
windows_mssql_memmgr_database_cache_memory_bytes Specifies the amount of memory the server is currently using for the database pages cache gauge mssql_instance
windows_mssql_memmgr_external_benefit_of_memory An internal estimation of the performance benefit from adding memory to a specific cache gauge mssql_instance
windows_mssql_memmgr_free_memory_bytes Specifies the amount of committed memory currently not used by the server gauge mssql_instance
windows_mssql_memmgr_granted_workspace_memory_bytes Specifies the total amount of memory currently granted to executing processes, such as hash, sort, bulk copy, and index creation operations gauge mssql_instance
windows_mssql_memmgr_lock_blocks Specifies the 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 gauge mssql_instance
windows_mssql_memmgr_allocated_lock_blocks Specifies the 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 gauge mssql_instance
windows_mssql_memmgr_lock_memory_bytes Specifies the total amount of dynamic memory the server is using for locks gauge mssql_instance
windows_mssql_memmgr_lock_owner_blocks Specifies the 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 gauge mssql_instance
windows_mssql_memmgr_allocated_lock_owner_blocks Not yet documented gauge mssql_instance
windows_mssql_memmgr_log_pool_memory_bytes Total amount of dynamic memory the server is using for Log Pool gauge mssql_instance
windows_mssql_memmgr_maximum_workspace_memory_bytes Indicates the maximum amount of memory available for executing processes, such as hash, sort, bulk copy, and index creation operations gauge mssql_instance
windows_mssql_memmgr_outstanding_memory_grants Specifies the total number of processes that have successfully acquired a workspace memory grant gauge mssql_instance
windows_mssql_memmgr_pending_memory_grants Specifies the total number of processes waiting for a workspace memory grant gauge mssql_instance
windows_mssql_memmgr_optimizer_memory_bytes Specifies the total amount of dynamic memory the server is using for query optimization gauge mssql_instance
windows_mssql_memmgr_reserved_server_memory_bytes ndicates the amount of memory the server has reserved for future usage. This counter shows the current unused amount of memory initially granted that is shown in Granted Workspace Memory gauge mssql_instance
windows_mssql_memmgr_sql_cache_memory_bytes Specifies the total amount of dynamic memory the server is using for the dynamic SQL cache gauge mssql_instance
windows_mssql_memmgr_stolen_server_memory_bytes Specifies the amount of memory the server is using for purposes other than database pages gauge mssql_instance
windows_mssql_memmgr_target_server_memory_bytes Indicates the ideal amount of memory the server can consume gauge mssql_instance
windows_mssql_memmgr_total_server_memory_bytes Specifies the amount of memory the server has committed using the memory manager gauge mssql_instance
windows_mssql_sqlstats_auto_parameterization_attempts Number of failed auto-parameterization attempts per second. This should be small. Note that auto-parameterizations are also known as simple parameterizations in later versions of SQL Server counter mssql_instance
windows_mssql_sqlstats_batch_requests Not yet documented counter mssql_instance
windows_mssql_sqlstats_failed_auto_parameterization_attempts Not yet documented counter mssql_instance
windows_mssql_sqlstats_forced_parameterizations Number of successful forced parameterizations per second counter mssql_instance
windows_mssql_sqlstats_guided_plan_executions Number of plan executions per second in which the query plan has been generated by using a plan guide counter mssql_instance
windows_mssql_sqlstats_misguided_plan_executions Number of plan executions per second in which a plan guide could not be honored during plan generation counter mssql_instance
windows_mssql_sqlstats_safe_auto_parameterization_attempts Number of safe auto-parameterization attempts per second counter mssql_instance
windows_mssql_sqlstats_sql_attentions Number of attentions per second counter mssql_instance
windows_mssql_sqlstats_sql_compilations Number of SQL compilations per second counter mssql_instance
windows_mssql_sqlstats_sql_recompilations Number of statement recompiles per second counter mssql_instance
windows_mssql_sqlstats_unsafe_auto_parameterization_attempts Number of unsafe auto-parameterization attempts per second. counter mssql_instance
windows_mssql_sql_errors_total Information for all errors counter mssql_instance, resource
windows_mssql_transactions_tempdb_free_space_bytes The amount of space (in kilobytes) available in tempdb gauge mssql_instance
windows_mssql_transactions_longest_transaction_running_seconds The length of time (in seconds) since the start of the transaction that has been active longer than any other current transaction gauge mssql_instance
windows_mssql_transactions_nonsnapshot_version_active_total The 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 counter mssql_instance
windows_mssql_transactions_snapshot_active_total The number of currently active transactions using the snapshot isolation level counter mssql_instance
windows_mssql_transactions_active The number of currently active transactions of all types gauge mssql_instance
windows_mssql_transactions_update_conflicts_total The percentage of those transactions using the snapshot isolation level that have encountered update conflicts within the last second counter mssql_instance
windows_mssql_transactions_update_snapshot_active_total The number of currently active transactions using the snapshot isolation level and have modified data counter mssql_instance
windows_mssql_transactions_version_cleanup_rate_bytes The rate (in kilobytes per second) at which row versions are removed from the snapshot isolation version store in tempdb gauge mssql_instance
windows_mssql_transactions_version_generation_rate_bytes The rate (in kilobytes per second) at which new row versions are added to the snapshot isolation version store in tempdb gauge mssql_instance
windows_mssql_transactions_version_store_size_bytes he amount of space (in kilobytes) in tempdb being used to store snapshot isolation level row versions gauge mssql_instance
windows_mssql_transactions_version_store_units The number of active allocation units in the snapshot isolation version store in tempdb counter mssql_instance
windows_mssql_transactions_version_store_creation_units The number of allocation units that have been created in the snapshot isolation store since the instance of the Database Engine was started counter mssql_instance
windows_mssql_transactions_version_store_truncation_units The number of allocation units that have been removed from the snapshot isolation store since the instance of the Database Engine was started counter mssql_instance
windows_mssql_waitstats_lock_waits Statistics for processes waiting on a lock gauge mssql_instance, item
windows_mssql_waitstats_memory_grant_queue_waits Statistics for processes waiting for memory grant to become available gauge mssql_instance, item
windows_mssql_waitstats_thread_safe_memory_objects_waits Statistics for processes waiting on thread-safe memory allocators gauge mssql_instance, item
windows_mssql_waitstats_log_write_waits Statistics for processes waiting for log buffer to be written gauge mssql_instance, item
windows_mssql_waitstats_log_buffer_waits Statistics for processes waiting for log buffer to be available gauge mssql_instance, item
windows_mssql_waitstats_network_io_waits Statistics relevant to wait on network I/O gauge mssql_instance, item
windows_mssql_waitstats_page_io_latch_waits Statistics relevant to page I/O latches gauge mssql_instance, item
windows_mssql_waitstats_page_latch_waits Statistics relevant to page latches, not including I/O latches gauge mssql_instance, item
windows_mssql_waitstats_nonpage_latch_waits Statistics relevant to non-page latches gauge mssql_instance, item
windows_mssql_waitstats_wait_for_the_worker_waits Statistics relevant to processes waiting for worker to become available gauge mssql_instance, item
windows_mssql_waitstats_workspace_synchronization_waits Statistics relevant to processes synchronizing access to workspace gauge mssql_instance, item
windows_mssql_waitstats_transaction_ownership_waits Statistics relevant to processes synchronizing access to transaction gauge mssql_instance, item

Example metric

Query Full table scans

rate(windows_mssql_accessmethods_full_scans{instance='host:9182'}[$__rate_interval])

Useful queries

Database file size

This collector retrieves the two values (data file and log file) and sums the total in PromQL to give the Database file size.

windows_mssql_databases_data_files_size_bytes{database='DatabaseName',instance='host:9182'} + windows_mssql_databases_log_files_size_bytes{database='DatabaseName',instance='host:9182'}

Buffer Cache Hit Ratio

When you read the counter in perfmon you will get the the percentage pages found in the buffer cache. This percentage is calculated internally based on the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. This collector retrieves the two internal values separately. In order to calculate the Buffer Cache Hit Ratio in PromQL.

windows_mssql_bufman_buffer_cache_hits{instance="host:9182", mssql_instance="MSSQLSERVER"} /
windows_mssql_bufman_buffer_cache_lookups{instance="host:9182", mssql_instance="MSSQLSERVER"}

This principal can be used for following metrics too:

  • AccessMethodsWorktablesFromCacheHitRatio
    • accessmethods_worktables_from_cache_hits
    • accessmethods_worktables_from_cache_lookups
  • LogCacheHitRatio
    • databases_log_cache_hits
    • databases_log_cache_lookups
  • AverageLockWaitTime
    • locks_wait_time_seconds
    • locks_count

Alerting examples

groups:
- name: database_alerts
  rules:
  - alert: DatabaseSizeExceeded
    expr: windows_mssql_databases_data_files_size_bytes{database='DatabaseName',instance='host:9182'} + windows_mssql_databases_log_files_size_bytes{database='DatabaseName',instance='host:9182'} > 1e10  # 10 GB in bytes
    for: 5m  # Trigger the alert if the condition persists for 5 mins
    labels:
      severity: critical
    annotations:
      summary: "SQl EXpress Database size exceeded 10GB"
      description: "The database size has grown larger than 10GB. Instance: {{ $labels.instance }}"