{"id":108249,"date":"2026-02-16T14:01:00","date_gmt":"2026-02-16T14:01:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=108249"},"modified":"2026-02-20T08:32:17","modified_gmt":"2026-02-20T08:32:17","slug":"long-running-query-detection-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/long-running-query-detection-in-sql-server\/","title":{"rendered":"How to Find and Monitor Slow\/Long-Running Queries in SQL Server"},"content":{"rendered":"\n<p><a href=\"https:\/\/www.red-gate.com\/products\/redgate-monitor\/\" target=\"_blank\" rel=\"noreferrer noopener\">Monitoring and alerting<\/a> can be overwhelming tasks, especially for those new to the world of managing production data. One common challenge for any data professional is the identification and management of queries that run for longer than they should.<\/p>\n\n\n\n<p><a href=\"https:\/\/www.red-gate.com\/hub\/product-learning\/redgate-monitor\/query-executions-in-redgate-monitor\" target=\"_blank\" rel=\"noreferrer noopener\">Long-running queries<\/a> pose immediate challenges as they can impact availability, performance, and even data security. Unexpected high-duration workloads can contribute to excessive IO, waits, locking, blocking, and other problems that can lead to application downtime.<\/p>\n\n\n\n<p>This article dives into a variety of ways to identify, manage, and alert on long-running database queries for free using nothing more than <a href=\"https:\/\/www.red-gate.com\/simple-talk\/t-sql-coding-basics\/\" target=\"_blank\" rel=\"noreferrer noopener\">T-SQL<\/a> and some creativity!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-the-importance-of-monitoring-slow-sql-server-queries\">The Importance of Monitoring Slow SQL Server Queries<\/h2>\n\n\n\n<p>It is a rite of passage for anyone responsible for a software application to chase down the \u201cit is slow!\u201d complaint. When users are forced to wait for long enough, they become frustrated and are quick to let everybody know that there&#8217;s a problem needing to be resolved as soon as possible.<\/p>\n\n\n\n<p>When application performance problems are the direct result of <a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/writing-an-efficient-query\/\" target=\"_blank\" rel=\"noreferrer noopener\">problematic queries<\/a>, it falls to a database professional to identify, diagnose, and fix those queries as efficiently as possible. If an organization doesn&#8217;t have a database professional, a software developer or operations specialist will need to fill that role.<\/p>\n\n\n\n<p>Ideally, knowledge of a painful query would be delivered by an alert or automation, and not by an angry user. It would be beneficial to know when something is becoming slow, before it takes minutes to execute. Similarly, it would be advantageous to identify queries that are consuming excessive server resources before they ultimately crush their database servers.<\/p>\n\n\n\n<p>This foresight is invaluable and provides many big benefits to developers, including:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>The ability to improve applications proactively<br><br><\/li>\n\n\n\n<li>Identifying bugs before they are reported by users<br><br><\/li>\n\n\n\n<li>Finding queries that are becoming less efficient over time<br><br><\/li>\n\n\n\n<li>Performance testing of new code<br><br><\/li>\n\n\n\n<li>Avoiding last-minute scrambling and panic<\/li>\n<\/ul>\n<\/div>\n\n\n<p>One of the biggest challenges in this process is that historical tools may not be of help here unless the query in question has both run previously and performed poorly. <a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/t-sql-tuesday-181-query-store-and-its-evolution\/\" target=\"_blank\" rel=\"noreferrer noopener\">Query Store<\/a>, for example, is a powerful tool for identifying performance issues quickly. It captures data in-memory, though, which is not flushed to storage immediately. Fifteen minutes is the default delay for Query Store, which is a good value for the feature, but not adequate for near-real-time reporting. Therefore, if an immediate response is required, we need tools that show what is happening live, right now!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-using-dynamic-management-views-to-detect-long-running-queries-in-sql-server\">Using Dynamic Management Views to Detect Long-Running Queries in SQL Server<\/h2>\n\n\n\n<p>System views are available in all editions of <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/\" target=\"_blank\" rel=\"noreferrer noopener\">SQL Server<\/a> that provide detailed information on what is running, who is running it, and how it is performing. The following is a brief summary of each view:<\/p>\n\n\n\n<p><strong>Sys.dm_exec_sessions<\/strong>: Returns one row per session on a SQL Server or <a href=\"https:\/\/azure.microsoft.com\/en-gb\/products\/azure-sql\/managed-instance\" target=\"_blank\" rel=\"noreferrer noopener\">Azure Managed Instance<\/a>. The <code><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/functions\/session-id-transact-sql?view=aps-pdw-2016-au7\" target=\"_blank\" rel=\"noreferrer noopener\">SESSION_ID<\/a><\/code> (aka: spid) is the primary identifier for a session in SQL Server. If the session is not internal, then quite a bit of useful information is provided, such as the host name, program name, login name, and more.<\/p>\n\n\n\n<p><strong>Sys.dm_exec_requests<\/strong>: Returns one row per request in SQL Server or Azure Managed Instance. The <code>SESSION_ID<\/code> is provided to link it back to a session and its associated login. Information is provided, such as the command type, status, and wait type\/wait time, if waits are occurring. Metrics such as CPU time and IO are also provided. The <a href=\"https:\/\/techcommunity.microsoft.com\/blog\/sqlserver\/2-0-sql-handle-and-plan-handle-explained\/383204\" target=\"_blank\" rel=\"noreferrer noopener\"><code>sql_handle<\/code><\/a><em> <\/em>can be used to link this to query detail.<\/p>\n\n\n\n<p><strong>Sys.dm_exec_connections<\/strong>: Returns a row per connection to this SQL Server or Azure Managed Instance. Included is information such as overall reads\/writes, protocol, client IP address, and port. The <code>SESSION_ID<\/code> links it back to a login session.<\/p>\n\n\n\n<p><strong>Sys.dm_exec_sql_text<\/strong>: When provided with a <code>sql_handle<\/code> or <code>plan_handle<\/code>, this returns the text for a currently executing query.<\/p>\n\n\n\n<p>There are more views available that can provide further details but, for the purposes of research, this is what most operators will want available to begin researching \u201cthe slow thing\u201d.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-view-currently-executing-queries-in-sql-server\">How to View Currently Executing Queries in SQL Server<\/h2>\n\n\n\n<p>With the above views available, let\u2019s combine them into something we can use to gather information on what is running on a server. To limit the output, a filter will be added to only return queries that have been executing for 5 seconds or longer:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n\tdm_exec_sessions.[session_id],\n\tISNULL(dm_exec_requests.total_elapsed_time \/ 1000, 0) AS \nQueryRuntimeSeconds,\n\tDB_NAME(dm_exec_requests.database_id) AS DatabaseName,\n\tISNULL(dm_exec_sql_text.[text], '') AS FullQueryText,\n\tISNULL(dm_exec_sessions.[program_name], '') AS ProgramName,\n\tdm_exec_sessions.login_name AS LoginName,\n\tISNULL(dm_exec_sessions.[host_name], '') AS HostName,\n\tISNULL(dm_exec_connections.client_net_address, '') AS IPAddress,\n\tdm_exec_requests.command AS CommandType\nFROM sys.dm_exec_sessions WITH (NOLOCK)\nINNER JOIN sys.dm_exec_requests WITH (NOLOCK)\nON dm_exec_requests.session_id = dm_exec_sessions.session_id\nLEFT JOIN sys.dm_exec_connections WITH (NOLOCK)\nON dm_exec_connections.connection_id = dm_exec_requests.connection_id\nCROSS APPLY sys.dm_exec_sql_text(dm_exec_requests.sql_handle)\nWHERE dm_exec_requests.total_elapsed_time \/ 1000 &gt;= 5 -- Seconds\n\nORDER BY dm_exec_requests.total_elapsed_time ASC;<\/pre><\/div>\n\n\n\n<p>This is kept intentionally simple. More views, columns, and metrics can be easily added later, when needed. Before executing this, I am going to run an intentionally bad-written query. Once my local server has been beaten up for a bit, the diagnostic query from above returns the following row of information:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"939\" height=\"59\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-5.png\" alt=\"An image showing the information returned from the diagnostic query.\" class=\"wp-image-108250\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-5.png 939w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-5-300x19.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-5-768x48.png 768w\" sizes=\"auto, (max-width: 939px) 100vw, 939px\" \/><\/figure>\n\n\n\n<p>This is enough to indicate that the problem query originates from <a href=\"https:\/\/www.red-gate.com\/simple-talk\/opinion\/editorials\/sql-server-management-studio-is-as-relevant-as-ever\/\" target=\"_blank\" rel=\"noreferrer noopener\">SQL Server Management Studio<\/a> and is being run by the login <em>epollack<\/em> (that\u2019s me!). It is a <code><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/the-basic-t-sql-select-statement\/\" target=\"_blank\" rel=\"noreferrer noopener\">SELECT<\/a><\/code> query against <em>WideWorldImporters<\/em> and the SQL text, when copy\/pasted, is a total disaster:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n\t*\nFROM Sales.Invoices\nLEFT JOIN Sales.InvoiceLines\nON InvoiceLines.InvoiceID = Invoices.InvoiceID\nLEFT JOIN Warehouse.StockItems\nON StockItems.StockItemID = InvoiceLines.StockItemID\nWHERE (\tInvoices.DeliveryInstructions LIKE '%Shop 258%'\n        OR ( LEN(Invoices.CustomerPurchaseOrderNumber) = 5\n             AND LEN(Invoices.CustomerPurchaseOrderNumber) IS NOT NULL\n             AND Invoices.IsCreditNote = 0\n             AND Invoices.InvoiceDate &gt;= '1\/1\/2014')\n        OR ( JSON_VALUE(Invoices.ReturnedDeliveryData, '$.Events[1].DriverID')\n = 7\n              AND Invoices.ReturnedDeliveryData IS NOT NULL))\n        OR ( InvoiceLines.[Description] LIKE '%Developer joke mug%'\n             AND StockItems.StockItemName LIKE '%DBA joke mug - mind if I join \nyou%');\n<\/pre><\/div>\n\n\n\n<p>Yuck! With this level of knowledge, we can inform the correct teams\/people about the bad query or, if needed, we can take direct actions to resolve it. If the query is disruptive and needs to be stopped immediately, the session can be killed:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">KILL 73;<\/pre><\/div>\n\n\n\n<p>Use caution when killing sessions as doing so immediately cancels a query and rolls it back. If the query originates from an application user, the result will be an error of some sort. In addition, if a query has modified significant amounts of data before it is killed, the rollback process may also take a long time. <\/p>\n\n\n\n<p>As a general rule of thumb, allow at least as much time for the rollback as the query has run thus far <em>prior<\/em> to being killed. The <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/how-does-accelerated-database-recovery-work\/\" target=\"_blank\" rel=\"noreferrer noopener\">Accelerated Database Recovery<\/a> feature greatly reduces rollback time if enabled on the target database. Restarting SQL Server or taking other drastic actions to \u201cget rid\u201d of a query in the rollback status will only make matters worse. <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/language-elements\/kill-transact-sql\" target=\"_blank\" rel=\"noreferrer noopener\">Click here for more information on the <code>KILL<\/code> command<\/a> and the implications of using it.<\/p>\n\n\n\n<p>This is a great start &#8211; but at the same time, our work has consisted of manual queries that were run after a user complained that things were slow. This will be perfect for some scenarios, but automation can improve things significantly. To make this better, a process can be created that runs periodically and checks if any long-running queries are executing and, if so, it alerts on them.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-automate-long-running-query-detection-for-sql-server\">How to Automate Long-Running Query Detection for SQL Server<\/h2>\n\n\n\n<p>With a query in hand that provides exactly the data that is needed, running it and collecting the results can be automated. A key to this process is to perform it in steps:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>Perform a lightweight check to see if there are any long-running queries.<br><br><\/li>\n\n\n\n<li>Only if there are queries to report on should further action be taken.<br><br><\/li>\n\n\n\n<li>Report on problematic queries, if any exist<\/li>\n<\/ul>\n<\/div>\n\n\n<p>Query text and other string metrics can get big, so collecting them should be avoided until necessary. It is important to obey the #1 rule of all monitoring processes, that they should not create disruptions for the applications they support. Consider it the Hippocratic Oath for Monitoring: <em>First Do No Harm! <\/em>The following query checks the system views for any queries that exceed 5 seconds:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n\tCOUNT(*)\nFROM sys.dm_exec_sessions WITH (NOLOCK)\nINNER JOIN sys.dm_exec_requests WITH (NOLOCK)\nON dm_exec_requests.session_id = dm_exec_sessions.session_id\nCROSS APPLY sys.dm_exec_sql_text(dm_exec_requests.sql_handle)\nWHERE dm_exec_requests.total_elapsed_time \/ 1000 &gt;= 5;\n<\/pre><\/div>\n\n\n\n<p>The view <code>sys.dm_exec_sql_text<\/code> is included solely to filter out any long-running processes that do not include SQL text. Without this, quite a few sleeping sessions will be returned, which are not indicative of a query-induced problem. If this count is zero, then there is nothing to report, otherwise, we can proceed to pull information about the query and use it for alerting purposes. This count can be checked as often as is needed to verify whether long-running queries are running or not.<\/p>\n\n\n\n<p>Once the count for that check is greater than zero, we can gather information about the query so that its source and cause are immediately pinpointed. For this example, only the first and longest-running query will be reported on. This keeps the process lightweight and guards against a flood of blocking that results in tens or hundreds of rows returned. Feel free to adjust the TOP to return more if more are needed:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT TOP 1\n\tdm_exec_sessions.[session_id] AS SessionId,\n\tISNULL(dm_exec_requests.total_elapsed_time \/ 1000 , 0) AS \nQueryRuntimeSeconds,\n\tDB_NAME(dm_exec_requests.database_id) AS TriggeringDatabaseName,\n\tISNULL(dm_exec_sql_text.[text], '') AS FullQueryText,\n\tISNULL(dm_exec_sessions.[program_name], '') AS TriggeringProgramName,\n\tdm_exec_sessions.login_name AS TriggeringLoginName,\n\tISNULL(dm_exec_sessions.[host_name], '') AS TriggeringHostName,\n\tISNULL(dm_exec_connections.client_net_address, '') AS \nTriggeringIPAddress,\n\tdm_exec_requests.command AS TriggeringCommandType\nFROM sys.dm_exec_sessions WITH (NOLOCK)\nINNER JOIN sys.dm_exec_requests WITH (NOLOCK)\nON dm_exec_requests.session_id = dm_exec_sessions.session_id\nLEFT JOIN sys.dm_exec_connections WITH (NOLOCK)\nON dm_exec_connections.connection_id = dm_exec_requests.connection_id\nCROSS APPLY sys.dm_exec_sql_text(dm_exec_requests.sql_handle)\nWHERE dm_exec_requests.total_elapsed_time \/ 1000 &gt;= 5\n\nORDER BY dm_exec_requests.total_elapsed_time ASC;\n<\/pre><\/div>\n\n\n\n<p>This returns a single row with similar information to what was reviewed earlier in this article:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"939\" height=\"47\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-6.png\" alt=\"An image showing the single row containing information similar to what was reviewed earlier in this article.\" class=\"wp-image-108251\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-6.png 939w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-6-300x15.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-6-768x38.png 768w\" sizes=\"auto, (max-width: 939px) 100vw, 939px\" \/><\/figure>\n\n\n\n<p>The next step is to do something with this information. The data can be sent to an external application via text\/email, or stored in a table for use by other apps, such as a <a href=\"https:\/\/www.red-gate.com\/products\/redgate-monitor\/\" target=\"_blank\" rel=\"noreferrer noopener\">monitoring\/alerting tool<\/a>. <\/p>\n\n\n\n<section id=\"my-first-block-block_2270b965c483e012c8aaec836e2d3d97\" class=\"my-first-block alignwide\">\n    <div class=\"bg-brand-600 text-base-white py-5xl px-4xl rounded-sm bg-gradient-to-r from-brand-600 to-brand-500 red\">\n        <div class=\"gap-4xl items-start md:items-center flex flex-col md:flex-row justify-between\">\n            <div class=\"flex-1 col-span-10 lg:col-span-7\">\n                <h3 class=\"mt-0 font-display mb-2 text-display-sm\">Future-proof database monitoring with Redgate Monitor<\/h3>\n                <div class=\"child:last-of-type:mb-0\">\n                                            Multi-platform database observability for your entire estate. Optimize performance, ensure security, and mitigate potential risks with fast deep-dive analysis, intelligent alerting, and AI-powered insights.                                    <\/div>\n            <\/div>\n                                            <a href=\"https:\/\/www.red-gate.com\/products\/redgate-monitor\/\" class=\"btn btn--secondary btn--lg\" aria-label=\"Learn more &amp; try for free: Future-proof database monitoring with Redgate Monitor\">Learn more &amp; try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n<p>The easiest way to do this is to store each of the captured values in a <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/exploring-scalar-solutions-to-complex-data-math\/\" target=\"_blank\" rel=\"noreferrer noopener\">scalar<\/a> variable and create a message body using them. The following modified code accomplishes this:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">DECLARE @SessionId INT;\nDECLARE @QueryRuntimeSeconds INT;\nDECLARE @TriggeringDatabaseName VARCHAR(100);\nDECLARE @FullQueryText VARCHAR(MAX);\nDECLARE @TriggeringProgramName VARCHAR(100);\nDECLARE @TriggeringLoginName VARCHAR(100);\nDECLARE @TriggeringHostName VARCHAR(100);\nDECLARE @TriggeringIPAddress VARCHAR(100);\nDECLARE @TriggeringCommandType VARCHAR(32);\n\nSELECT TOP 1\n\t@SessionId = dm_exec_sessions.[session_id],\n\t@QueryRuntimeSeconds = ISNULL(dm_exec_requests.total_elapsed_time \/ 1000, 0),\n\t@TriggeringDatabaseName = DB_NAME(dm_exec_requests.database_id),\n\t@FullQueryText = ISNULL(dm_exec_sql_text.[text], ''),\n\t@TriggeringProgramName = ISNULL(dm_exec_sessions.[program_name], ''),\n\t@TriggeringLoginName = dm_exec_sessions.login_name,\n\t@TriggeringHostName = ISNULL(dm_exec_sessions.[host_name], ''),\n\t@TriggeringIPAddress = ISNULL(dm_exec_connections.client_net_address, \n''),\n\t@TriggeringCommandType = dm_exec_requests.command\nFROM sys.dm_exec_sessions WITH (NOLOCK)\nINNER JOIN sys.dm_exec_requests WITH (NOLOCK)\nON dm_exec_requests.session_id = dm_exec_sessions.session_id\nLEFT JOIN sys.dm_exec_connections WITH (NOLOCK)\nON dm_exec_connections.connection_id = dm_exec_requests.connection_id\nCROSS APPLY sys.dm_exec_sql_text(dm_exec_requests.sql_handle)\nWHERE dm_exec_requests.total_elapsed_time \/ 1000 &gt;= 5\nORDER BY dm_exec_requests.total_elapsed_time ASC;\n<\/pre><\/div>\n\n\n\n<p>From here, an email can be generated and sent to whomever needs to know about the slow query:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">DECLARE @ServerName VARCHAR(100) = @@SERVERNAME;\nDECLARE @ProfileName VARCHAR(MAX) = 'Default';\nDECLARE @EmailAddressList VARCHAR(MAX) = 'ed@somedomain.com';\nDECLARE @EmailSubject VARCHAR(MAX)= 'Long Running Query Alert on ' + @ServerName;\nDECLARE @EmailBody VARCHAR(MAX);\n\nSELECT @EmailBody = '&lt;html&gt;&lt;body&gt;The following long-running query was identified:&lt;P&gt;\nDatabase Server: ' + ISNULL(@@SERVERNAME, CAST(SERVERPROPERTY('ServerName') AS VARCHAR(MAX))) + '&lt;BR&gt;\nQuery Runtime (Minutes): ' + CAST(@QueryRuntimeSeconds AS VARCHAR(MAX)) + '&lt;BR&gt;\nSession Id: ' + CAST(@SessionId AS VARCHAR(MAX)) + '&lt;BR&gt;\nDatabase Name: ' + @TriggeringDatabaseName + '&lt;BR&gt;\nProgram Name: ' + @TriggeringProgramName + '&lt;BR&gt;\nLogin Name: ' + @TriggeringLoginName + '&lt;BR&gt;\nHost Name: ' + @TriggeringHostName + '&lt;BR&gt;\nIP Address: ' + @TriggeringIPAddress + '&lt;BR&gt;\nCommand Type: ' + @TriggeringCommandType + '&lt;BR&gt;\nFull Query Text: ' + @FullQueryText + '&lt;\/body&gt;&lt;\/html&gt;';\n\nEXEC msdb.dbo.sp_send_dbmail\n\t@profile_name = @ProfileName,\n\t@recipients = @EmailAddressList,\n\t@subject = @EmailSubject,\n        @body_format = 'html',\n        @body = @EmailBody;\n<\/pre><\/div>\n\n\n\n<p>This creates a basic HTML email and sends it out based on the parameters provided using <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/database-mail\/database-mail?view=sql-server-ver17\" target=\"_blank\" rel=\"noreferrer noopener\">Database Mail<\/a>. If you <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sqlclr-practice-creating-better-way-sending-email-sql-server\/\" target=\"_blank\" rel=\"noreferrer noopener\">do not<\/a> use SQL Server Database Mail, any other connected service can be used in its place. The following is what the resulting HTML would look like:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"833\" height=\"755\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-7.png\" alt=\"An image showing what the resulting HTML would look like.\" class=\"wp-image-108252\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-7.png 833w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-7-300x272.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-7-768x696.png 768w\" sizes=\"auto, (max-width: 833px) 100vw, 833px\" \/><\/figure>\n\n\n\n<p>The HTML can be customized to your heart\u2019s content, adding tables, colors, or other styles to make it either prettier or more functional. It can also be plain text, if preferred. The information provided is enough to be able to hunt down the offending query and begin troubleshooting it.<\/p>\n\n\n\n<p>So far, the execution plan for the offending query was omitted as it can potentially get large. Adding it in is easy enough by adding one more variable, join, and assignment to the query from earlier:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">DECLARE @ExecutionPlan VARCHAR(MAX);\n\nSELECT TOP 1\n\t@SessionId = dm_exec_sessions.[session_id],\n\t@QueryRuntimeSeconds = ISNULL(dm_exec_requests.total_elapsed_time \/ 1000, 0),\n\t@TriggeringDatabaseName = DB_NAME(dm_exec_requests.database_id),\n\t@FullQueryText = ISNULL(dm_exec_sql_text.[text], ''),\n\t@TriggeringProgramName = ISNULL(dm_exec_sessions.[program_name], ''),\n\t@TriggeringLoginName = dm_exec_sessions.login_name,\n\t@TriggeringHostName = ISNULL(dm_exec_sessions.[host_name], ''),\n        @TriggeringIPAddress = ISNULL(dm_exec_connections.client_net_address, \n        ''),\n        @TriggeringCommandType = dm_exec_requests.command ,\n\t@ExecutionPlan = dm_exec_query_plan.query_plan\nFROM sys.dm_exec_sessions WITH (NOLOCK)\nINNER JOIN sys.dm_exec_requests WITH (NOLOCK)\nON dm_exec_requests.session_id = dm_exec_sessions.session_id\nLEFT JOIN sys.dm_exec_connections WITH (NOLOCK)\nON dm_exec_connections.connection_id = dm_exec_requests.connection_id\nCROSS APPLY sys.dm_exec_sql_text(dm_exec_requests.sql_handle)\nCROSS APPLY sys.dm_exec_query_plan(dm_exec_requests.plan_handle)  \nWHERE dm_exec_requests.total_elapsed_time \/ 1000 &gt;= 5\nORDER BY dm_exec_requests.total_elapsed_time ASC;\n<\/pre><\/div>\n\n\n\n<p>The execution plan itself is XML, so it will not look very readable as-is:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"939\" height=\"505\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-8.png\" alt=\"An image showing the execution plan in XML - not very readable!\" class=\"wp-image-108253\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-8.png 939w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-8-300x161.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-8-768x413.png 768w\" sizes=\"auto, (max-width: 939px) 100vw, 939px\" \/><\/figure>\n\n\n\n<p>Despite that, the XML text can be stored alongside the other query-related data and viewed when ready, if needed. This sample XML is 972 lines long, so a graphical solution is ideal for analyzing it. Consider the ever-useful (and free) SQL Sentry Plan Explorer for this task:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"941\" height=\"683\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-9.png\" alt=\"An image showing the sample XML in SQL Sentry Plan Explorer.\" class=\"wp-image-108254\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-9.png 941w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-9-300x218.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/01\/image-9-768x557.png 768w\" sizes=\"auto, (max-width: 941px) 100vw, 941px\" \/><\/figure>\n\n\n\n<p>SSMS provides graphical representations for execution plans as well, but this is a tool I always recommend as it adds quite a bit of utility to make plans easier to view. This is especially important when a query (and its plan) become BIG.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-the-best-way-to-monitor-sql-server-query-performance\">The Best Way to Monitor SQL Server Query Performance<\/h2>\n\n\n\n<p>The ultimate solution here is a script that is executed often and checks for the count of queries whose runtime exceeds a given threshold. This may be executed by a SQL Server Agent job, a PowerShell task, or any other automation tool that is readily available.<\/p>\n\n\n\n<p>Here is a simple version of a stored procedure that does exactly this, and nothing more. A barebones process is fast, lightweight, and easily adaptable to your specific database environment and its needs:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \" >USE WideWorldImporters;\nGO\n\nCREATE OR ALTER PROCEDURE dbo.LongRunningQueryManagement\n\t@RunTimeSeconds INT = 5, @ProfileName VARCHAR(MAX), @EmailAddressList VARCHAR(MAX)\nAS\nBEGIN\n\tSET NOCOUNT ON;\n\tDECLARE @ServerName VARCHAR(100) = @@SERVERNAME;\n\tDECLARE @LongRunningQueryCount INT;\n\n\tSELECT\n\t\t@LongRunningQueryCount = COUNT(*)\n\tFROM sys.dm_exec_sessions WITH (NOLOCK)\n\tINNER JOIN sys.dm_exec_requests WITH (NOLOCK)\n\tON dm_exec_requests.session_id = dm_exec_sessions.session_id\n\tCROSS APPLY sys.dm_exec_sql_text(dm_exec_requests.sql_handle)\n\tWHERE dm_exec_requests.total_elapsed_time \/ 1000 &gt;= @RunTimeSeconds;\n\t\t\n\tIF @LongRunningQueryCount &gt; 0\n\tBEGIN\n\t\tDECLARE @SessionId INT;\n\t\tDECLARE @QueryRuntimeSeconds INT;\n\t\tDECLARE @TriggeringDatabaseName VARCHAR(MAX);\n\t\tDECLARE @FullQueryText VARCHAR(MAX);\n\t\tDECLARE @TriggeringProgramName VARCHAR(MAX);\n\t\tDECLARE @TriggeringLoginName VARCHAR(MAX);\n\t\tDECLARE @TriggeringHostName VARCHAR(MAX);\n\t\tDECLARE @TriggeringIPAddress VARCHAR(MAX);\n\t\tDECLARE @TriggeringCommandType VARCHAR(MAX);\n\n\t\tSELECT TOP 1\n\t\t\t@SessionId = dm_exec_sessions.[session_id],\n\t\t\t@QueryRuntimeSeconds = ISNULL(dm_exec_requests.total_elapsed_time \/ 1000, 0),\n\t\t\t@TriggeringDatabaseName = DB_NAME(dm_exec_requests.database_id),\n\t\t\t@FullQueryText = ISNULL(dm_exec_sql_text.[text], ''),\n\t\t\t@TriggeringProgramName = ISNULL(dm_exec_sessions.[program_name], ''),\n\t\t\t@TriggeringLoginName = dm_exec_sessions.login_name,\n\t\t\t@TriggeringHostName = ISNULL(dm_exec_sessions.[host_name], ''),\n\t\t\t@TriggeringIPAddress = ISNULL(dm_exec_connections.client_net_address, ''),\n\t\t\t@TriggeringCommandType = dm_exec_requests.command\n\t\tFROM sys.dm_exec_sessions WITH (NOLOCK)\n\t\tINNER JOIN sys.dm_exec_requests WITH (NOLOCK)\n\t\tON dm_exec_requests.session_id = dm_exec_sessions.session_id\n\t\tLEFT JOIN sys.dm_exec_connections WITH (NOLOCK)\n\t\tON dm_exec_connections.connection_id = dm_exec_requests.connection_id\n\t\tCROSS APPLY sys.dm_exec_sql_text(dm_exec_requests.sql_handle)\n\t\tWHERE dm_exec_requests.total_elapsed_time \/ 1000 &gt;= 5\n\t\tORDER BY dm_exec_requests.total_elapsed_time ASC;\n\n\t\tDECLARE @EmailSubject VARCHAR(MAX)= 'Long Running Query Alert on ' + @ServerName;\n\t\tDECLARE @EmailBody VARCHAR(MAX);\n\n\t\tSELECT @EmailBody = '&lt;html&gt;&lt;body&gt;The following long-running query was identified:&lt;P&gt;\n\t\tDatabase Server: ' + @ServerName + '&lt;BR&gt;\n\t\tQuery Runtime (Minutes): ' + CAST(@QueryRuntimeSeconds AS VARCHAR(MAX)) + '&lt;BR&gt;\n\t\tSession Id: ' + CAST(@SessionId AS VARCHAR(MAX)) + '&lt;BR&gt;\n\t\tDatabase Name: ' + @TriggeringDatabaseName + '&lt;BR&gt;\n\t\tProgram Name: ' + @TriggeringProgramName + '&lt;BR&gt;\n\t\tLogin Name: ' + @TriggeringLoginName + '&lt;BR&gt;\n\t\tHost Name: ' + @TriggeringHostName + '&lt;BR&gt;\n\t\tIP Address: ' + @TriggeringIPAddress + '&lt;BR&gt;\n\t\tCommand Type: ' + @TriggeringCommandType + '&lt;BR&gt;\n\t\tFull Query Text: ' + @FullQueryText + '&lt;\/body&gt;&lt;\/html&gt;';\n\n\t\tEXEC msdb.dbo.sp_send_dbmail\n\t\t\t@profile_name = @ProfileName,\n\t\t\t@recipients = @EmailAddressList,\n\t\t\t@subject = @EmailSubject,\n\t\t\t@body_format = 'html',\n\t\t\t@body = @EmailBody;\n\tEND\nEND\nGO\n-- Sample Execution\nEXEC dbo.LongRunningQueryManagement\n\t@RunTimeSeconds = 5,\n\t@ProfileName = 'DefaultMailProfile',\n\t@EmailAddressList = 'epollack@transfinder.com';\nGO<\/pre><\/div>\n\n\n\n<p>In addition, be sure to adjust details in the code that will differ for each environment, such as the email address to send to, the mail profile, and the latency to alert on.<\/p>\n\n\n\n<p>It is likely that this code would be targeted at a specific application, program, database, or SQL text. Parameters can easily be added to ensure that no noise is returned. In an optimal monitoring\/alerting application, an alert would never be received unless there was a problem that required attention. While perfection can be hard to achieve with that goal, we should strive to meet it the best we can. Too many false positives or extraneous alerts will result in people simply ignoring the system altogether, rendering it ineffective.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-customizing-the-monitoring-process\">Customizing the Monitoring Process<\/h3>\n\n\n\n<p>Any process like this can be customized extensively, with tactics such as:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>Adding more columns to the result set, such as the execution plan or resource consumption.<br><br><\/li>\n\n\n\n<li>Adding filters to remove results that are of no interest, such as backups and other existing jobs\/tasks that are expected to be slow.<br><br><\/li>\n\n\n\n<li>Adjust number of results returned.<br><br><\/li>\n\n\n\n<li>Create permanent tables to store the results for posterity or for further review.<br><br><\/li>\n\n\n\n<li>Automatically kill specific types of queries after a set amount of runtime has elapsed.<br><br><\/li>\n\n\n\n<li>Adjust the definition of a slow query.<br><br><\/li>\n\n\n\n<li>A whole lot more!<\/li>\n<\/ul>\n<\/div>\n\n\n<p>If the attached code does not meet your needs, then adjust it to handle those scenarios. Need more data? Add it! Need to filter? Go for it! There are absolutely no rules here and every database server and application will have its own workload patterns that dictate what you most care about and need to see.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-long-running-queries-are-not-always-the-cause-of-sql-server-performance-problems\">Long-Running Queries are Not Always the Cause of SQL Server Performance Problems<\/h2>\n\n\n\n<p>It is important to remind ourselves that the \u201cBig Bad\u201d query is not always the cause of a database performance problem. Sometimes it is a simple query that spams a server thousands of times a second. Other times it is an operating system issue. Or a resource issue. Or something else.<\/p>\n\n\n\n<p>Therefore, be sure to fully investigate performance challenges and do not complete the detective work the moment that long-running queries have been ruled out as the cause.<\/p>\n\n\n\n<p>I cannot count the number of times in my career that a performance problem was the result of a fast query than ran millions of times per day and evaded the typical searches for high IO, high CPU, or long waits. It\u2019s a big number, that\u2019s all \ud83d\ude0a<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-monitoring-sql-server-query-performance-conclusion-amp-next-steps\">Monitoring SQL Server Query Performance: Conclusion &amp; Next Steps<\/h2>\n\n\n\n<p>The ability to automatically alert on queries that exceed a minimum runtime threshold allows early communication to be made of potential performance problems. All of the tools needed to do this are free and included in any edition of SQL Server.<\/p>\n\n\n\n<p>Using the basic processes and guidance from this article, you can implement your own alerting process and use it to improve SQL query alerting! Customization allows for a wide variety of applications that can detect anything from rogue users to long-running report queries, to developers running destructive queries from SQL Server Management Studio.<\/p>\n\n\n\n<p>Feel free to share this code, and if you come up with any innovative features for it, please let me know!<\/p>\n\n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>Frequently Asked Questions: SQL Server Long-Running Query Detection<\/h2>\n\n                        <h3 class=\"mt-4xl\">1. What is a long-running query in SQL Server?<\/h3>\n            <div class=\"faq-answer\">\n                <p>A long-running query is any SQL Server query whose execution time exceeds an acceptable threshold for your workload, often causing blocking, high CPU usage, or degraded performance.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">2. How can I detect long-running queries in SQL Server?<\/h3>\n            <div class=\"faq-answer\">\n                <p data-start=\"2538\" data-end=\"2711\">You can detect long-running queries by querying dynamic management views such as <code data-start=\"2619\" data-end=\"2641\">sys.dm_exec_requests<\/code> and <code data-start=\"2646\" data-end=\"2668\">sys.dm_exec_sessions<\/code>, which expose real-time execution details.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">3. Which DMV shows currently executing queries in SQL Server?<\/h3>\n            <div class=\"faq-answer\">\n                <p data-start=\"2771\" data-end=\"2923\">The <code data-start=\"2775\" data-end=\"2797\">sys.dm_exec_requests<\/code> DMV returns information about queries currently executing in SQL Server, including start time, wait type, and resource usage.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">4. Does Query Store capture long-running SQL Server queries?<\/h3>\n            <div class=\"faq-answer\">\n                <p data-start=\"2985\" data-end=\"3154\">Query Store captures historical execution data but does not reliably detect or alert on long-running queries in real time, making DMVs more suitable for live monitoring.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">5. Can I automate alerts for long-running SQL Server queries?<\/h3>\n            <div class=\"faq-answer\">\n                <p data-start=\"3228\" data-end=\"3397\">Yes. You can automate detection using scheduled T-SQL scripts, SQL Agent jobs, or SQL Server monitoring tools like <a href=\"https:\/\/www.red-gate.com\/products\/redgate-monitor\/\" target=\"_blank\" rel=\"noopener\">Redgate Monitor<\/a> that trigger alerts when execution thresholds are exceeded.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to detect long-running queries in SQL Server using dynamic management views and T-SQL scripts to monitor performance in real time.&hellip;<\/p>\n","protected":false},"author":329827,"featured_media":105547,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143523,53,143524],"tags":[4168,4150,4151],"coauthors":[101655],"class_list":["post-108249","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-featured","category-sql-server","tag-database","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/108249","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/329827"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=108249"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/108249\/revisions"}],"predecessor-version":[{"id":108504,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/108249\/revisions\/108504"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/105547"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=108249"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=108249"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=108249"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=108249"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}