IMPORTANT: No additional bug fixes or documentation updates will be released for this version. For the latest information, see the current release documentation.
« MSSQL module MSSQL transaction_log metricset »
Elastic Docs ›Metricbeat Reference [7.1] ›Modules ›MSSQL module

MSSQL performance metricset

edit

MSSQL performance metricset

edit

This functionality is in beta and is subject to change. The design and code is less mature than official GA features and is being provided as-is with no warranties. Beta features are not subject to the support SLA of official GA features.

performance Metricset fetches information from what’s commonly known as Performance Counters in MSSQL.

We fetch the following data:

  • page_splits_per_sec: Number of page splits per second that occur as the result of overflowing index pages.
  • lock_waits_per_sec: Number of lock requests per second that required the caller to wait.
  • user_connections: Total number of user connections
  • transactions: Total number of transactions
  • active_temp_tables: Number of temporary tables/table variables in use.
  • connections_reset_per_sec: Total number of logins started from the connection pool.
  • logins_per_sec: Total number of logins started per second. This does not include pooled connections.
  • logouts_per_sec: Total number of logout operations started per second.
  • recompilations_per_sec: Number of statement recompiles per second. Counts the number of times statement recompiles are triggered. Generally, you want the recompiles to be low.
  • compilations_per_sec: 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.
  • batch_requests_per_sec: Number of Transact-SQL command batches received per second. This statistic is affected by all constraints (such as I/O, number of users, cache size, complexity of requests, and so on). High batch requests mean good throughput.
  • cache_hit.pct: The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high
  • page_life_expectancy.sec: Indicates the number of seconds a page will stay in the buffer pool without references (in seconds).
  • buffer.checkpoint_pages_per_sec: Indicates the number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.
  • buffer.database_pages: Indicates the number of pages in the buffer pool with database content.
  • buffer.target_pages: Ideal number of pages in the buffer pool.

Fields

edit

For a description of each field in the metricset, see the exported fields section.

Here is an example document generated by this metricset:

{
    "@timestamp": "2017-10-12T08:05:34.853Z",
    "agent": {
        "hostname": "host.example.com",
        "name": "host.example.com"
    },
    "event": {
        "dataset": "mssql.performance",
        "duration": 115000,
        "module": "mssql"
    },
    "metricset": {
        "name": "performance"
    },
    "mssql": {
        "performance": {
            "active_temp_tables": 0,
            "batch_requests_per_sec": 24249,
            "buffer": {
                "cache_hit": {
                    "pct": 0.14
                },
                "checkpoint_pages_per_sec": 182,
                "database_pages": 1892,
                "page_life_expectancy": {
                    "sec": 8201
                },
                "target_pages": 3194880
            },
            "compilations_per_sec": 7379,
            "connections_reset_per_sec": 2179,
            "lock_waits_per_sec": 3,
            "logins_per_sec": 7346,
            "logouts_per_sec": 7339,
            "page_splits_per_sec": 45,
            "recompilations_per_sec": 0,
            "transactions": 0,
            "user_connections": 7
        }
    },
    "service": {
        "address": "172.26.0.2",
        "type": "mssql"
    }
}
« MSSQL module MSSQL transaction_log metricset »