Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Devon, UK
    Posts
    3

    Question Unanswered: Profiling/RPC:Completed

    Hi DB Gurus,

    I'm profiling a SQL2000 database and I'm seeing collossal spikes in the length of time taken to execute some stored procedures - usually clocking in around 18ms, but sometimes spiking to 10000ms and I'm wondering why. I can't find anything in the manuals detailing exactly what is being measured by the RPC:Completed event class. Does anyone know if it includes network round trip time? I.e. is the time measured including the time taken to deliver the resultset to the client?

    I'd be grateful for any advice!

    Toot

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think it's only on the server...

    Do you see any locks?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Must be Profiler day on DBForums ;-).

    There is an article in BOL called "SQL Profiler Data Columns" that details what the data means. As for the data pertaining to the event, I also believe it is the time taken for the server to generate the result. If your client is on a dialup connection, and tries to do

    select * from millionrowtable

    He should use the time it takes to download the result set to reflect on the benefits of where clauses.

    On the matter of the stored procedure, it sounds like you can give parameters to the query. Do different parameters give the wildly different times? Or is it totally random?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you post the procedure...usually the culprit...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2003
    Location
    Devon, UK
    Posts
    3
    Thanks for the replies. Some answers to the questions:

    It doesn't appear to be a locking problem but that's a good idea to definitely get to the bottom of - I'll re-check that now.

    Having a bit of an issue with the documentation, which is why I came here - all it seems to say for profiler stuff is a re-wording of the topic - for example an explanation of event classes:

    Duration Amount of time (in milliseconds) taken by the event.
    I've noticed Microsoft doing that a lot recently...

    For these tests, I'm running the same carefully selected set of data through over and over and the spikes aren't occurring at regular intervals - which rules the data out.

    The question is: Is "Duration" measuring database execution time or the entire round trip?

    Confused,
    Toot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •