MS SQL
Overview
MS SQL Server, the powerful and scalable relational database management system developed by Microsoft, seamlessly integrates with Motadata AIOps to provide comprehensive monitoring capabilities. With this integration, organizations gain real-time visibility into the performance and health of their MS SQL Server databases. Monitor critical database metrics such as query execution times, transaction rates, and database size to ensure efficient data processing and retrieval.
Supported Versions
| Versions |
|---|
| 16 |
Prerequisites for MS SQL Integration with Motadata AIOps:
Ensure that the MS SQL port (default: 14333) is open for the Motadata AIOps server.
Ensure you have the necessary credentials, including the username and password, for Motadata AIOps to connect to the MS SQL database.
Ensure that JDBC is supported on the server where MS SQL Database is configured.
For agentless monitoring, ensure that the user has the required access for remote access to the MS SQL server. For agent-based monitoring, this is not required.
Ensure that the MS SQL service is active and running on the server.
Ensure you have the name of the MS SQL database that you want to monitor.
Confirm that the MS SQL process and service are listed in the process and service monitor settings of Motadata AIOps. While these may be listed by default, verify that the names of the service and process match the specific MS SQL version that you intend to monitor.
By following these prerequisites, you can integrate MS SQL with Motadata AIOps and ensure smooth functioning of the monitoring process.
List of Supported KPIs
| Name | Description | Type |
|---|---|---|
| system.tags | Tags associated with the system | String |
| mssql.version | Version of Microsoft SQL Server being used | String |
| mssql.cache.hit.ratio.percent | Percentage of cache hits for SQL Server | Count |
| mssql.buffer.cache.hit.ratio.percent | Percentage of buffer cache hits for SQL Server | Count |
| mssql.lazy.writes.per.sec | Number of lazy writes per second | Count |
| mssql.free.list.stalls.per.sec | Number of free list stalls per second | Count |
| mssql.active.transactions | Number of active transactions | Count |
| mssql.transactions.per.sec | Number of transactions per second | Count |
| mssql.log.growths | Number of log growths | Count |
| mssql.cache.object.counts | Number of objects in the cache | Count |
| mssql.cache.used.objects | Number of used objects in the cache | Count |
| mssql.cache.pages | Number of cache pages | Count |
| mssql.lock.requests.per.sec | Number of lock requests per second | Count |
| mssql.lock.waits.per.sec | Number of lock waits per second | Count |
| mssql.latch.waits.per.sec | Number of latch waits per second | Count |
| mssql.lock.timeouts.per.sec | Number of lock timeouts per second | Count |
| mssql.average.latch.wait.time.ms | Average latch wait time in milliseconds | Count |
| mssql.average.wait.time.ms | Average wait time in milliseconds | Count |
| mssql.deadlocks.per.sec | Number of deadlocks occurring per second | Count |
| mssql.sql.compilations.per.sec | Number of SQL compilations per second | Count |
| mssql.sql.recompilations.per.sec | Number of SQL recompilations per second | Count |
| mssql.table.lock.escalations.per.sec | Number of table lock escalations per second | Count |
| mssql.full.scans.per.sec | Number of full scans performed per second | Count |
| mssql.batch.requests.per.sec | Number of batch requests per second | Count |
| mssql.probe.scans.per.sec | Number of probe scans performed per second | Count |
| mssql.range.scans.per.sec | Number of range scans performed per second | Count |
| mssql.auto.param.attempts.per.sec | Number of automatic parameter attempts per second | Count |
| mssql.workfile.creates.per.sec | Number of workfile creations per second | Count |
| mssql.worktable.creates.per.sec | Number of worktable creations per second | Count |
| mssql.page.splits.per.sec | Number of page splits per second | Count |
| mssql.failed.auto.params.per.sec | Number of failed automatic parameter attempts per second | Count |
| mssql.user.connections | Number of active user connections | Count |
| mssql.blocked.processes | Number of currently blocked processes | Count |
| mssql.logins.per.sec | Number of logins per second | Count |
| mssql.logouts.per.sec | Number of logouts per second | Count |
| mssql.page.reads.per.sec | Number of page reads per second | Count |
| mssql.page.writes.per.sec | Number of page writes per second | Count |
| mssql.page.lookups.per.sec | Number of page lookups per second | Count |
| mssql.checkpoint.pages.per.sec | Number of checkpoint pages per second | Count |
| mssql.page.life.expectancy | Average time in seconds a page is expected to stay in memory | Count |
| mssql.database.pages | Total number of pages in the database | Count |
| mssql.server.memory.provisioned.bytes | Total amount of memory provisioned for the server | Count |
| mssql.target.server.memory.bytes | Target amount of memory for the server | Count |
| mssql.sql.cache.memory.bytes | Amount of memory used by the SQL cache | Count |
| mssql.lock.memory.bytes | Amount of memory used by locks | Count |
| mssql.optimizer.memory.bytes | Amount of memory used by the optimizer | Count |
| mssql.connection.memory.bytes | Amount of memory used by connections | Count |
| mssql.memory.pending.grants | Number of pending memory grants | Count |
| mssql.granted.workspace.memory.bytes | Amount of granted workspace memory | Count |
| mssql.memory.outstanding.grants | Number of outstanding memory grants | Count |
| mssql.stolen.pages | Number of stolen pages | Count |
| mssql.free.pages | Number of free pages | Count |
| mssql.pages | Total number of pages | Count |
| mssql.errors.per.sec | Number of errors per second | Count |
| mssql.received.packets.rate | Number of received packets per second (Random metric) | Count |
| mssql.sent.packets.rate | Number of sent packets per second | Count |
| mssql.error.packets | Number of error packets | Count |
| started.time | Uptime of the system | String |
| started.time.sec | Uptime of the system in seconds | Count |
| mssql.cpu.busy.seconds | Total time the CPU was busy in seconds | Count |
| mssql.idle.seconds | Total time the CPU was idle in seconds | Count |
| mssql.reads.rate | Number of database reads per second | Count |
| mssql.writes.rate | Number of database writes per second | Count |
| mssql.errors | Total number of errors | Count |
| mssql.connections | Total number of database connections | Count |
| mssql.io.busy.seconds | Total time the I/O system was busy in seconds | Count |
| mssql.query | SQL query executed | String |
| mssql.query.creation.time | Timestamp when the query was created | String |
| mssql.query.last.execution.time | Timestamp of the last execution of the query | String |
| mssql.query.physical.reads | Number of physical reads performed by the query | Count |
| mssql.query.logical.reads | Number of logical reads performed by the query | Count |
| mssql.query.logical.writes | Number of logical writes performed by the query | Count |
| mssql.query.execution.count | Total count of times the query has been executed | Count |
| mssql.query.cpu.time.sec | Total CPU time consumed by the query in seconds | Count |
| mssql.query.elapsed.time.sec | Total elapsed time for query execution in seconds | Count |
| mssql.query.avg.elapsed.time.sec | Average elapsed time for query execution in seconds | Count |
| correlation.metrics | Count of correlation metrics | Count |
| mssql.active.sessions | Count of active MSSQL sessions | Count |
| mssql.idle.sessions | Count of idle MSSQL sessions | Count |
| mssql.connected.sessions | Count of connected MSSQL sessions | Count |
| mssql.blocked.sessions | Count of blocked MSSQL sessions | Count |
| mssql.blocked.session | Count of blocked MSSQL session (randomized value) | Count |
| mssql.session | Count of MSSQL sessions | Count |
| mssql.waiting.session | Count of waiting MSSQL sessions | Count |
| mssql.session.id | Unique identifier of an MSSQL session | Count |
| mssql.session.used.memory.bytes | Amount of memory used by an MSSQL session in bytes | Count |
| mssql.session.cpu.time.ms | CPU time consumed by an MSSQL session in milliseconds | Count |
| mssql.session.failed.logons | Count of failed logon attempts for an MSSQL session | Count |
| mssql.session.reads | Number of reads performed by an MSSQL session | Count |
| mssql.session.writes | Number of writes performed by an MSSQL session | Count |
| mssql.session.status | Status of an MSSQL session | Count |
| mssql.session.duration.sec | Duration of an MSSQL session in seconds | Count |
| mssql.session.duration | Duration of an MSSQL session | Count |
| mssql.session.login.time | Timestamp of the login time for an MSSQL session | Count |
| mssql.session.login.name | Login name associated with an MSSQL session | Count |
| mssql.session.application | Application associated with an MSSQL session | Count |
| mssql.session.remote.client | Remote client connected to an MSSQL session | Count |
| mssql.session.domain | Domain associated with an MSSQL session | Count |
| mssql.session.domain.user | Domain user associated with an MSSQL session | Count |
| mssql.waiting.session.id | Unique identifier of a waiting MSSQL session | Count |
| mssql.waiting.session.remote.client | Remote client connected to a waiting MSSQL session | Count |
| mssql.waiting.session.domain.user | Domain user associated with a waiting MSSQL session | Count |
| mssql.waiting.session.domain | Domain associated with a waiting MSSQL session | Count |
| mssql.waiting.session.program.name | Program name associated with a waiting MSSQL session | Count |
| mssql.waiting.session.login.name | Login name associated with a waiting MSSQL session | Count |
| mssql.waiting.session.wait.duration.ms | Waiting duration in milliseconds for a waiting MSSQL session | Count |
| mssql.waiting.session.wait.type | Type of wait for a waiting MSSQL session | Count |
| mssql.blocked.session.id | Unique identifier of a blocked MSSQL session | Count |
| mssql.blocking.session | Identifier of the blocking session for a blocked MSSQL session | Count |
| mssql.blocked.session.database | Database associated with a blocked MSSQL session | Count |
| mssql.blocked.session.resource.type | Type of resource being blocked by a MSSQL session | Count |
| mssql.blocked.session.resource.mode | Mode of resource being blocked by a MSSQL session | Count |
| mssql.blocked.session.blocked.query | Query causing the blockage for a MSSQL session | Count |
| mssql.blocked.session.blocking.query | Query being executed by a blocking MSSQL session | Count |
| mssql.blocked.session.remote.client | Remote client connected to a blocked MSSQL session | Count |
| mssql.blocked.session.domain.user | Domain user associated with a blocked MSSQL session (randomized value) | Count |
| mssql.blocked.session.domain | Domain associated with a blocked MSSQL session (randomized value) | Count |
| mssql.blocked.session.user | User associated with a blocked MSSQL session | Count |
| mssql.blocking.session.domain.user | Domain user associated with a blocking MSSQL session (randomized value) | Count |
| mssql.blocking.session.domain | Domain associated with a blocking MSSQL session (randomized value) | Count |
| mssql.blocking.session.user | User associated with a blocking MSSQL session | Count |
| mssql.blocked.session.program.name | Program name associated with a blocked MSSQL session | Count |
| mssql.log.shipping.database.type | Type of the MSSQL log shipping database | String |
| mssql.log.shipping.database.name | Name of the MSSQL log shipping database | String |
| mssql.log.shipping.last.backup.duration.seconds | Duration of the last backup for the MSSQL log shipping | Count |
| mssql.log.shipping.last.backedup.file | Last backed-up file for the MSSQL log shipping | String |
| mssql.log.shipping.last.copy.duration.seconds | Duration of the last copy for the MSSQL log shipping | Count |
| mssql.log.shipping.last.copied.file | Last copied file for the MSSQL log shipping | Count |
| mssql.log.shipping.last.restore.duration.seconds | Duration of the last restore for the MSSQL log shipping | Count |
| mssql.log.shipping.last.restored.file | Last restored file for the MSSQL log shipping | Count |
| mssql.alwayson.connected.state | Connected state of the MSSQL AlwaysOn | String |
| mssql.alwayson.role | Role of the MSSQL AlwaysOn | String |
| mssql.alwayson.operational.state | Operational state of the MSSQL AlwaysOn (randomized value) | String |
| mssql.alwayson.availability.mode | Availability mode of the MSSQL AlwaysOn | String |
| mssql.alwayson.failover.mode | Failover mode of the MSSQL AlwaysOn | String |
| mssql.alwayson | MSSQL AlwaysOn | String |
| mssql.alwayson.replica.name | Name of the MSSQL AlwaysOn replica | String |
| mssql.alwayson.name | Name of the MSSQL AlwaysOn | String |
| mssql.alwayson.endpoint.url | Endpoint URL of the MSSQL AlwaysOn | String |
| mssql.cluster.node.name | Name of the MSSQL cluster node | String |
| mssql.cluster.node.state | State of the MSSQL cluster node | String |
| mssql.cluster.node.is.owner | Specifies if the MSSQL cluster node is an owner (Boolean value) | Boolean |
| correlation.metrics | Correlation metrics | String |
| mssql.waiting.processes | Number of MSSQL processes waiting | Count |
| mssql.suspended.processes | Number of MSSQL processes suspended | Count |
| mssql.running.processes | Number of MSSQL processes running | Count |
| mssql.process.wait.time.ms | Wait time in milliseconds for the MSSQL process | Count |
| mssql.process | MSSQL process | Count |
| mssql.process.kernel.id | Kernel ID of the MSSQL process | Count |
| mssql.process.status | Status of the MSSQL process | String |
| mssql.process.hostname | Hostname associated with the MSSQL process | String |
| mssql.process.command | Command executed by the MSSQL process | String |
| mssql.process.io.ops.rate | Rate of I/O operations for the MSSQL process | Count |
| mssql.process.memory.bytes | Memory usage in bytes by the MSSQL process | Count |
| mssql.process.host | Number of hosts running the MSSQL process | Count |
| mssql.process.name | Name of the MSSQL process | String |
| mssql.job | MSSQL job | String |
| mssql.job.server | Server associated with the MSSQL job | String |
| mssql.job.retry.attempts | Number of retry attempts for the MSSQL job | Count |
| mssql.job.current.status.code | Current status code of the MSSQL job | String |
| mssql.job.last.run.status | Last run status of the MSSQL job | String |
| mssql.job.last.run.status.code | Last run status code of the MSSQL job | String |
| mssql.job.current.status | Current status of the MSSQL job | String |
| mssql.job.last.execution.time | Last execution time of the MSSQL job | String |
| mssql.job.next.execution.time | Next execution time of the MSSQL job | String |
| mssql.missing.indices | Number of missing indices in MSSQL | Count |
| correlation.metrics | Correlation metrics | String |
| mssql.indices | Number of indices in MSSQL | Count |
| mssql.unused.indices | Number of unused indices in MSSQL | Count |
| mssql.index | MSSQL index | String |
| mssql.index.database.name | Name of the database for the MSSQL index | String |
| mssql.index.schema.name | Name of the schema for the MSSQL index | String |
| mssql.index.table.name | Name of the table for the MSSQL index | Count |
| mssql.index.user.seeks.rate | User seeks rate for the MSSQL index | Count |
| mssql.index.user.scans.rate | User scans rate for the MSSQL index | Count |
| mssql.index.user.lookups.rate | User lookups rate for the MSSQL index | Count |
| mssql.index.user.updates.rate | User updates rate for the MSSQL index | Count |
| mssql.unused.index | Unused MSSQL index | String |
| mssql.unused.index.database.name | Database name for the unused MSSQL index | String |
| mssql.unused.index.schema.name | Schema name for the unused MSSQL index | String |
| mssql.unused.index.table.name | Table name for the unused MSSQL index | String |
| mssql.unused.index.id | Identifier for the unused MSSQL index | Count |
| mssql.missing.index | Missing MSSQL index | Count |
| mssql.missing.index.group | Group for the missing MSSQL index | Count |
| mssql.missing.index.database.name | Database name for the missing MSSQL index | String |
| mssql.missing.index.table.name | Table name for the missing MSSQL index | String |
| mssql.missing.index.column.id | Identifier for the column of the missing index | Count |
| mssql.missing.index.column.name | Column name for the missing MSSQL index | String |
| mssql.missing.index.column.usage | Usage count for the column of the missing index | Count |
| mssql.missing.index.user.seeks.rate | User seeks rate for the missing MSSQL index | Count |
| mssql.missing.index.user.scans.rate | User scans rate for the missing MSSQL index | Count |
| mssql.missing.index.user.cost | User cost for the missing MSSQL index | Count |
| mssql.missing.index.user.percent | User percentage for the missing MSSQL index | Count |
| mssql.database | MSSQL database | Count |
| mssql.database.active.transactions | Number of active transactions in the MSSQL database | Count |
| mssql.database.status | Status of the MSSQL database | String |
| mssql.database.log.file.path | File path of the log file for the MSSQL database | String |
| mssql.database.log.growths | Number of log file growths for the MSSQL database | Count |
| mssql.database.backup.restore.ops.per.sec | Number of backup and restore operations per second | Count |
| mssql.database.replication.transactions.per.sec | Number of replication transactions per second | Count |
| mssql.database.bulk.copy.ops.per.sec | Number of bulk copy operations per second | Count |
| mssql.database.shrink.data.movement.bytes.per.sec | Number of data movement bytes per second during shrink operations | Count |
| mssql.database.data.file.path | File path of the data file for the MSSQL database | String |
| mssql.database.flush.log.waits.per.sec | Number of log flush waits per second | Count |
| mssql.database.bulk.copy.rows.per.sec | Number of rows copied per second during bulk copy operations | Count |
| mssql.database.log.used.percent | Percentage of log space used in the MSSQL database | Count |
| mssql.database.log.file.used.bytes | Number of bytes used in the log file of the MSSQL database | Count |
| mssql.database.creation.time | Creation time of the MSSQL database | String |
| mssql.database.dbcc.logical.scan.bytes.per.sec | Rate of logical scan bytes per second in the MSSQL database | Count |
| mssql.database.transactions.per.sec | Rate of transactions per second in the MSSQL database | Count |
| mssql.database.tables | Number of tables in the MSSQL database | Count |
| mssql.database.data.file.size.bytes | Size of the data file in bytes for the MSSQL database | Count |
| mssql.database.log.shrinks | Number of log file shrinks in the MSSQL database | Count |
| mssql.database.log.flushed.bytes.per.sec | Rate of flushed log bytes per second in the MSSQL database | Count |
| mssql.database.log.cache.reads.per.sec | Rate of log cache reads per second in the MSSQL database | Count |
| mssql.database.log.cache.hit.ratio.percent | Percentage of log cache hits in the MSSQL database | Count |
| mssql.database.log.file.size.bytes | Size of the log file in bytes for the MSSQL database | Count |
| mssql.database.log.flush.wait.time.ms | Wait time for log flushes in milliseconds in the MSSQL database | Count |
| mssql.database.log.flushes.per.sec | Rate of log flushes per second in the MSSQL database | Count |
| mssql.database.database.size.bytes | Size of the database in bytes for the MSSQL database | Count |
| mssql.database.log.transactions | Number of log transactions in the MSSQL database | Count |
| mssql.backup.server.name | Name of the server where the MSSQL backup was created | String |
| mssql.backup.database.name | Name of the database being backed up in MSSQL | String |
| mssql.backup.start.time | Start time of the MSSQL backup | String |
| mssql.backup.end.time | End time of the MSSQL backup | String |
| mssql.backup.recovery.model | Recovery model used for the MSSQL backup | String |
| mssql.backup.is.damaged | Indicates whether the MSSQL backup is damaged | String |
| mssql.backup.duration | Duration of the MSSQL backup | String |
| mssql.backup.type | Type of the MSSQL backup | String |
| mssql.backup.bytes | Size of the MSSQL backup in bytes | Count |
| mssql.backup.logical.device.name | Logical device name used for the MSSQL backup | String |
| mssql.backup.physical.device.name | Physical device name used for the MSSQL backup | String |
| mssql.backup.set.name | Name of the backup set in MSSQL | String |
| mssql.last.backup.seconds | Time elapsed since the last backup in seconds | Count |
| mssql.backup | Number of MSSQL backups | Count |