Number of queries running

| 0 comments | 2,798 views
Categories:

VN:F [1.9.17_1161]
Rating: 4.2/5 (5 votes cast)

This metric counts how many queries are running, and alerts you if the value goes above a defined threshold. If an alert is raised, you can run the query manually against the database server to see what is happening.

Number of queries running, 4.2 out of 5 based on 5 ratings

Metric definition

Metric Name:
Number of queries running
Description:

This metric counts the number of queries running at specific intervals. You can also run this query manually against a database server to see what is happening:

 
SELECT st.[text] AS [Command Text], s.login_time, [host_name], s.cpu_time, s.total_elapsed_time, r.session_id, c.client_net_address,
r.[status], r.command, DB_NAME(r.database_id) AS [DatabaseName]
FROM sys.dm_exec_requests AS r WITH (NOLOCK)
INNER JOIN sys.dm_exec_connections AS c WITH (NOLOCK)
ON r.session_id = c.session_id
INNER JOIN sys.dm_exec_sessions AS s WITH (NOLOCK)
ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE s.is_user_process = 1
AND r.session_id <> @@SPID
			
Enter the T-SQL query that will collect data:
With CurrentlyRunningQueries
AS (SELECT st.[text] AS [Command Text], s.login_time, [host_name], s.cpu_time, s.total_elapsed_time, r.session_id, c.client_net_address,
r.[status], r.command, DB_NAME(r.database_id) AS [DatabaseName]
FROM sys.dm_exec_requests AS r WITH (NOLOCK)
INNER JOIN sys.dm_exec_connections AS c WITH (NOLOCK)
ON r.session_id = c.session_id
INNER JOIN sys.dm_exec_sessions AS s WITH (NOLOCK)
ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE s.is_user_process = 1
AND r.session_id <> @@SPID)
SELECT count(*) from CurrentlyRunningQueries
Select instances to collect from:
Select all
Choose databases to collect from:
Specify databases and type
master
Set collection frequency:
Collect data every 5 minutes
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
Enabled

Alert definition

Alert name:
High number of queries running
Description:

This alert is raised when the number of queries running during metric collection goes above a defined threshold. To see what's happening on your database server, run the following query manually:

 
SELECT st.[text] AS [Command Text], s.login_time, [host_name], s.cpu_time, s.total_elapsed_time, r.session_id, c.client_net_address,
r.[status], r.command, DB_NAME(r.database_id) AS [DatabaseName]
FROM sys.dm_exec_requests AS r WITH (NOLOCK)
INNER JOIN sys.dm_exec_connections AS c WITH (NOLOCK)
ON r.session_id = c.session_id
INNER JOIN sys.dm_exec_sessions AS s WITH (NOLOCK)
ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE s.is_user_process = 1
AND r.session_id <> @@SPID
			
Raise an alert when the metric value goes:
Above the defined thresholds
Default threshold values:
Set the thresholds as follows:
High:According to your environment
Medium:According to your environment
Low:According to your environment
Note: You'll need to enable each threshold and add values based on the typical number of queries running in your environment.
Raise an alert when the threshold is passed for:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 4.2/5 (5 votes cast)