read sqlserver trace file

Question

Post reply

read sqlserver trace file

thbaig

Hall of Fame

Points: 3003
More actions
March 18, 2011 at 5:45 am

#234325

Hi,
I have sqlserver 2005. I have enable trace and checked all the required performance parameters to get execution plans.
Now I have file but don't know how to read it to extract execution plans.
kindly tell me any utility or software that can extract execution plan and performance from trace file.
thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Mr. Holio Hall of Fame Points: 3314 More actions · Answer 1

You should be able to open the file created from the trace using SSMS.

__________________________
Allzu viel ist ungesund...

thbaig Hall of Fame Points: 3003 More actions · Answer 2

but it is showing like binary, how can I extract execution plan group with queries etc?

Lowell SSC Guru Points: 323518 More actions · Answer 3

easiest way is from within SSMS via TSQL for me; the trace file will be open as if it were a table:

you might need to do select * from sys.traces to find out which ID is your trace:

--which id is YOUR trace?

--1 is either c2 auditing or the default DDL trace;

--2 is the default trace IF C2 auditing is enabled, otherwise it's one of your traces.

declare @sql varchar(max)

declare @TraceFileName nvarchar(256)

set @TraceFileName = (select path from sys.traces where id = 1)

)

SELECT

trc_evnt.name

,dflt_trc.DatabaseName

,dflt_trc.ApplicationName

,dflt_trc.TextData

,dflt_trc.FileName

,dflt_trc.LoginName

,dflt_trc.StartTime

--.*

FROM fn_trace_gettable( @Tracefilename , NULL) AS dflt_trc

INNER JOIN sys.trace_events AS trc_evnt

ON dflt_trc.EventClass = trc_evnt.trace_event_id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

thbaig Hall of Fame Points: 3003 More actions · Answer 4

thanks Lowell, but it is not giving required results.

where can i find execution plan of queries?

GSquared SSC Guru Points: 260824 More actions · Answer 5

thbaig1 (3/18/2011)
thanks Lowell, but it is not giving required results.
where can i find execution plan of queries?

How did you create the trace? Did you use Profiler, or did you use sp_trace_create? Or something else?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

thbaig Hall of Fame Points: 3003 More actions · Answer 6

I have used profiler. with all performance option checked also deadlock and some other options

GSquared SSC Guru Points: 260824 More actions · Answer 7

thbaig1 (3/18/2011)
I have used profiler. with all performance option checked also deadlock and some other options

Then Profiler can open the trace file for you.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

thbaig Hall of Fame Points: 3003 More actions · Answer 8

my question is how can I extract execution plan in it.

I have created a table with some function , I have tried to open in several way but nothing like explain plan for queries are there.

I have read the documentation selected all the parameter in profiler that used to create execution plan.

But nothing like execution plan............. 🙁

I am oracle professional and things are documented and straight there. like tfprof etc to generate report on trace files.

thbaig Hall of Fame Points: 3003 More actions · Answer 9

at last I have generated the explain plan file from profiler.

Now these are hundreds of files.

Is there any way to read and group all files at once and generate a single report?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

I strongly recommend that you don't pull exec plans using profiler unless the trace is filtered, short and run as a server-side trace. That's far from a lightweight trace, it can cause serious problems.

What were you wanting to do with those exec plans? What was the point of collecting them?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 11

SQL Nexus[/url] is a nice tool to use to process trace files.

ClearTrace is another one: http://www.scalesql.com/cleartrace/[/url]

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton