|
|
Posted by Aaron West on 07/17/06 23:38
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.
'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;"
' The query goes here
sql = "select " & vbCrLf & _
" t1.session_id, " & vbCrLf & _
" t2.total_elapsed_time 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
Navigation:
[Reply to this message]
|