Post reply

Effective filtering of SQL Profiler tracing

  • March 21, 2012 at 3:53 am

    #266069

    Hi,

    I've never quite fathomed how SQL Server Profiler uses the filters you may add to your trace.

    I want to collect RPC:Completed and Showplan XML Statistics Profile, for a particular database for any stored procedue that has a CPU time value of more thanb 5000 ms for example and Duration greater than 5000 ms.

    I set the databasename filter to the database in question, and set CPU and Duration accordingly.

    I can see that I only see RPC:Completed events that satify those criteria, but the Showplan event is generated for evey bit of SQL that is executed by the engine. It's a busy server so that would mean a big trace file which i don't want.

    All i want is the RPC events that satisfy the cpu/duration criteria, and an associated showplan event. not a showplan event for every bit of tSQL SQLServer executes.

    How do I do this?

  • March 21, 2012 at 4:05 am

    #1462067

    Hi Steven,

    It's because the ShowPlan event doesn't have CPU time value, so it can't be filtered by that, and returns all rows for that event.

    You can exclude them by ticking the 'Exclude rows that do not contain values' box on the filter screen, but I then I think that would exclude all Showplan events!

    Not sure there's a way around it, I've been regularly frustrated by Profiler on things like this.

    My usual way is to grab all, export to table, and then filter.

    Hopefully they'll be someone along who's more of a Profiler expert than I that can help further 😀

    Cheers

    Gaz

  • March 21, 2012 at 6:17 am

    #1462093

    The showplan events don't have durations or the like, so they can't be filtered. Nasty problem.

    What I've recommended in the past is a 2-phase approach. Trace first for the procedures with high durations, identify the ones you want to look at then run a second trace for the showplan filtering on the object names.

    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 21, 2012 at 10:38 am

    #1462287

    Thanks both. i'll follow your advice

Viewing 4 posts - 1 through 3 (of 3 total)

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