472,783 Members | 961 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,783 software developers and data experts.

LongestRunningQueries.vbs - Using a VB Script to show long-running queries, complete with query plans

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):
select count_big(*)
from sys.objects s1, sys.objects s2, sys.objects s3,
sys.objects s4, sys.objects s5

Output is:

session_id elapsed task_alloc task_dealloc runningSqlText FullSqlText
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
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

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.

'By Aaron W. West, 7/14/2006
'Idea from:
'Reference: Troubleshooting Performance Problems in SQL Server 2005
Sub Main()
Const MinimumMilliseconds = 1000
Dim srvname
If WScript.Arguments.count 0 Then
srvname = WScript.Arguments(0)
srvname = InputBox ( "Enter the server Name", "Server", ".", VbOk)
If srvname = "" Then
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

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
pg = pg & "</tr>"
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>"
'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)
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)
pg = pg & "<td><a href=""" & filename & """>Plan</a>"
'We could open them immediately, eg:
'WshShell.run temp & filename

pg = pg & "</tr>"

pg = pg & "</table>"

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

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

'Alternate method:
'WshShell.run filename

' cleaning up
Set WshShell = Nothing
Set oIE = Nothing
Set f = Nothing
End Sub

Jul 17 '06 #1
1 3378

SQL 2005 only.

And don't forget to cancel that long-running query, if you try it.

"Aaron West" <ta******@hotmail.no.spam.comwrote in message
news:Qu******************************@speakeasy.ne t...
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):
select count_big(*)
from sys.objects s1, sys.objects s2, sys.objects s3,
sys.objects s4, sys.objects s5

Jul 17 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: David Walker | last post by:
Hi Does anyone know of a script similar in style to phpbb (or really any threaded forum that can show old posts and post new messages), but which can use a nntp newsgroup as the backend instead...
by: kpp9c | last post by:
I am kind of in a bit of a jam (okay a big jam) and i was hoping that someone here could give me a quick hand. I had a few pages of time calculations to do. So, i just started in on them typing...
by: kmunderwood | last post by:
I am having trouble excluding select xml out to HTML using xsl I want to ignore some xml and turn others red I can not find the right way to both: 1. Only show the <tag> that want to, and...
by: Random Person | last post by:
Does anyone know how to use VBA to relink tables between two MS Access databases? We have two databases, one with VBA code and the other with data tables. The tables are referenced by linked...
by: Grasshopper | last post by:
Hi, I am automating Access reports to PDF using PDF Writer 6.0. I've created a DTS package to run the reports and schedule a job to run this DTS package. If I PC Anywhere into the server on...
by: Jozsef Bekes | last post by:
Hi All, I need to offer scripting possibilities in my app, and have to use MSSCriptControl for some reasons. I would like to use the feature that's called variable number of arguments, that is...
by: amessimon | last post by:
Hi I have a long form - around 80 fields - which i have decided to break up into more manageable sections using the <asp:Panel> control. Basically i have three panels (stages) containing...
by: Rob | last post by:
Hi all, I am having trouble converting the code below (found on http://vbnet.mvps.org/index.html?code/core/sendmessage.htm) into a format that will work using vb .NET. Can anyone have a look...
by: dbaplusplus | last post by:
I worked on web development using java script many many years, so I am now a newbie to javascript. I have a single html page, which is generated dynamically using some programming language. Web...
by: rahulthathoo | last post by:
Hi I have a perl program called by my php script, which in turn is called by the browser. Now, the perl program is called using exec, but takes a long time to finish. So, till the time the perl...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.