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?
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?