April 17, 2008 at 1:23 pm
Hi Gail,
So it means basically that 1643 RowNumber is useless.
I just need to look at 1644 RowNumber? It's the same SQL, User, SPID.
This will show me Duration / StarTime / EndTime?
Am I correct?
RowNumber TextData Duration StartTime EndTime
----------------------------------------------------------------------------------------------
1643 Select count(*) from dispenser ... NULL 4/17/2008 14:18 NULL
1644 Select count(*) from dispenser ... 306 4/17/2008 14:18 4/17/2008 14:18
Gail what is ClientProcessID? Is it a unique client identification for a session,connection?
What is it?
Thanks,
Robert
April 17, 2008 at 1:26 pm
Gail,
Are you talking about these books?
http://www.insidesqlserver.com/thebooks.html
Inside Microsoft® SQL Server(TM) 2005: Query Tuning and Optimization (Paperback)
by Kalen Delaney (Author), Sunil Agarwal (Author), Craig Freedman (Author), Ron Talmage (Author), Adam Machanic (Author)
Thanks,
Robert
April 17, 2008 at 1:41 pm
Not necessarily useless, just not of value for what you're doing. You can remove the starting event from the trace if you wish.
The client process ID is the ProcessID (from task manager) of the querying tool running on the client PC. Probably not of value.
Yes, those are the books. There are 4 in the series
Storage Engine
T-SQL Querying
T-SQL programming
Tuning and optimisation
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2008 at 1:37 am
An alternative to using SQL Profiler is querying system DMVs (provided your system has been running for a sufficiently long time for enough stats to be collected in memory).
Costliest queries by I/O:
SELECT TOP 10
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;
Costliest queries by CPU:
SELECT TOP 10
[Average CPU used] = total_worker_time / qs.execution_count
,[Total CPU used] = total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END -
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;
Queries that execute most often:
SELECT TOP 10
[Execution count] = execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Execution count] DESC;
Info taken from link below. I would strongly recommend it:
Uncover Hidden Data to Optimize Application Performance
http://msdn.microsoft.com/msdnmag/issues/08/01/SqlDmvs/default.aspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 21, 2008 at 7:31 am
Mario,
Thank you very much for your queries.
They seem to be a much faster way to quickly identify
the most "troubled SQL code" in the system.
Question.
What are the Individual Query and Parent Query?
Is like Parent calls Individual?
My goal is to quickly find which stored procedure
has that piece of SQL code that is so costly.
Do I search for an INDIVIDUAL or PARENT string?
Thanks,
Robert
April 21, 2008 at 8:57 am
riga1966 (4/21/2008)
Mario,Thank you very much for your queries.
They seem to be a much faster way to quickly identify
the most "troubled SQL code" in the system.
Question.
What are the Individual Query and Parent Query?
Is like Parent calls Individual?
My goal is to quickly find which stored procedure
has that piece of SQL code that is so costly.
Do I search for an INDIVIDUAL or PARENT string?
Thanks,
Robert
Parent query is the module (sproc, UDF etc.) that contains the individual statement (SELECT, INSERT, etc.). So, for your purposes you would search for the PARENT string to find the sproc containing the costly piece of code.
Let me know if you have more questions.
Regards
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 21, 2008 at 9:56 am
Hi Mario,
So Parent contains the whole text of Stor proc?
Even if it's 3000 lines?
Thanks,
Robert
April 21, 2008 at 7:29 pm
I believe the parent is the complete SQL text. Here's a fun script I run sometimes, which writes an HTML to the local filesystem and opens it in Internet Explorer, allowing you to see the running SQL and link to a file containing the complete SQL batch ("parent" SQL), and also provides a link to the query plan .
'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;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"
rs.Open sql, conn, adOpenStatic, adLockOptimistic
'rs.MoveFirst
pg = " " & vbCrLf
pg = pg & " " & vbCrLf
If Not rs.EOF Then
pg = pg & " "
For Each col In rs.Fields
pg = pg & " "
c = c + 1
Next
pg = pg & " "
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 & " "
For c = 0 to cols-3
pg = pg & " "
Next
'Output FullSQL and Plan Text to files, provide links to them
filename = "topplan-sql" & i & ".txt"
if rs.fields.count > 2 then
Set f = fso.CreateTextFile(temp & filename, True, True)
f.Write rs(cols-2)
f.Close
pg = pg & " "
filename = "topplan" & i & ".sqlplan"
Set f = fso.CreateTextFile(temp & filename, True, True)
f.Write rs(cols-1)
f.Close
pg = pg & " "
end if
'We could open them immediately, eg:
'WshShell.run temp & filename
rs.MoveNext
pg = pg & " "
Loop
pg = pg & " "
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
April 22, 2008 at 6:42 am
Hi Aaron,
It's definitely a fun script. I love this kind of things.
But I got an error:
Query returned no results
I guess I need to test the query first in Query Analyzer.
By the way in 2005 it's not QA anymore.
How do they call it?
Robert
April 22, 2008 at 7:37 am
riga1966 (4/21/2008)
Hi Mario,So Parent contains the whole text of Stor proc?
Even if it's 3000 lines?
Thanks,
Robert
yes, I believe so.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 22, 2008 at 10:56 am
The script as designed only shows actively-running SQL batches with high CPU (greater than 1 second), so you'll need to find a slow-running batch to test it.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply