Stackstate-SQL Server Integration

Overview

Connect SQL Server to Stackstate in order to:

  • Visualize your database performance.
  • Correlate the performance of SQL Server with the rest of your applications.
SQL Server check can only be run from a Windows environment

Setup

Configuration

Prepare SQL Server

  1. Make sure that your SQL Server instance supports SQL Server authentication by enabling “SQL Server and Windows Authentication mode” in the server properties. Server Properties -> Security -> SQL Server and Windows Authentication mode

    setup auth

  2. Create a read-only user to connect to your server:

    CREATE LOGIN stackstate WITH PASSWORD = 'YOUR_PASSWORD';
    CREATE USER stackstate FOR LOGIN stackstate;
    GRANT SELECT on sys.dm_os_performance_counters to stackstate;
    GRANT VIEW SERVER STATE to stackstate;
    

Connect the Agent

  1. Configure the Agent to connect to SQL Server Edit the “sqlserver” configuration in the Agent Manager and add this server to instances:

    instances:
      -   host: MY_HOST,MY_PORT
          username: stackstate
          password: YOUR_PASSWORD
    
    Make sure to change the MY_HOST and MY_PORT to your host and port. The default host and port is 127.0.0.1,1433.

  2. Restart the Agent using the Agent Manager (or restart the service)

Validation

Check the info page in the Agent Manager and verify that the integration check has passed. It should display a section similar to the following:

Checks
======

  [...]

  sqlserver
  ---------
      - instance #0 [OK]
      - Collected 8 metrics & 0 events

Data Collected

Metrics

sqlserver.buffer.cache_hit_ratio
(gauge)
The ratio of data pages found and read from the buffer cache over all data page requests.
shown as fraction
sqlserver.buffer.page_life_expectancy
(gauge)
Duration that a page resides in the buffer pool.
shown as second
sqlserver.stats.batch_requests
(gauge)
The number of batch requests per second.
shown as request
sqlserver.stats.sql_compilations
(gauge)
The number of SQL compilations per second.
shown as operation
sqlserver.stats.sql_recompilations
(gauge)
The number of SQL re-compilations per second.
shown as operation
sqlserver.stats.connections
(gauge)
The number of user connections.
shown as connection
sqlserver.stats.lock_waits
(gauge)
The number of times per second that SQL Server is unable to retain a lock right away for a resource.
shown as lock
sqlserver.access.page_splits
(gauge)
The number of page splits per second.
shown as operation
sqlserver.stats.procs_blocked
(gauge)
The number of processes blocked.
shown as process
sqlserver.buffer.checkpoint_pages
(gauge)
The number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.
shown as page