server side [?] query execution time

Question

Post reply

server side [?] query execution time

rw30

SSC Veteran

Points: 213
More actions
June 28, 2009 at 8:04 am

#133881

hi,
I would like to know what is query execution time but without overhead added by :
---transferring data thru the network \
---visualizing data rows in client application
---other things I didn't mention
example:
I run a query
select * from fac1
in SSMS
buffers are cleared before each execution
once I ran it at the machine where the server is installed, query execution time shown by SS Profiler is ~22secs
when I ran same query at the machine connected to the server by the wireless network, query execution time is ~76 secs
same query, same execution plan etc etc.... [and, of course, query plan says that cost of selection is very small when compared to table scan ]
so the difference seems to be caused by network latency;
situation would be the same if the query returns big amount of data, i.e. there would be some latency caused just by waiting for the rows to appear in SMSS windows;
is that possible to know query execution time not influenced by those factors ?
regards,
kk

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

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

rw30 SSC Veteran Points: 213 More actions · Answer 1

what I would like to get is the same what you get inOracle by setting SQL_TRACE=TRUE

and then extracting the trace file by TKPROF

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

SET STATISTICS TIME ON

and then run your query. There will be fairly detailed time breakdowns in the messages pane.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rw30 SSC Veteran Points: 213 More actions · Answer 3

GilaMonster (6/28/2009)
SET STATISTICS TIME ON
and then run your query. There will be fairly detailed time breakdowns in the messages pane.

results shown after enabling SET STATISTICS TIME are almost exactly same as those shown in Profiles [differs by few ms]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

rw30 (6/28/2009)
GilaMonster (6/28/2009)
SET STATISTICS TIME ON
and then run your query. There will be fairly detailed time breakdowns in the messages pane.
results shown after enabling SET STATISTICS TIME are almost exactly same as those shown in Profiles [differs by few ms]

Yes, they will be. Both show the server-side query execution time, ie the execution time excluding network latency, display time, etc. I thought that was what you were after?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rw30 SSC Veteran Points: 213 More actions · Answer 5

GilaMonster (6/28/2009)
rw30 (6/28/2009)
GilaMonster (6/28/2009)
SET STATISTICS TIME ON
and then run your query. There will be fairly detailed time breakdowns in the messages pane.
results shown after enabling SET STATISTICS TIME are almost exactly same as those shown in Profiles [differs by few ms]
Yes, they will be. Both show the server-side query execution time, ie the execution time excluding network latency, display time, etc. I thought that was what you were after?

hmmmm

that's exactly what I'm after, but, as I wrote before:

once I ran it at the machine where the server is installed, query execution time shown by SS Profiler is ~22secs

when I ran same query at the machine connected to the server by the wireless network, query execution time is ~76 secs

so it looks like the network latency IS included ;(

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Was that via profiler or SSMS's query execution time (bottom right). The latter does include network trip time and everything as it's a display of the time until management studio's finished

If it was via profiler, the query could have been slowed down by network waits. If SQL can't finish sending the data because the network's slow, the query execution time goes up. It has to, execution time is from the time the server starts to execute the statement until it has finished and sent all the results (though not necessarily the time until the client receives them). If there are network waits (visible in SQL as Async_Network_IO wait types), then the query execution time goes up.

It still doesn't include the time actually taken for the data to get across the network and be displayed though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rw30 SSC Veteran Points: 213 More actions · Answer 7

Was that via profiler or SSMS's query execution time (bottom right). The latter does include network trip time and everything as it's a display of the time until management studio's finished

regardless of the machine, where the query was ran [server or other PC], the results shown by:

---SS profiler

---in the 'Messages' tab [after executing SET STATISTICS TIME ON]

---in the status bar [bottom right]

are almost exactly the same

time in SS profiler is usually few ms greater than time shown in 'Messages' tab

time in status bar is the same as two others, but it's rounded down to the nearest integer

If it was via profiler, the query could have been slowed down by network waits. If SQL can't finish sending the data because the network's slow, the query execution time goes up. It has to, execution time is from the time the server starts to execute the statement until it has finished and sent all the results (though not necessarily the time until the client receives them). If there are network waits (visible in SQL as Async_Network_IO wait types), then the query execution time goes up.

It still doesn't include the time actually taken for the data to get across the network and be displayed though.

then the only way to know server side query execution time is to run SSMS on the server ? right ?

p.s. thx for helping me, as I didn't thanked before

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

rw30 (6/28/2009)
then the only way to know server side query execution time is to run SSMS on the server ? right ?

Or with a fast network where SQL's not going to have to sit and wait to put stuff on the network.

Question is, what are you trying to get here? Network waits are as much part of query execution as IO waits are. If SQL has tom wait for something while executing the query, then I'd personally consider it part of the query execution time and, if it's excessive, try and do something about the cause (the slow network)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rw30 SSC Veteran Points: 213 More actions · Answer 9

GilaMonster (6/28/2009)
rw30 (6/28/2009)
then the only way to know server side query execution time is to run SSMS on the server ? right ?
Or with a fast network where SQL's not going to have to sit and wait to put stuff on the network.
Question is, what are you trying to get here? Network waits are as much part of query execution as IO waits are. If SQL has tom wait for something while executing the query, then I'd personally consider it part of the query execution time and, if it's excessive, try and do something about the cause (the slow network)

I'm trying to check server performance with some queries [for some course @ university] - so stuff like network latency is not a factor at all

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Are things like IO latency (when fetching data off disk), lock wait times and the like relevant, or are you just looking for the amount of timethe queries spent executing (on the CPU)?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rw30 SSC Veteran Points: 213 More actions · Answer 11

GilaMonster (6/28/2009)
Are things like IO latency (when fetching data off disk), lock wait times and the like relevant, or are you just looking for the amount of timethe queries spent executing (on the CPU)?

all factors you mentioned are relevant, not only CPU time

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Then you should use the execution time from statistics IO or the duration from profiler as it includes all the times that SQL had to wait while executing that query. Put the SQL Server on your local machine if you're concerned about network-related waits. That said, as far as I'm concerned, wait time due to network contention's just as relevant as wait time due to IO contention.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rw30 SSC Veteran Points: 213 More actions · Answer 13

rw30

SSC Veteran

Points: 213

June 28, 2009 at 2:58 pm

#1016563

ok, thx for your help Gail :kiss:

server side [?] query execution time

Cookies on SQLServerCentral