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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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