profiler - "duration" column

  • ckempste

    SSCoach

    Points: 17983

    Hi all

    After some testing, I have found that the "duration" filter/column is a poor one to measure DML performance. The trace seems to pick up the duration for the entire elasped time for not on the DML to run, but its parse time, and connect/disconnect time for the transaction returning back to the end-user. For example, I have a app where (via ado) a connection is made, the query runs, returns the data to the com+ object which then wrappers it with xml and closes the recordset and thats it. Under profiler its telling me the duration is 20sec when in actual fact i only want to locate the SQL statement itself taking 20secs, not the total elasped time. As for this example, the query actually runs very quickly but is returing around 4000 rows. Its good to know of that the transaction's elasped time is XYZ but doesnt tell me the query is the bottleneck.

    Ideas?

    Cheers

    Chris


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Andy Warren

    SSC Guru

    Points: 119675

    What events are you profiling?

  • ckempste

    SSCoach

    Points: 17983

    Hi

    This is a bog basic trace, tracing sql that has a duration of more than 4sec. The trace includes the text, read, write totals.

    Cheers

    Chris.


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

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

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