Try this script to see what queries are taking over a second.
To get some real output, you need a long-running query. Here's one
(estimated to take over an hour):
PRINT GETDATE()
select count_big(*)
from sys.objects s1, sys.objects s2, sys.objects s3,
sys.objects s4, sys.objects s5
PRINT GETDATE()
Output is:
session_id elapsed task_alloc task_dealloc runningSqlText FullSqlText
query_plan
51 32847 0 0 select count_big(*) from sys.objects s1, sys.objects s2,
sys.objects s3, sys.objects s4, sys.objects s5 SQL Plan
Clicking on SQL opens the full SQL batch as a .txt file, including the PRINT
statements
Clicking on Plan allows you to see the .sqlplan file in MSSMS
========
Title: Using a VB Script to show long-running queries, complete with query
plans.
Today (July 14th), I found a query running for hours on a development box.
Rather than kill it, I decided to use this opportunity to develop a script
to show long-running queries, so I could see what was going on. (Reference
Roy Carlson's article for the idea.)
This script generates a web page which shows long-running queries with the
currently-executing SQL command, full SQL text, and .sqlplan files. The full
SQL query text and the sqlplan file are output to files in your temp
directory. If you have SQL Management Studio installed on the local
computer, you should be able to open the .sqlplan to see the query plan of
the whole batch for any statement.
'LongestRunning Queries.vbs
'By Aaron W. West, 7/14/2006
'Idea from:
'http://www.sqlserverce ntral.com/columnists/rcarlson/scriptedservers napshot.asp
'Reference: Troubleshooting Performance Problems in SQL Server 2005
'http://www.microsoft.c om/technet/prodtechnol/sql/2005/tsprfprb.mspx
Sub Main()
Const MinimumMillisec onds = 1000
Dim srvname
If WScript.Argumen ts.count 0 Then
srvname = WScript.Argumen ts(0)
Else
srvname = InputBox ( "Enter the server Name", "Server", ".", VbOk)
If srvname = "" Then
MsgBox("Cancell ed")
Exit Sub
End If
End If
Const adOpenStatic = 3
Const adLockOptimisti c = 3
Dim i
' making the connection to your sql server
' change yourservername to match your server
Set conn = CreateObject("A DODB.Connection ")
Set rs = CreateObject("A DODB.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=SQLOL EDB;Data Source=" & _
srvname & ";Trusted_Conne ction=Yes;Initi al Catalog=Master; "
' The query goes here
sql = "select " & vbCrLf & _
" t1.session_id, " & vbCrLf & _
" t2.total_elapse d_time AS elapsed, " & vbCrLf & _
" -- t1.request_id, " & vbCrLf & _
" t1.task_alloc, " & vbCrLf & _
" t1.task_dealloc , " & vbCrLf & _
" -- t2.sql_handle, " & vbCrLf & _
" -- t2.statement_st art_offset, " & vbCrLf & _
" -- t2.statement_en d_offset, " & vbCrLf & _
" -- t2.plan_handle, " & vbCrLf & _
" substring(sql.t ext, statement_start _offset/2, " & vbCrLf & _
" CASE WHEN statement_end_o ffset<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_ob jects_alloc_pag e_count) as task_alloc, " &
vbCrLf & _
" sum (internal_objec ts_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_req uests as t2 " & vbCrLf & _
"cross apply sys.dm_exec_sql _text(t2.sql_ha ndle) AS sql " & vbCrLf & _
"cross apply sys.dm_exec_que ry_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_t ime " & MinimumMillisec onds & vbCrLf & _
"order by t1.task_alloc DESC"
rs.Open sql, conn, adOpenStatic, adLockOptimisti c
'rs.MoveFirst
pg = "<html><head><t itle>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.CreateO bject("WScript. Shell")
Set WshSysEnv = WshShell.Enviro nment("PROCESS" )
temp = WshShell.Expand EnvironmentStri ngs(WshSysEnv(" TEMP")) & "\"
filename = temp & filename
Dim fso, f
Set fso = CreateObject("S cripting.FileSy stemObject")
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.CreateTextF ile(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.CreateTextF ile(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.h tm"
Set f = fso.CreateTextF ile(filename, True, True)
f.Write pg
f.Close
Dim oIE
SET oIE = CreateObject("I nternetExplorer .Application")
oIE.Visible = True
oIE.Navigate(fi lename)
'Alternate method:
'WshShell.run filename
' cleaning up
rs.Close
conn.Close
Set WshShell = Nothing
Set oIE = Nothing
Set f = Nothing
End Sub
Main
To get some real output, you need a long-running query. Here's one
(estimated to take over an hour):
PRINT GETDATE()
select count_big(*)
from sys.objects s1, sys.objects s2, sys.objects s3,
sys.objects s4, sys.objects s5
PRINT GETDATE()
Output is:
session_id elapsed task_alloc task_dealloc runningSqlText FullSqlText
query_plan
51 32847 0 0 select count_big(*) from sys.objects s1, sys.objects s2,
sys.objects s3, sys.objects s4, sys.objects s5 SQL Plan
Clicking on SQL opens the full SQL batch as a .txt file, including the PRINT
statements
Clicking on Plan allows you to see the .sqlplan file in MSSMS
========
Title: Using a VB Script to show long-running queries, complete with query
plans.
Today (July 14th), I found a query running for hours on a development box.
Rather than kill it, I decided to use this opportunity to develop a script
to show long-running queries, so I could see what was going on. (Reference
Roy Carlson's article for the idea.)
This script generates a web page which shows long-running queries with the
currently-executing SQL command, full SQL text, and .sqlplan files. The full
SQL query text and the sqlplan file are output to files in your temp
directory. If you have SQL Management Studio installed on the local
computer, you should be able to open the .sqlplan to see the query plan of
the whole batch for any statement.
'LongestRunning Queries.vbs
'By Aaron W. West, 7/14/2006
'Idea from:
'http://www.sqlserverce ntral.com/columnists/rcarlson/scriptedservers napshot.asp
'Reference: Troubleshooting Performance Problems in SQL Server 2005
'http://www.microsoft.c om/technet/prodtechnol/sql/2005/tsprfprb.mspx
Sub Main()
Const MinimumMillisec onds = 1000
Dim srvname
If WScript.Argumen ts.count 0 Then
srvname = WScript.Argumen ts(0)
Else
srvname = InputBox ( "Enter the server Name", "Server", ".", VbOk)
If srvname = "" Then
MsgBox("Cancell ed")
Exit Sub
End If
End If
Const adOpenStatic = 3
Const adLockOptimisti c = 3
Dim i
' making the connection to your sql server
' change yourservername to match your server
Set conn = CreateObject("A DODB.Connection ")
Set rs = CreateObject("A DODB.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=SQLOL EDB;Data Source=" & _
srvname & ";Trusted_Conne ction=Yes;Initi al Catalog=Master; "
' The query goes here
sql = "select " & vbCrLf & _
" t1.session_id, " & vbCrLf & _
" t2.total_elapse d_time AS elapsed, " & vbCrLf & _
" -- t1.request_id, " & vbCrLf & _
" t1.task_alloc, " & vbCrLf & _
" t1.task_dealloc , " & vbCrLf & _
" -- t2.sql_handle, " & vbCrLf & _
" -- t2.statement_st art_offset, " & vbCrLf & _
" -- t2.statement_en d_offset, " & vbCrLf & _
" -- t2.plan_handle, " & vbCrLf & _
" substring(sql.t ext, statement_start _offset/2, " & vbCrLf & _
" CASE WHEN statement_end_o ffset<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_ob jects_alloc_pag e_count) as task_alloc, " &
vbCrLf & _
" sum (internal_objec ts_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_req uests as t2 " & vbCrLf & _
"cross apply sys.dm_exec_sql _text(t2.sql_ha ndle) AS sql " & vbCrLf & _
"cross apply sys.dm_exec_que ry_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_t ime " & MinimumMillisec onds & vbCrLf & _
"order by t1.task_alloc DESC"
rs.Open sql, conn, adOpenStatic, adLockOptimisti c
'rs.MoveFirst
pg = "<html><head><t itle>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.CreateO bject("WScript. Shell")
Set WshSysEnv = WshShell.Enviro nment("PROCESS" )
temp = WshShell.Expand EnvironmentStri ngs(WshSysEnv(" TEMP")) & "\"
filename = temp & filename
Dim fso, f
Set fso = CreateObject("S cripting.FileSy stemObject")
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.CreateTextF ile(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.CreateTextF ile(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.h tm"
Set f = fso.CreateTextF ile(filename, True, True)
f.Write pg
f.Close
Dim oIE
SET oIE = CreateObject("I nternetExplorer .Application")
oIE.Visible = True
oIE.Navigate(fi lename)
'Alternate method:
'WshShell.run filename
' cleaning up
rs.Close
conn.Close
Set WshShell = Nothing
Set oIE = Nothing
Set f = Nothing
End Sub
Main
Comment