Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /* SPROC - Return Query Text for queries that have been running greater than a specfied time.sql
- Purpose: Alert for Long Running Queries
- Author: Lars Rasmussen
- Last Edited: 2013-07-10
- Instructions: Use in Job that runs every 2 minutes, Pass milliseconds as Threshold
- References:
- Query Result in HTML format http://coding.smashingmagazine.com/2008/08/13/top-10-css-table-designs/
- CSS prettiness for table output http://coding.smashingmagazine.com/2008/08/13/top-10-css-table-designs/
- Example of Running Stored Proc with 5 min threshold for currently running queries:
- EXEC sp_Check_For_Long_Running_Queries 300000
- */
- CREATE PROCEDURE [dbo].[usp_Check_For_Long_Running_Queries]
- -- Add the parameters for the stored procedure here
- @intMillisecondsDurationExceeded INT = 10000 --(in ms) default value longer than 10 secs
- AS
- BEGIN
- SET NOCOUNT ON;
- --Return Query Text for queries that have been running > specfied time
- DECLARE @RowCount INT = 0,
- @TotalNumLongRunningQueries INT = 0
- SELECT D.text SQLStatement,
- A.Session_ID SPID,
- ISNULL(B.status, A.status) Status,
- DATEDIFF(minute, A.last_request_start_time, getdate()) AS MinutesRun,
- A.login_name Login,
- A.host_name HostName,
- C.BlkBy,
- DB_NAME(B.Database_ID) DBName,
- B.command,
- ISNULL(B.cpu_time, A.cpu_time) CPUTime,
- ISNULL(( B.reads + B.writes ), ( A.reads + A.writes )) DiskIO,
- A.last_request_start_time LastBatch,
- A.program_name
- INTO #TempLongRunningQueriesForAlerts
- FROM sys.dm_exec_sessions A
- LEFT JOIN sys.dm_exec_requests B
- ON A.session_id = B.session_id
- AND B.total_elapsed_time > @intMillisecondsDurationExceeded
- LEFT JOIN (
- SELECT A.request_session_id SPID,
- B.blocking_session_id BlkBy
- FROM sys.dm_tran_locks AS A
- INNER JOIN sys.dm_os_waiting_tasks AS B
- ON A.lock_owner_address = B.resource_address
- ) C
- ON A.Session_ID = C.SPID
- CROSS APPLY sys.dm_exec_sql_text(sql_handle) D
- WHERE A.is_user_process = 1
- AND A.Session_ID != @@SPID
- AND ISNULL(B.status, A.status) != 'sleeping'
- AND B.command NOT LIKE 'BACKUP DATABASE%'
- AND B.command NOT LIKE 'RESTORE HEADER%'
- AND B.command NOT LIKE 'UPDATE STATISTIC'
- --AND D.text NOT LIKE 'ALTER INDEX%'
- AND A.program_name NOT LIKE 'DatabaseMail - DatabaseMail - Id%';
- SELECT @TotalNumLongRunningQueries = COUNT (*) FROM #TempLongRunningQueriesForAlerts;
- SELECT SQLStatement,
- SPID,
- Status,
- MinutesRun,
- Login,
- HostName,
- BlkBy,
- DBName,
- command,
- CPUTime,
- DiskIO,
- LastBatch,
- program_name FROM #TempLongRunningQueriesForAlerts;
- SELECT @RowCount = COUNT(1) FROM #TempLongRunningQueriesForAlerts
- IF @RowCount > 0
- BEGIN
- PRINT 'Doing stuff...'
- DECLARE @HTMLBody VARCHAR(MAX),
- @TableHead VARCHAR(MAX),
- @TableStyle VARCHAR(MAX),
- @TableTail VARCHAR(MAX)
- SET @TableTail = '</table>';
- SELECT @TableHead = '<html><head>' + '<style>'
- --+ 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
- +'#hor-minimalist-b{font-family:"Lucida Sans Unicode", "Lucida Grande", Sans-Serif;font-size:11px;background:#fff;width:90%;border-collapse:collapse;text-align:left;}#hor-minimalist-b th{font-size:14px;font-weight:normal;color:#039;border-bottom:2px solid #6678b1;padding:10px 8px;}#hor-minimalist-b td{border-bottom:1px solid #ccc;color:#669;padding:4px 6px;vertical-align: top;}#hor-minimalist-b tbody tr:hover td{color:#009;}'
- --+'#hor-minimalist-b{font-family:"Lucida Sans Unicode", "Lucida Grande", Sans-Serif;font-size:12px;background:#fff;width:90%;border-collapse:collapse;text-align:left;margin:10px;}#hor-minimalist-b th{font-size:14px;font-weight:normal;color:#039;border-bottom:2px solid #6678b1;padding:10px 8px;}#hor-minimalist-b td{border-bottom:1px solid #ccc;color:#669;padding:6px 8px;vertical-align: top;}#hor-minimalist-b tbody tr:hover td{color:#009;}'
- --+ '</style>' + '</head>' + '<body><table id="hor-minimalist-b">'
- + '</style>' + '</head>' + '<body style="font-family:Consolas,Monaco,Lucida Console,Liberation Mono,DejaVu Sans Mono,Bitstream Vera Sans Mono,Courier New, monospace;font-size:12px;background:#fff">'
- + '<table id="hor-minimalist-b">'
- + '<tr bgcolor=#FFEFD8><td align=left>' + LTRIM(RTRIM (CAST(@TotalNumLongRunningQueries AS CHAR(4)) + ' row(s)' )) + '</td>'
- --+ '<tr bgcolor=#FFEFD8><td valign=top>' + LTRIM(RTRIM (CAST(@TotalNumLongRunningQueries AS CHAR(4)) )) + '</td>'
- + '<td align=left>SQLStatement</td>'
- + '<td align=left>SPID</td>'
- + '<td align=center>Status</td>'
- + '<td align=left>MinutesRun</td>'
- + '<td align=center>Login</td>'
- + '<td align=center>HostName</td>'
- + '<td align=left>BlkBy</td>'
- + '<td align=center>DBName</td>'
- + '<td align=center>command</td>'
- + '<td align=left>CPUTime</td>'
- + '<td align=left>DiskIO</td>'
- + '<td align=left>LastBatch</td>'
- + '<td align=center>program_name</td></tr>';
- SELECT @HTMLBody = (
- SELECT ROW_NUMBER() OVER ( ORDER BY LastBatch DESC ) AS [TD],
- ISNULL(SQLStatement, 'NULL') AS [TD],
- SPID AS [TD],
- Status AS [TD],
- MinutesRun AS [TD],
- Login AS [TD],
- HostName AS [TD],
- ISNULL(BlkBy, 0) AS [TD],
- ISNULL(DBName, 'NULL') AS [TD],
- ISNULL(command, 'NULL') AS [TD],
- CPUTime AS [TD],
- DiskIO AS [TD],
- LastBatch AS [TD],
- ISNULL(program_name, 'NULL') AS [TD]
- FROM #TempLongRunningQueriesForAlerts
- ORDER BY LastBatch DESC
- FOR
- XML RAW('tr'),
- ELEMENTS
- )
- DECLARE @server_footer NVARCHAR(2000);
- SELECT @server_footer =
- '<br>' + '<br>' + '<br>' +
- + '<hr style="border: 1px dashed #ccc;" />'
- + 'Server Time: ' + CONVERT(CHAR(19), GETDATE(), 120)
- + '<br>SQL Server instance ' + @@SERVERNAME + ' (' + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' + SUBSTRING(@@VERSION, CHARINDEX('X',@@VERSION),4) + ' - ' + CAST(SERVERPROPERTY('edition') AS VARCHAR)
- + '<hr style="border: 1px dashed #ccc;" />'
- + '</p></body></html>'
- IF @HTMLBody IS NULL
- OR @HTMLBody = ''
- SET @HTMLBody = '<tr><td colspan="4">no data found</td></tr>'
- SELECT @HTMLBody = @TableHead + @HTMLBody + @TableTail + @server_footer;
- --SELECT @HTMLBody = @TableHead + @HTMLBody + @TableTail;
- EXEC msdb.dbo.sp_send_dbmail
- @recipients = '[email protected]',
- --@copy_recipients = '[email protected]',
- @body = @HTMLBody,
- @subject = 'Long Running Query Detected',
- @profile_name = 'YOUR_DBMAIL_PROFILE',
- @body_format = 'html';
- END
- ELSE
- BEGIN
- RETURN(0)
- END
- DROP TABLE #TempLongRunningQueriesForAlerts;
- SET NOCOUNT OFF;
- END
- ;;
- GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
