How to use zabbix to monitor MS SQL database performance

Jephe Wu - http://linuxtechres.blogspot.com

Environment: Zabbix 1.8.X monitoring system, MS SQL server 2005
Objective: to monitor MS SQL server performance by zabbix,


Concept: use sys.dm_os_performance_counters which resides in master and msdb database.

According to http://madebysql.blogspot.com.au/2011_01_01_archive.html, sys.dm_os_performance_counters has 3 kinds of values as follows

1. value/base: (e.g. buffer cache hit ratio)

take counter with cntr_type = 537003264 and divide it by
'base' counter with cntr_type = 1073939712.

for example:
("Buffer cache hit ratio" / "Buffer cache hit ratio base") *100
will give you the 'Buffer cache hit ratio'

2. Point In Time:
These are point-in-time counters.They hold the value at the
current point-in-time.These counters have cntr_type = 65792.

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'User Connections'
              AND object_name Like '%General Statistics%'

3. cumulative values
meaning the
value must be compared at 2 diffrerent times by calculating the
differences between the values.For instance,get a counter value,
save it, then get it again after 5 seconds for example and the right
final counter value will be:
(@SecondCounterValue-@FirstCounterValue)/5 seconds.

These counters have cntr_type = 272696576.

Methods:
1. use zabbix agent to monitor performance counter
You can run 'perfmon' from run command line, then add a performance counter to list all kinds of sql server counters.

SQL: Number Users Connected         perf_counter["\SQLServer:General Statistics\User Connections"]   
SQL: Lock Waits per second         perf_counter["\SQLServer:Locks(_Total)\Lock Waits/sec"]   
SQL: Number of Deadlocks per second    perf_counter["\SQLServer:Locks(_Total)\Number of Deadlocks/sec"]
SQL: Total Server Memory         perf_counter["\SQLServer:Memory Manager\Total Server Memory (KB)"]

For sql server services monitoring:
use zabbix agent and key 'service_state[MSSQLSERVER]' to monitor , show value is 'Windows service state', type of information is 'Numeric(unsigned)', data type is DEcimal.
Same for service_state[SQLServerAgent] and service_state[SQLBrowser]

that is service_state[<service_name>].

2. use Dbforbix to monitor performance counter
You need to create a sql user 'zabbix' under security inside sql server managament studio(security-logins) and grant sysadmin role to it.
And make sure zabbix is able to login through sql authentication mode. (right click server name in object explorer, properities, security, choose 'SQL server and Windows authentication mode' instead of just Windows authentication mode. Otherwise, zabbix user is not able to login. (Check sql error log if it's unable to login)

You can use the following settings in dbforbix ms sql query configuration:

restoredelay.Query=select restore_delay from msdb.dbo.log_shipping_secondary_databases where secondary_database='WSS_CONTENT';

pagelifeexpectancy.Query=select cntr_value from sys.dm_os_performance_counters where counter_name='Page life expectancy' and instance_name='';
lockwait.Query=select cntr_value from sys.dm_os_performance_counters where counter_name='Lock Waits/sec' and instance_name='_Total';
processblocked.Query=select cntr_value from sys.dm_os_performance_counters where counter_name='Processes blocked';
checkpointpage.Query=select cntr_value from sys.dm_os_performance_counters where counter_name='Checkpoint pages/sec';
pagesplit.Query=select cntr_value from sys.dm_os_performance_counters where counter_name='Page Split/sec';
userconnections.Query=select cntr_value from sys.dm_os_performance_counters where counter_name='User Connections';
batchrequest.Query=select cntr_value from sys.dm_os_performance_counters where counter_name='Batch Requests/sec';
sqlcompilation.Query=select cntr_value from sys.dm_os_performance_counters where counter_name='SQL Compilations/sec';
sqlrecompilation.Query=select cntr_value from sys.dm_os_performance_counters where counter_name='SQL Re-Compilations/sec';

note: table sys.dm_os_performance_counters is in master/msdb system views.

References:

1. http://www.databasejournal.com/features/mssql/article.php/3932406/Top-10-SQL-Server-Counters-for-Monitoring-SQL-Server-Performance.htm 

2. http://www.smartmarmot.com/wiki/index.php/Dbforbix#Steps_for_Installation_on_Microsoft_SQL_Server

3. http://madebysql.blogspot.com.au/2011_01_01_archive.html - How to use performance counters in sys.dm_os_performance_counters DMV

4. http://www.smartmarmot.com/product/dbforbix/ and http://www.smartmarmot.com/wiki/index.php/Main_Page