Post reply

SQL Profile Trace results analysis

  • November 12, 2014 at 4:19 am

    #291951

    Hi,

    I have run a trace to capture slow running queries and have captured a few.

    When I go into the showPlanXML for these and hover over 'Clustered index scan' I can only see 'Estimated number of Rows'. I cannot see the option for 'Actual Number of Rows'.

    Do you know why this is?

    Also how I can see the actual number of rows option? do I need to enable it in settings somewhere to see it?

    thanks.

  • November 12, 2014 at 4:25 am

    #1757900

    The profiler trace can only capture the estimated execution plan. The actual execution plan is available only when you run the query.

    -- Gianluca Sartori

  • November 12, 2014 at 4:28 am

    #1757901

    I see. Thanks for that.

  • November 12, 2014 at 4:39 am

    #1757907

    Which event did you use? Showplan XML Statistics Profile will capture actual plans. But, it has an extremely high overhead. Because trace has such lousy filtering, I don't recommend you do this at all.

    If you were on 2012 or higher, you can use extended events. It's still a very expensive capture, but the filtering works much better, so it's safter. Unfortunately, actual plans couldn't be captured with extended events in 2008.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • November 12, 2014 at 4:58 am

    #1757918

    It was for SQL Server 2008 R2.

    So to use extended events would I just create a new session in SSMS 2012? I used ShowplanXML for my trace.

  • November 12, 2014 at 5:01 am

    #1757919

    Thanks for correcting me, Grant.

    Sorry for posting incorrect information.

    -- Gianluca Sartori

  • November 12, 2014 at 5:12 am

    #1757922

    zedtec (11/12/2014)

    So to use extended events would I just create a new session in SSMS 2012?

    Create a new session, pick the events, add filters, run session. That's the basics, but enough to get you started.

    Watch the overhead, the actual execution plan event is still a nasty one, even in extended events. Not something you really want to run for hours on busy servers.

    I used ShowplanXML for my trace.

    Showplan XML returns the estimated plan. Actual plan one has the words 'statistics profile' in the event name.

    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

  • November 12, 2014 at 6:33 am

    #1757962

    spaghettidba (11/12/2014)

    Thanks for correcting me, Grant.

    Sorry for posting incorrect information.

    Not a correction, an enhancement.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • November 12, 2014 at 7:13 am

    #1757978

    Thanks to all of you for your help.

Viewing 9 posts - 1 through 8 (of 8 total)

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