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

Verax NMS: Microsoft SQL Server monitoring
  • 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.

Configuration view

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.

Schema audit

Verax NMS: Microsoft SQL Server management plugin

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.

Activity view

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.

Queries view

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

Databases view

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

See also

Oracle RDBMS management plugin »

IBM DB2 management plugin »

MySQL management plugin »

PostgreSQL management plugin »

Microsoft Exchange monitoring and management plugin »

Microsoft SharePoint management plugin »

Microsoft IIS management plugin »

Microsoft Active Directory management plugin »

.NET Framework management plugin »

Microsoft Windows monitoring and management plugin »