Microsoft SQL Server monitoring and management plugin
Verax NMS Microsoft SQL Server management plugin enables easy monitoring, alerting, health check, management and performance reporting for Microsoft SQL Server. SQL Server versions 2005, 2008 and higher are fully supported. A limited support including monitoring, sensors and counters is available for the 2000 edition. The plugin uses SQL queries in order to communicate with SQL Server instances.
General information view
- Software version: product name (e.g. SQL Server 2008), version number, build number and build date, service packs applied and platform (e.g. X86).
- Configuration: server name, language and default collation, number of processors.
- Usage statistics: number of databases, number of connections, active / idle sessions, active / blocked transactions, CPU usage chart, I/O usage chart.
Provides detailed configuration information and configuration change history:
- Detailed list of configuration parameters (such as affinity I/O mask, c2audit mode and others along with current value, description and in use/dynamic/advanced flags status.
- Configuration changelog with date, host, login and message indicating what kind of configuration change has been made.
The schema audit view allows tracking changes in the database structure (schema) such as added tables, changed columns, new indexes, etc. It enables quick diagnosing and troubleshooting of problems related to schema changes. The view presents a detailed schema changelog for each database including:
- Date when change was made.
- Database name, operation type (e.g. adding an index) and object that the operation was performed on (e.g. a table).
- Login information and application name for the user who was applying the change.
Server memory view
The server memory view enables diagnosing and troubleshooting problems related to Microsoft SQL Server memory management. The view provides the following information:
Memory manager statistics including:
- Lock Owner Blocks
- Granted Workspace Memory
- Memory Grants Pending
- Memory Grants Outstanding
- Lock Blocks
- Optimizer memory
- Connection Memory
- SQL Cache Memory
- Lock Memory
- Lock Blocks Allocated
- Lock Owner Blocks Allocated
- Total Server Memory
- Maximum Workspace Memory
- Target Server Memory
- Detailed memory components summary listing all components (e.g. CACHESTORE_TEMPTABLES, MEMORYCLERK_SQLBUFFERPOOL, MEMORYCLERK_SQLQUERYPLAN, etc.) with their current allocated, reserved and committed memory sizes.
The activity view provides current session summary with the following information:
- Session Id
- Login time
- Connecting host
- Connecting application name
- CPU usage
- RAM usage
- Number of reads made during the session
- Number of writes made during the session
- Number of SQL connections within the session
The view also provides top CPU usage and top memory usage session charts for easy identification of users or applications with excessive Microsoft SQL Server resource consumption.
The view provides summary for top SQL queries executed against SQL Server databases. It enables administrators and developers to identify performance bottlenecks and remedy them with SQL optimization or database structure optimization (e.g. adding indexes). The following information is provided for each query:
- Query text (SQL)
- Total CPU time consumed
- Average CPU time consumed per query
- Total numbers of reads and writes for the query
- Total and average I/O operations required to service the query
- Number of query executions and last execution time
The view provides a summary of databases created on the SQL Server including:
- Name and creation date
- User access mode (e.g. MULTI_USER)
- State (online, offline)
- Recovery model (e.g. FULL)
- Collation sequence (e.g. SQL_Latin1_CP1_CI_AS)
- Physical file list for the database
Predefined Microsoft SQL Server monitoring templates
The plugin provides predefined templates for most commonly monitored SQL Server items (listed in the table below). Other, user-defined sensors and performance counters can be added.
|Microsoft SQL Server monitoring templates|
|SQL Server Server.User Connections|
|SQL Server Server.Free Space in tempdb (KB)|
|SQL Server Server.Transactions|
|SQL Server SQL Plans.Cache Hit Ratio|
|SQL Server SQL Plans.Cache Object Counts|
|SQL Server Database.Average Wait Time (ms)|
|SQL Server Database.Lock Wait Time (ms)|
|SQL Server Database.Number of Deadlocks/sec|
|SQL Server File.Average Wait Time (ms)|
|SQL Server File.Lock Wait Time (ms)|
|SQL Server File.Number of Deadlocks/sec|
|SQL Server Key.Average Wait Time (ms)|
|SQL Server Key.Lock Wait Time (ms)|
|SQL Server Key.Number of Deadlocks/sec|
|SQL Server Metadata.Average Wait Time (ms)|
|SQL Server Metadata.Lock Wait Time (ms)|
|SQL Server Metadata.Number of Deadlocks/sec|
|SQL Server Object.Average Wait Time (ms)|
|SQL Server Object.Lock Wait Time (ms)|
|SQL Server Object.Number of Deadlocks/sec|
|SQL Server Server.Total Server Memory (KB)|
|SQL Server Server.Buffer cache hit ratio|
|SQL Server Server.Page reads/sec|
|SQL Server Server.Page writes/sec|
|SQL Server Server.Logical Connections|