473,320 Members | 2,000 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 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):
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
Jul 17 '06 #1
1 3446
PS.

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

Jul 17 '06 #2

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

Similar topics

0
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...
15
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...
0
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...
3
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...
11
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...
1
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...
0
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...
3
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...
8
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...
2
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...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.