LongestRunningQueries.vbs

,

This SQL 2005-only VBS script will show the longest-running queries on a given server, complete with graphical .sqlplan when clicked. Results go to a web page, viewed from the local machines's temp directory.

Each row of the resulting table has the session ID, the currently running statement of the batch, a link to a text file containing the full text for the SQL batch running (all statements), and a link to a page containing a .sqlplan file which loads the full query plan into SSMS when clicked. I have found it very interesting for analyzing long-running queries.

One problem: sometimes I get a "timeout" error when running this. Perhaps I need to change ADO's connection or query timeouts. I added a Connect Timeout=120, below, in case that helps.

'LongestRunningQueries.vbs
'By Aaron W. West, 7/14/2006
'Idea from:
'http://www.sqlservercentral.com/columnists/rcarlson/scriptedserversnapshot.asp
'Reference: Troubleshooting Performance Problems in SQL Server 2005
'http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
Sub Main()
Const MinimumMilliseconds = 1000
Dim srvname
If WScript.Arguments.count > 0 Then 
	srvname = WScript.Arguments(0)
Else
	srvname = InputBox ( "Enter the server Name", "Server", ".", VbOk)
	If srvname = "" Then
	    MsgBox("Cancelled")
	    Exit Sub
	End If
End If
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim i
' making the connection to your sql server 
' change yourservername to match your server
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

' this is using the trusted connection if you use sql logins
' add username and password, but I would then encrypt this
' using Windows Script Encoder
conn.Open "Provider=SQLOLEDB;Data Source=" & _
  srvname & ";Trusted_Connection=Yes;Initial Catalog=Master;Connect Timeout=120"
        
' The query goes here
sql = "select  " & vbCrLf & _
"    t1.session_id,  " & vbCrLf & _
"    CASE WHEN t2.total_elapsed_time > 86400e3 THEN CAST(CAST(t2.total_elapsed_time/86400e3 AS DEC(5,1)) AS VARCHAR(7))+' days' ELSE SUBSTRING(CONVERT(VARCHAR(27),CONVERT(DATETIME,t2.total_elapsed_time/864e5),121),12,15) END AS elapsed,  " & vbCrLf & _
" --   t1.request_id,  " & vbCrLf & _
"    t1.task_alloc, " & vbCrLf & _
"    t1.task_dealloc, " & vbCrLf & _
" --   t2.sql_handle,  " & vbCrLf & _
" --   t2.statement_start_offset,  " & vbCrLf & _
" --   t2.statement_end_offset,  " & vbCrLf & _
" --   t2.plan_handle," & vbCrLf & _
"	substring(sql.text, statement_start_offset/2, " & vbCrLf & _
"		CASE WHEN statement_end_offset<1 THEN 8000 " & vbCrLf & _
"		  ELSE (statement_end_offset-statement_start_offset)/2 " & vbCrLf & _
"		END) AS runningSqlText," & vbCrLf & _
"	sql.text as FullSqlText," & vbCrLf & _
"	p.query_plan " & vbCrLf & _
"from (Select session_id,  " & vbCrLf & _
"             request_id, " & vbCrLf & _
"             sum(internal_objects_alloc_page_count) as task_alloc, " & vbCrLf & _
"             sum (internal_objects_dealloc_page_count) as task_dealloc  " & vbCrLf & _
"      from sys.dm_db_task_space_usage  " & vbCrLf & _
"      group by session_id, request_id) as t1,  " & vbCrLf & _
"      sys.dm_exec_requests as t2 " & vbCrLf & _
"cross apply sys.dm_exec_sql_text(t2.sql_handle) AS sql " & vbCrLf & _
"cross apply sys.dm_exec_query_plan(t2.plan_handle) AS p " & vbCrLf & _
"where t1.session_id = t2.session_id and  " & vbCrLf & _
"     (t1.request_id = t2.request_id) " & vbCrLf & _
" AND total_elapsed_time > " & MinimumMilliseconds & vbCrLf & _
"order by t1.task_alloc DESC"
rs.Open sql, conn, adOpenStatic, adLockOptimistic
'rs.MoveFirst

pg = "<html><head><title>Top consuming queries</title></head>" & vbCrLf
pg = pg & "<table border=1>"  & vbCrLf
If Not rs.EOF Then
    pg = pg & "<tr>"
    For Each col In rs.Fields
	pg = pg & "<th>" & col.Name & "</th>"
        c = c + 1
    Next
    pg = pg & "</tr>"
Else
    pg = pg & "Query returned no results"
End If
cols = c

dim filename
dim WshShell
set WshShell = WScript.CreateObject("WScript.Shell")
Set WshSysEnv = WshShell.Environment("PROCESS")
temp = WshShell.ExpandEnvironmentStrings(WshSysEnv("TEMP")) & "\"
filename = temp & filename
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")

i = 0
Dim c
Do Until rs.EOF
    i = i + 1
    pg = pg & "<tr>"
    For c = 0 to cols-3
	pg = pg & "<td>" & RTrim(rs(c)) & "</td>"
    Next
    'Output FullSQL and Plan Text to files, provide links to them
    filename = "topplan-sql" & i & ".txt"
    Set f = fso.CreateTextFile(temp & filename, True, True)
    f.Write rs(cols-2)
    f.Close
    pg = pg & "<td><a href=""" & filename & """>SQL</a>"
    filename = "topplan" & i & ".sqlplan"
    Set f = fso.CreateTextFile(temp & filename, True, True)
    f.Write rs(cols-1)
    f.Close
    pg = pg & "<td><a href=""" & filename & """>Plan</a>"
    'We could open them immediately, eg:
    'WshShell.run temp & filename

    rs.MoveNext
    pg = pg & "</tr>"
Loop

pg = pg & "</table>"

filename = temp & "topplans.htm"
Set f = fso.CreateTextFile(filename, True, True)
f.Write pg
f.Close

Dim oIE
SET oIE = CreateObject("InternetExplorer.Application")
oIE.Visible = True
oIE.Navigate(filename)

'Alternate method:
'WshShell.run filename

' cleaning up
rs.Close
conn.Close
Set WshShell = Nothing
Set oIE = Nothing
Set f = Nothing
End Sub

Main

Rate

5 (1)

Share

Share

Rate

5 (1)