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 
