January 23, 2002 at 9:59 am
Hi,
One of my user complained about slow response time of her application. Then I started sql profiler to check how much time each query is taking. while checking i found strange thing that.
there is a query
Select * from dbo.customer
It took duration 4600 through application Then same query i ran from query analyser it took 50450 duration.
why that much differnce.this query is running faster through application than Query Analyser. Being as DBA how can i know what is ecact duration for that query.
Thanks
January 23, 2002 at 10:22 am
There are a several possibilities which could explain the difference in query time (and they aren't limited to these examples):
A) One query was blocked by an exclusive lock on the data while the other was not.
B) One client isn't able to receive data as fast as the other client (e.g.,One client is on a 10Mb connection, the other on a 100Mb connection).
C) One query went through when there was a light load on the server, the other went through when there was an extremely heavy load.
For locking, you can run sp_who or sp_who2 to see blocking. sp_lock will return what resources are being locked.
For the speed of the connection, that you'll probably have to verify with the network folks, unless your on a 10Mb hub and the other is not or something of that sort.
For the third, if you are monitoring processor and memory utilization on the server, you might be able to compare the times and see if there's a match.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 23, 2002 at 10:45 am
I was the only person on the total server when i ran the quey in query analyser.Does this query analyser is goodenough as ado/rdo connected application or is QA slow?.
January 23, 2002 at 10:57 am
Roughly the same connection, but the rendering code may be different. QA builds a grid and formats from the results, which can be slow for large result sets.
Steve Jones
January 23, 2002 at 12:39 pm
Was that the entire SELECT statement?
My understanding is that clauses, such as TOP, SET ROWCOUNT, etc...., can have different effects on how the SQL Server engine processes information, therefore affecting performance. Perhaps there are similar statements later in the query?
Comments, additional information? I'm learning SQL, so this is a great forum and question for me to consider. Thanks.
cabby2583
Always Learn!
January 23, 2002 at 12:43 pm
Were there other processes going on such as backups being run, etc? If so, was some of this backup traffic impacting the network (is the network switched)?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 23, 2002 at 4:59 pm
Dont believe what profiler tells you, full stop! 🙂
Sorry, but I have never come accross any figures in profilers duration column that is even half close to the truth. I had one the other day, a SQL statement in profiler was recorded as 10sec and in Query analyser completed its execution in 5! what the!
I am running more tests today If the friggen tape drive works in dev.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply