SQL Server Performance Forum – Threads Archive

SQL 2005 default traces

Ok wizzards, I got a question for ya.
I know that the default trace provides the data for the default built-in reports, but when looking at the reports, I do not find much textdata at all. Given that, how can the reports show the top 10 queries using IO/cpu, etc? It has to see all selects, code etc to make that determination. Thoughts? I am wanting to use this feature to capture any rogue selects etc that could be happening. I am considering making a "file watcher" that would grab data from the 5 trace files as they are created or rolled off and analyze them and keep what I want to review later. Mike Michael
MCDBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7

Please change to different thread if I have mis-posted.
Michael
MCDBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7

Why dont you take help of DMVs in this case?
Is it compulsory to use Trace>? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

no its not. I should do so. I havent got into many of the DMVs yet. Just starting to get my feet wet lately. Michael
MCDBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7

Then see Hari’shttp://www.codeproject.com/useritems/Dynamic_Management_Views.asp article on DMVs usage and you know BOL is your friend too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

I still would like to know how the reports gleen this info from the default traces. The DMVs are good for troubleshooting, but I am looking to capture historical info to look back and say ‘a-ha’ this person did "this". I know that is prevented by permissions, but I am looking for stuff that would be out of the ordinary. Michael
MCDBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7

Check the following interesting articles…and don’t any where modifying this and as per Kalen Delaney article you can’t modify it… http://www.sqlmag.com/Article/ArticleID/48939/sql_server_48939.html
http://mssqltips.com/tip.asp?tip=1111 MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Ok.. I found an answer. It doesnt use the trace for but some info reports. I spoke with Kalen Delaney and she just ran a trace when running the top queries IO report and got the following. Duh.. I should of just run a trace! anyway.. here is what it gives. I am sure we can work this query other ways for info we need. exec sp_executesql @stmt=N’begin try
select top 10 rank() over (order by total_logical_reads+total_logical_writes desc,sql_handle,statement_start_offset ) as row_no
, (rank() over (order by total_logical_reads+total_logical_writes desc,sql_handle,statement_start_offset ))%2 as l1
, creation_time
, last_execution_time
, (total_worker_time+0.0)/1000 as total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
, total_logical_reads as [LogicalReads]
, total_logical_writes as [LogicalWrites]
, execution_count
, total_logical_reads+total_logical_writes as [AggIO]
, (total_logical_reads+total_logical_writes)/(execution_count+0.0) as [AvgIO]
, case when sql_handle IS NULL
then ” ”
else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end – qs.statement_start_offset) /2 ) )
end as query_text
, db_name(st.dbid) as database_name
, st.objectid as object_id
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_logical_reads+total_logical_writes > 0
order by [AggIO] desc
end try
begin catch
select -100 AS row_no
, 1 AS l1, 1 AS creation_time, 1 AS last_execution_time, 1 AS total_worker_time, 1 AS Avg_CPU_Time, 1 AS logicalReads, 1 AS LogicalWrites
, ERROR_NUMBER() AS execution_count
, ERROR_SEVERITY() AS AggIO
, ERROR_STATE() AS AvgIO
, ERROR_MESSAGE() AS query_text
end catch’,@params=N”
Michael
MCDBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7

]]>