Infrastructure Integration

Configuration

  1. Configure the agent by editing /etc/netsil-dd-agent/conf.d/sqlserver.yamlin the collectors.

Example:

    init_config:
      #
      # By default, we only capture *some* of the metrics available in the
      # `sys.dm_os_performance_counters` table. You can easily add additional
      # metrics by following the custom_metrics structure shown below.
      #
      # In order to connect to SQL Server either enable SQL Authentication and
      # specify a username or password below. If you do not specify a username
      # or password then we will connect using integrated authentication.
      #
      # custom_metrics:

        # This is a basic custom metric. There is not instance associated with
        # this counter.
        #
        # - name: sqlserver.clr.execution
        #   counter_name: CLR Execution

        # This counter has multiple instances associated with it and we're
        # choosing to only fetch the 'Cumulative execution time (ms) per second' instance.
        #
        # - name: sqlserver.exec.in_progress
        #   counter_name: OLEDB calls
        #   instance_name: Cumulative execution time (ms) per second

        # This counter has multiple instances associated with it and we want
        # every instance available. We'll use the special case ALL instance
        # which *requires* a value for "tag_by". In this case, we'll get metrics
        # tagged as "db:mydb1", "db:mydb2".
        #
        # - name: sqlserver.db.commit_table_entries
        #   counter_name: Log Flushes/sec
        #   instance_name: ALL
        #   tag_by: db

        # As well as capturing from the DMV you can also capture from a custom proc
        # Please note this feature will produce a number of custom metrics that might affect your billing
        # To use this feature, specify in your instance the procedure to execute : 
        # stored_procedure: ProcedureToExecute
        #
        # The proc should return this table
        # CREATE TABLE
        # (
        #   [metric] varchar(255) not null,
        #   [type] varchar(50) not null,
        #   [value] float not null,
        #   [tags] varchar(255)
        # )
        # 
        # You can also specify:
        # ignore_missing_database : if DB doesn't exist on the server then don't do the check. Default False
        # proc_only_if : run this SQL before each call to stored_procedure. Only if it returns 1 then call the proc
        # proc_only_if_database : the database to run the proc_only_if SQL in. Optional. 
        #                         Defaults to database attribute
        #
        # The proc_only_if guard condition is useful for HA scenarios where a database can move between servers.

    instances:
       # All '%' characters must be escaped as '%%'.

      - host: HOST,PORT
        # Optional, change the connection method from adodbapi (the default) to
        # odbc (valid connector names are 'odbc' and 'adodbapi')
        # odbc is only available on Windows
        # connector: odbc

        # Optional, if using odbc, use the named driver.  If none is specified,
        # the 'SQL Server' driver will be used
        # driver: SQL Server

        # Optional, if using odbc, configure a connection using a DSN
        # dsn: DSN_NAME
        username: my_username
        password: my_password

        # Optional, timeout in seconds for the connection and each command run
        # command_timeout: 30
        # database: my_database # Optional, defaults to "master"
        tags:
          - optional_tag

      # get metrics from custom proc in MyDB but only if the database is writeable (i.e. it's the master in an availability group)
      # - host: HOST,PORT
      #   database: MyDB
      #   username: my_username
      #   password: my_password
      #   stored_procedure: GetMetrics
      #   proc_only_if: SELECT CASE CONVERT(sysname,DatabasePropertyEx('MyDB','Updateability')) WHEN 'READ_WRITE' THEN 1 ELSE 0 END
      #   proc_only_if_database: master
      #   ignore_missing_database: True
  1. Check and make sure that all yaml files are valid with following command:

    /etc/init.d/netsil-collectors configcheck
    
  2. Restart the Agent using the following command:

    /etc/init.d/netsil-collectors restart
    
  3. Execute the info command to verify that the integration check has passed:

    /etc/init.d/netsil-collectors info
    

Infrastructure Datasources

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