Monitoring MSSQL Metrics with DB Query Sensors

The user must have enough permissions to access these metrics.

For details on user Permissions and DB Configuration Click here

Using DB Query Sensors to Retrieve MSSQL Metrics

Private Sensor Name: Total Connection
SQL: select sum(cntr_value), '~' AS Applicare_Internal from sys.dm_os_performance_counters where counter_name in ('User Connections','Logical Connections');

Private Sensor Name: User Connections
SQL: select cntr_value,'~' AS Applicare_Internal from sys.dm_os_performance_counters where counter_name='User Connections';

Private Sensor Name: Logical Connections
SQL: select cntr_value, '~' AS Applicare_Internal from sys.dm_os_performance_counters where counter_name='Logical Connections';

Private Sensor Name: Active Temp Tables
SQL: select cntr_value, '~' AS Applicare_Internal from sys.dm_os_performance_counters where counter_name='Active Temp Tables';

Private Sensor Name: Lock Blocks Allocated
SQL: select cntr_value, '~' AS Applicare_Internal from sys.dm_os_performance_counters where counter_name='Lock Blocks Allocated';

Private Sensor Name: Lock Owner Blocks Allocated
SQL: select cntr_value, '~' AS Applicare_Internal from sys.dm_os_performance_counters where counter_name='Lock Owner Blocks Allocated';

Private Sensor Name: Connection Memory
SQL: select cntr_value, '~' AS Applicare_Internal from sys.dm_os_performance_counters where counter_name='Connection Memory (KB)';

Private Sensor Name: Process blocked
SQL: select cntr_value, '~' AS Applicare_Internal from sys.dm_os_performance_counters where counter_name='Processes blocked';

Private Sensor Name: Transactions
SQL: select cntr_value, '~' AS Applicare_Internal from sys.dm_os_performance_counters where counter_name='Transactions' and object_name like '%Transactions%';

Private Sensor Name: Active Transactions
SQL: select sum(cntr_value), '~' AS Applicare_Internal from sys.dm_os_performance_counters where counter_name='Active Transactions';

Private Sensor Name: Total Database file size (KB)
SQL: select sum(cntr_value), '~' AS Applicare_Internal from sys.dm_os_performance_counters where counter_name='Data File(s) Size (KB)';

Private Sensor Name: Total Database Log file size (KB)
SQL: select sum(cntr_value), '~' AS Applicare_Internal from sys.dm_os_performance_counters where counter_name='Log File(s) Size (KB)';

Private Sensor Name: Buffer cache hit ratio
SQL: select cntr_value, '~' AS Applicare_Internal from sys.dm_os_performance_counters where counter_name='Buffer cache hit ratio';

Private Sensor Name: Database pages
SQL: select cntr_value, '~' AS Applicare_Internal from sys.dm_os_performance_counters where counter_name='Database pages' and object_name like '%Transactions%';