Number of queries running

March 13, 2013 by Tim Hidalgo | 0 comments | 1,053 views
Categories: Performance

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.

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