10,388 Metrics install automatically if you have Redgate Monitor installed.
If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.
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
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
The T-SQL query that will collect data
Instances to collect from
Select all
Databases to collect from
master
Collection frequency
300
Use collected or calculated values
Leave the Use a calculated rate of change between collections check box unchecked
Metric collection
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 threshholds
Default threshold values
| 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
1 collection
Alert is
Enabled
