Post reply

read sqlserver trace file

  • 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

  • March 18, 2011 at 6:09 am

    #1300433

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

    __________________________
    Allzu viel ist ungesund...

  • March 18, 2011 at 6:18 am

    #1300435

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

  • March 18, 2011 at 6:22 am

    #1300436

    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!

  • March 18, 2011 at 6:46 am

    #1300457

    thanks Lowell, but it is not giving required results.

    where can i find execution plan of queries?

  • March 18, 2011 at 6:53 am

    #1300467

    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

  • March 18, 2011 at 6:58 am

    #1300471

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

  • March 18, 2011 at 7:00 am

    #1300474

    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

  • March 18, 2011 at 7:04 am

    #1300479

    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.

  • March 18, 2011 at 11:34 am

    #1300694

    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?

  • March 18, 2011 at 11:39 am

    #1300698

    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

  • March 18, 2011 at 11:42 am

    #1300702

    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

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

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