Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Use SQL Profiler


How to Use SQL Profiler

Author
Message
thbaig
thbaig
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 478
Hi,
I want to trace all sql,stored proc with sql server profiler. So that I may able to extract top resource consuming components.

I have created a template with with following selection
Performance
Performance Statistics
ShowPlanAll
ShowPlanXML

TSQL
SQL batch completed
Sql batchStarting
Sql StmtCompleted
Sql StmtStarting

Can you please suggest
1- is above selection enough or should add or remove some options ?
2- I ran trace with above and stored results in a table. But was not able to extract use full info.I think I don't know which column to group etc to find costly query in term of read,write or time.
Kindly guide me how should I read trace table to extract required information?

thanks
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2596 Visits: 8437
Why not use the system dmvs? e.g.

SELECT TOP 100 [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) qt
ORDER BY [Average IO] DESC;

SELECT TOP 100 [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) qt
ORDER BY [Average CPU used] DESC;




Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
thbaig
thbaig
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 478
thank you very much SSCrazy. These queries are really great to have. Can I modify these queries to extract queries executed by specific user?

I want to use profiler too, as I have to save records in table for some historical analysis of application benchmark Sad
Can you guide me for profiler too ?
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2596 Visits: 8437
thbaig1 (2/19/2013)
thank you very much SSCrazy. These queries are really great to have. Can I modify these queries to extract queries executed by specific user?


No.

thbaig1 (2/19/2013)
I want to use profiler too, as I have to save records in table for some historical analysis of application benchmark Sad
Can you guide me for profiler too ?


OK, I guess that all you probably need is "SQL:StmtCompleted" with the columns "TextData", "SPID", "Duration", "StartTime", "EndTime", "Reads" and "Write" selected.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
thbaig
thbaig
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 478
I have completed the profiling and now have all the data in table Smile

I am using following query to extract results. I have observed that for statements data is saying sql took 593 Sec and 8000 reads. But if I may use that query from TextData and execute, it gives me results in less than second and reads are fewer. Total execution as per query is 1 . What could be the reason or I am interpreting data wrongly ?

SELECT TOP 5 COUNT(*) AS TotalExecutions,
EventClass, CAST(TextData as nvarchar(4000)) AS query
,SUM(Duration)/1000 AS DurationTotal_Sec
,SUM(CPU) AS CPUTotal_Sec
,SUM(Reads) AS ReadsTotal
,SUM(Writes) AS WritesTotal
FROM [Halsoos].[dbo].[tmpTable]
WHERE eventclass IN (41)
GROUP BY EventClass, CAST(TextData as nvarchar(4000))
ORDER BY DurationTotal_sec DESC
--Order by ReadsTotal desc
--ORDER BY WritesTotal DESC
--ORDER BY CPUTotal_Sec DESC
thbaig
thbaig
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 478
I got the answer

Beginning with SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). In SQL Server 2005 and later, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.

http://msdn.microsoft.com/en-us/library/ms175848.aspx
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search