Monitoring and Alerting For Long Running Queries (MSSQL)

[worx1986]

Hello!

I'm attempting to monitor and alert for MSSQL queries that take longer than 10 seconds to complete, but I'm having some problems getting this accomplished.

Would it be possible to take the output of the below query and somehow pipe it into Nagios? Please let me know if more information is needed, and thank you for your help ahead of time!

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
Where req.total_elapsed_time > 10000000

[dwasswa]

Hi @worx1986,

There is a plugin in

Code: Select all

/usr/local/nagios/libexec

called

Code: Select all

check_mssql

.

Plugin usage:

Code: Select all

This plugin checks various aspect of an MSSQL server. It will also execute queries or stored procedures and return results based on query execution times and expected query results.

Options:
-h, --help Print detailed help screen.
-V, --version Print version information.
-H, --hostname Hostname of the MSSQL server.
-U, --username Username to use when logging into the MSSQL server.
-P, --password Password to use when logging into the MSSQL server.
-F, --cfgfile Read parameters from a php file, e. g.
-p, --port Optional MSSQL server port. (Default is 1433).
-I, --instance Optional MSSQL Instance
-d, --database Optional DB name to connect to.
-q, --query Optional query or SQL file to execute on MSSQL server.
-l, --longquery Optional query or SQL file to execute on MSSQL server.
The query is used for multiple line output only.
By default Nagios will only read the first 4 KB.
(MAX_PLUGIN_OUTPUT_LENGTH)
--decode Reads the query -q in urlencoded format. Useful if special characters are in your query.

--decodeonly Decode the query -q
Prints the decoded query string and exits.

--encode Encodes the query -q
Prints urlencoded query and exits.

-s, --storedproc Optional stored procedure to execute on MSSQL server.

-r, --result Expected result from the specified query, requires -q.
The query pulls only the first row for comparison,
so you should limit yourself to small, simple queries.

-w, --warning Warning threshold in seconds on duration of check

-c, --critical Critical threshold in seconds on duration of check

-W, --querywarning Query warning threshold

-C, --querycritical Query critical threshold

Example: check_mssql -H myserver -U myuser -P mypass -q /tmp/query.sql -c 10 -W 2 -C 5
Example: check_mssql -H myserver -U myuser -P mypass -q "SELECT COUNT(*) FROM mytable" -r "632" -c 10 -W 2 -C 5

Please let me know if you have any questions.

[worx1986]

Hello @dwasswa!

We're currently using the check_mssql plugin, however I'm not seeing any way to alert on queries that last longer than 10 seconds?

The closest thing I'm seeing is the MSSQL Query Wizard, that allows you to run an on demand query and view statistics, but nothing that constantly monitors MSSQL for long running queries.

[dwasswa]

You can actually do that by setting warning and critical thresholds on the query see below....

Code: Select all

check_mssql -H myserver -U myuser -P mypass -q "SELECT COUNT(*) FROM mytable" -r "632" -c 10 -W 2 -C 5

-w, --warning Warning threshold in seconds on duration of check

-c, --critical Critical threshold in seconds on duration of check

-W, --querywarning Query warning threshold

-C, --querycritical Query critical threshold

[mcapra]

This technet article might be useful:
https://social.msdn.microsoft.com/Forum ... ransactsql

Looks like you'd have to to it per request:

Code: Select all

SELECT
	r.session_id
,	r.start_time
,	TotalElapsedTime_ms = r.total_elapsed_time
,	r.[status]
,	r.command
,	DatabaseName = DB_Name(r.database_id)
,	r.wait_type
,	r.last_wait_type
,	r.wait_resource
,	r.cpu_time
,	r.reads
,	r.writes
,	r.logical_reads
,	t.[text] AS [executing batch]
,	SUBSTRING(
				t.[text], r.statement_start_offset / 2, 
				(	CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text]) 
						 ELSE r.statement_end_offset 
					END - r.statement_start_offset ) / 2 
			 ) AS [executing statement] 
,	p.query_plan
FROM
	sys.dm_exec_requests r
CROSS APPLY
	sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY	
	sys.dm_exec_query_plan(r.plan_handle) AS p
ORDER BY 
	r.total_elapsed_time DESC;

Sort of messy and I don't know of any plugins with that query baked-in, but you could probably strip down the query itself (isolate TotalElapsedTime_ms and use that for your thresholds) and run it with check_mssql. I don't have a SQL Server instance handy to test this at the moment unfortunately.

[dwasswa]

Thanks @mcapra!

@worx1986 ,let us know if you have any questions.