Mastering SQL Server Profiler - Part 5: Data Columns

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    errh what did you understand when I said that the server could possibly handly 8 queries at the SAME TIME giving you 8 seconds of processing power PER SECOND? OBviously that's all theorical since the server can handle more than that when you consider all blockings and lockings, waits... etc.

    I'm not trying to be a jerk or anything, but I need to understand where I miscommunicated my message!!

  • oralinque

    Ten Centuries

    Points: 1157

    Ninja's_RGR'us (3/30/2010)


    errh what did you understand when I said that the server could possibly handly 8 queries at the SAME TIME giving you 8 seconds of processing power PER SECOND? OBviously that's all theorical since the server can handle more than that when you consider all blockings and lockings, waits... etc.

    I'm not trying to be a jerk or anything, but I need to understand where I miscommunicated my message!!

    Ha! So you did. My apologies. You didn't miscommunicate anything, but it was early in the troubleshooting process for me and I had a million ideas running through my head. I should have slowed down and read through the entire thread again. Part of the problem for me was going through 77,000,000 records in the profile table. I spent most of yesterday deleting the non-important records and got it down to about 23,000,000 records which allowed me to finally do some analysis without having to wait for hours for a query to run.

    Thank you all for your help.

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Well here's an obvious one I hadn't considered... because I don't trace this one anymore

    Were you catching the audit logouts events?

    That event keeps the total duration of the whole time when the connection is open (even if nothing is processed). That alone will at the very least double the total duration time. It could be even worse if you open the connection, run a query download the data in your app, process it, do another query. process some more, then finally close the connection. Because the duration now includes all the wait time, client processing time, network time and other stuff which might not be relevant to your situation. (It may be relevant if you realize the network is too slow to handle the load you're throwing at it, but that's another story).

    That could explain a lot in your case. Then considering the multiple queries at the same time and adding wait times for resources and data, you pretty much just answered the rest of the discrepancies.

    Maybe Brad could confirm this but I once read that the best meter for the processing time is the CPU because this is apparently when the server is actually working on the query. This figures should exclude the time wasted on disk reads and waiting on data because of locks or other waits.

    So Brad what's your take on this epiphany?

  • ioana-477197

    Ten Centuries

    Points: 1015

    Hello

    In the video it says duration is the total amount of time spent by an event, from beginning to end.

    My question is what is beginning and what is end? For example what are they for a select query coming from a client application?

    Does duration include the time spent in a SQLServer:NetworkIOWait?

    Thank you,

    Iana

Viewing 4 posts - 16 through 19 (of 19 total)

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