Advanced SQL query monitoring

Monitoring the size of individual databases in the SQL server isn't that straightforward. This article explains how to get the proper information from the system tables and transform it into counters that are collected automatically.

Database Sensors

NetCrunch offers two database sensors:

• Object - used to get a single string value (status) or numerical value (counter)
• Data - used to gather multiple counters in a single query (based on selected instance column)

No database option

This option can be used when the query is not related to a particular database (which is selected during the sensor configuration). When this option is checked, the database must be set in the query definition.

Pre-Requisites

This article will show an example based on Microsoft SQL and "sys.databases", therefore the user account used in the database connection needs proper access rights.

Sensor configuration

The example in this article requires query:data sensor to be added on the machine with MSSQL installed.

1. Open Node settings of the mssql server and add new SQL query data sensor
2. Check 'no database' option
3. Insert the Query below

   SELECT      sys.databases.Name,
      CONVERT(Int,SUM(size)*8/1024) AS [Total disk space]
   FROM        sys.databases
   JOIN        sys.master_files
   ON          sys.databases.database_id=sys.master_files.database_id
   where sys.databases.database_id > 4 
   GROUP BY    sys.databases.name
   ORDER BY    sys.databases.name
   

   Important When returning the counter values, make sure that they are numerical type.
4. Select 'Name' in the instance column
5. Run test by using the flask icon in the top right corner
6. Add collectors and (or) alerts on the Disk Space counters.

Result

'No database' option is especially useful when system databases are called on - for monitoring of internal performance metrics of any SQL database

databasedbmssql