Strange Performance Problem  
Author Message
zanziba





PostPosted: Thu Jun 03 22:44:32 CDT 2004 Top

SQL Server >> Strange Performance Problem

We are experiencing a very strange performance problem. Stored Procs that
are run through ADO are taking 30 seconds to run, while they run in less
than a second when run from query analyzer. This behavior was confirmed
using Profiler. The duration was 30 seconds from ADO, yet I can run the
same stored proc in less than a second from QA or EM.

This started this afternoon and was observed on several different servers.

Any ideas?

SQL Server131  
 
 
Aaron





PostPosted: Thu Jun 03 22:44:32 CDT 2004 Top

SQL Server >> Strange Performance Problem Are you using the exact same parameters? This can often happen due to
"parameter sniffing" and / or a bad query plan stored in the cache, or out
of date statistics.

See http://tinyurl.com/34flx

If you don't trust tinyurl, reconstruct this link:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=OfT1y1EHDHA.212%40cpmsftngxa06.phx.gbl&rnum=2&prev=/groups%3Fq%3Dparameter%2Bsniffing%2BDBCC%2BFREEPROCCACHE%26ie%3DUTF-8%26hl%3Den

--
http://www.aspfaq.com/
(Reverse address to reply.)



> We are experiencing a very strange performance problem. Stored Procs that
> are run through ADO are taking 30 seconds to run, while they run in less
> than a second when run from query analyzer. This behavior was confirmed
> using Profiler. The duration was 30 seconds from ADO, yet I can run the
> same stored proc in less than a second from QA or EM.
>
> This started this afternoon and was observed on several different servers.
>
> Any ideas?
>
>
>


 
 
Peter





PostPosted: Thu Jun 03 23:04:34 CDT 2004 Top

SQL Server >> Strange Performance Problem Use SQL Profiler to trace the actual execution plan that is used for the
stored procedure, from both ADO and QA. Might reveal something.


Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup




> We are experiencing a very strange performance problem. Stored Procs that
> are run through ADO are taking 30 seconds to run, while they run in less
> than a second when run from query analyzer. This behavior was confirmed
> using Profiler. The duration was 30 seconds from ADO, yet I can run the
> same stored proc in less than a second from QA or EM.
>
> This started this afternoon and was observed on several different servers.
>
> Any ideas?
>
>
>