Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Use SQL Profiler Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 5:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 7:26 AM
Points: 72, Visits: 220
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
Post #1421594
Posted Tuesday, February 19, 2013 5:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:06 AM
Points: 2,404, Visits: 7,311
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;




Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1421597
Posted Tuesday, February 19, 2013 6:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 7:26 AM
Points: 72, Visits: 220
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
Can you guide me for profiler too ?
Post #1421602
Posted Tuesday, February 19, 2013 6:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:06 AM
Points: 2,404, Visits: 7,311
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
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1421608
Posted Wednesday, February 20, 2013 10:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 7:26 AM
Points: 72, Visits: 220
I have completed the profiling and now have all the data in table :)

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
Post #1422213
Posted Thursday, February 21, 2013 4:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 7:26 AM
Points: 72, Visits: 220
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
Post #1422491
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse