February 19, 2013 at 5:48 am
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
February 19, 2013 at 5:52 am
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;
February 19, 2013 at 6:10 am
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 ?
February 19, 2013 at 6:27 am
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.
February 20, 2013 at 10:51 am
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
February 21, 2013 at 4:03 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply