{"name":"Number of queries running","description":"<p>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:<\/p>\r\n<pre class=\"sql\"> \r\nSELECT 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\nr.[status], r.command, DB_NAME(r.database_id) AS [DatabaseName]\r\nFROM sys.dm_exec_requests AS r WITH (NOLOCK)\r\nINNER JOIN sys.dm_exec_connections AS c WITH (NOLOCK)\r\nON r.session_id = c.session_id\r\nINNER JOIN sys.dm_exec_sessions AS s WITH (NOLOCK)\r\nON s.session_id = r.session_id\r\nCROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st\r\nWHERE s.is_user_process = 1\r\nAND r.session_id <> @@SPID\r\n<pre>","tsql":"With CurrentlyRunningQueries\r\nAS (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\nr.[status], r.command, DB_NAME(r.database_id) AS [DatabaseName]\r\nFROM sys.dm_exec_requests AS r WITH (NOLOCK)\r\nINNER JOIN sys.dm_exec_connections AS c WITH (NOLOCK)\r\nON r.session_id = c.session_id\r\nINNER JOIN sys.dm_exec_sessions AS s WITH (NOLOCK)\r\nON s.session_id = r.session_id\r\nCROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st\r\nWHERE s.is_user_process = 1\r\nAND r.session_id <> @@SPID)\r\nSELECT count(*) from CurrentlyRunningQueries","instances":true,"frequency":300,"databases":{"mode":1},"rateofchange":false,"metricenabled":true,"alertname":"High number of queries running","alertdescription":"<p>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:<\/p>\r\n<pre class=\"sql\"> \r\nSELECT 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\nr.[status], r.command, DB_NAME(r.database_id) AS [DatabaseName]\r\nFROM sys.dm_exec_requests AS r WITH (NOLOCK)\r\nINNER JOIN sys.dm_exec_connections AS c WITH (NOLOCK)\r\nON r.session_id = c.session_id\r\nINNER JOIN sys.dm_exec_sessions AS s WITH (NOLOCK)\r\nON s.session_id = r.session_id\r\nCROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st\r\nWHERE s.is_user_process = 1\r\nAND r.session_id <> @@SPID\r\n<pre>","aboveorbelow":"above","collections":1,"alertenabled":true,"_thresholds_high":{"selected":false},"_thresholds_medium":{"selected":false},"_thresholds_low":{"selected":false},"thresholds_comment":"Note: You'll need to enable each threshold and add values based on the typical number of queries running in your environment.","targetentitytype":0}