Query Execution time in Management Studio & profiler. What does it measure?

Question

I have my production SQL Server in a remote data center(and the web servers are located in the same data center). During development we observed that one particular view takes a long time to execute (about 60-80 secs) in our local development SQL Server, and we were OK with it.It was promoted to production and when I run the same query on Production DB (which is in the data center)from my local Management Studio I see that the query takes about 7 minutes,17 secs to run (available the bottom right corner of the management studio).When I ran a profiler I see that the time taken to execute that query is 437101 microseconds milliseconds, though it shows up in management studio as 7:17. , which actually is about 437101 milliseconds. My DBA says that in prod the view takes just about 60 to 80 seconds though I see different numbers from profiler and management studio.Can someone tell me what these durations mean in Profiler and management studio ?

My guess: duration between sending the last request byte and receiving the last response byte from the server. The client statistics were as follows: Client Processing time: 90393 Total Execution time:92221 Wait time on server replies: 1828

My best guess on what "duration" on the profiler means is "the time taken by SQL Server (optimization engine to parse the query,generate the query plan or use the existing query plan + fetch records from different pages) to generate the result set which excludes the time taken by data to travel over the wire to the client"

Edit: I find that both these times are about the same (management studio vs profiler). How do they relate with the times I see in client statistics ?

Can some one throw more light on these ?

Answer 1

If I'm understanding your question correctly, you are first questioning the difference between the Duration reported by Profiler and the statistics presented in SSMS (either in lower right-hand corner for general time and/or by SET STATISTICS TIME ON). In addition to that, you seem to be unconvinced of the production DBA's comment that the view is executing in the expected duration of ~60 seconds.

First, from Books Online, the statics that SSMS would report back via SET STATICS TIME ON:

"Displays the number of milliseconds required to parse, compile, and execute each statement."

You're spot-on for this. As for Duration in Profiler, it is described as:

"The duration (in microseconds) of the event."

From where I sit, these two should be functionally equivalent (and, as I'm sure you noticed, Profiler will report in microseconds if your going against SQL 2005 or later). I say this because the "event" in this case (regarding Duration in Profiler) is the execution of the select, which includes delivery to the client; this is consistent in both cases.

It seems you suspect that geography is the culprit to the long duration when executing the query remotely. This very well may be. You can test for this by executing the select on the view in one query window then spawning another query window and reviewing the wait type on the query:

select
    a.session_id
    ,a.start_time
    ,a.status
    ,a.command
    ,db_name(a.database_id) as database_name
    ,a.blocking_session_id
    ,a.wait_type
    ,a.wait_time
    ,a.cpu_time
    ,a.total_elapsed_time
    ,b.text
from sys.dm_exec_requests a
    cross apply sys.dm_exec_sql_text(a.sql_handle) b
where a.session_id != @@spid;

I would suspect that you would see something like ASYNC_NETWORK_IO as the wait type if geography is the problem - otherwise, check out what does come of this. If you're Profiling the query of your remote execution, the Duration will be reflective of the time statistics you see in SSMS. HOWEVER, if you're using Profiler and finding that the duration of this query when executed from one of the web servers that sits in the same data center as the SQL Server is still taking 7 minutes, then the DBA is a big, fat liar :). I would use Profiler to record queries that take longer than 1 minute, try to filter for your view and take the average to see if you're on target for performance.

Because there are no other answers posted, I'm concerned that I'm way off base here - but it's late and I'm new to this so I thought I'd give it a go!