Monitoring MSSQL's Query Respond Time

[bee]

Hi,
Do you know how to monitor the speed of your MSSQL's query from zabbix? For exact case like this:

I have table called masterdata which have 1000 record, and i want to know how good my mssql server respond for query like this:
select * from masterdata

Is there any trick for this?
The goal is i want to make my zabbix display/showing how fast (in msec or sec) the mssql to finished above query.

Thanks,
BEE

[claytronic]

You would have to setup a UserParameter in the zabbix_agentd.conf configuration file to call a script. I liked your idea so I wrote one for my own needs. It outputs the execution time of a query in milliseconds.

Add this line to your zabbix_agentd.conf

UserParameter = Query_Speed_Test1,cscript C:\Zabbix\sqlSpeed

Save the code below and make sure to edit it to your needs.

' File name:	sqlSpeed.vbs
' Description:	Reports the execution time of a sql script in milliseconds
' Date:		9:56 AM 1/3/2008
' Author:		Clayton Kramer
' Email:		[email protected]
' Version:		1.3

Option Explicit

' Declare Variables
Dim DSN, Conn, Query
Dim server, database, username, password
Dim startTime, endTime

' Set Variables
server = "localhost"
database = "master"
username = "sa"
password = ""
Query = "Select count(*) from sysdatabases"

' Database Connect
' Uncomment the first and comment out the second DSN if you want to use SQL authentication
'DSN = "DRIVER={SQL Server};SERVER=" & server & ";" &_
'	"UID=" & username & ";PWD=" & password & ";" &_
'	"DATABASE=" & database
DSN = "DRIVER={SQL Server};SERVER=" & server & ";" &_
	"Trusted_Connection=yes;DATABASE=" & database

Set Conn = CreateObject("ADODB.Connection")
Conn.Open(DSN)

' Execute SQL
startTime = Timer()
Conn.Execute(Query)

' Output the query execution time in milliseconds
Wscript.Echo stopWatch(startTime)

' stopWatch function
Function stopWatch(byVal startTime)
	Dim endTime
	endTime = Timer()
	
	'Account for special cases that cross midnight
	If endTime < startTime Then
		endTime = endTime + 86400
	End If
	
	stopWatch = Int((endTime - startTime) * 1000) mod 1000
End Function

[bee]

Thanks!

Hi claytronic,
Thanks for the trick. I have tried it and work with -in my case- the following note:

I've put the userparameter line such this:
UserParameter = QuerySpeed,cscript "C:\Program Files\ZabbixAgent\SQLSpeed.vbs" //NoLogo

If i dont put "//NoLogo" option, zabbix will return the value like this:
3896:20080104:080546 Run remote command [cmd /C " cscript "C:\Program Files\Zabbix Agent\SQLSpeed.vbs""] Result [120] [Microsoft (R) Window]...
3896:20080104:080546 For key [QuerySpeed] received value [Microsoft (R) Windows Script Host Version 5.6 Copyright (C) Microsoft Corporation 1996-2001. All rights reserved
13]

while with //NoLogo option, it return as what i want
2732:20080104:082409 Run remote command [cmd /C " cscript "C:\Program Files\Zabbix Agent\SQLSpeed.vbs" //NoLogo"] Result [1] [0]...
2732:20080104:082410 Sending back [0]
1156:20080104:082410 Sending back [0.000000]

Thanks,
BEE

[swaterhouse]

Just want to make a comment about this. Keep in mind that the query results will be cached the first time you run the check so every check after that will be faster so its not really a true estimation of respone time. The better checks are like the ones included in MS SQL template for Zabbix. For even more checks and an in depth explanation of each I would suggest you pick up a copy of "Inside MS SQL Server 2005: The Storage Engine" by Kalen Delaney.

If you choose to use this as a check I would suggest you use an actual quesry that your application runs on a regular basis that is one of if not the slowest quesries you have.