tallpeak · November 16, 2017 21:43
diff --git a/LongestRunningQueries.vbs b/LongestRunningQueries.vbs
 '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
 srvname = "." ' Feel free to place your most used servernname here
 If WScript.Arguments.count > 0 Then 
 	srvname = WScript.Arguments(0)
 Else
 	srvname = InputBox ( "Enter the server Name", "Server", srvname, 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"

 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")
 Set f = fso.CreateTextFile("c:\temp\longestrunningqueries.sql", True, True)
 f.Write sql
 f.Close

 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)
    on error resume next
 	f.Write rs(cols-2)
    on error goto 0
    f.Close
    pg = pg & "<td><a href=""" & filename & """>SQL</a>"
    filename = "topplan" & i & ".sqlplan"
    Set f = fso.CreateTextFile(temp & filename, True, True)
    on error resume next
    f.Write rs(cols-1)
    on error goto 0
    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
	'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
	srvname = "." ' Feel free to place your most used servernname here
	If WScript.Arguments.count > 0 Then
	srvname = WScript.Arguments(0)
	Else
	srvname = InputBox ( "Enter the server Name", "Server", srvname, 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"

	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")
	Set f = fso.CreateTextFile("c:\temp\longestrunningqueries.sql", True, True)
	f.Write sql
	f.Close

	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)
	on error resume next
	f.Write rs(cols-2)
	on error goto 0
	f.Close
	pg = pg & "<td><a href=""" & filename & """>SQL</a>"
	filename = "topplan" & i & ".sqlplan"
	Set f = fso.CreateTextFile(temp & filename, True, True)
	on error resume next
	f.Write rs(cols-1)
	on error goto 0
	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