Sometimes performance problems with MSDE

Sometimes performance problems with MSDE

Post by Jan Schmit » Fri, 17 Jun 2005 23:52:16


Hi,

i've a MFC-Application accessing a sql-server using ADO. Everything works
fine.
Now the same application accesses a MSDE with a smaller database and only
one user at one machine. The Application opens only one ADO-Connection to
the database. Most time evertything works fine. But sometimes, querys run
very slowly. The profiler tells me: The same query that needs about 30
"READS" for a sp_cursorfetch sometimes need over 3000 "READS".
What's the problem here? Why is the same query not needing the same count of
logical reads on the database?Any hints?

Greetings...

Jan
 
 
 

Sometimes performance problems with MSDE

Post by Andrea Mon » Sat, 18 Jun 2005 00:41:24

hi Jan,


please have a look at this great article by Andrew J. Kelly (SQL Server MVP)
about parameter sniffing at
http://www.yqcomputer.com/

--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.yqcomputer.com/ ://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

 
 
 

Sometimes performance problems with MSDE

Post by Jan Schmit » Sat, 18 Jun 2005 16:33:44


"Andrea Montanari" < XXXX@XXXXX.COM > schrieb im Newsbeitrag


MVP)
http://www.yqcomputer.com/

Hi Andrea,
thanks for this great link, I think it will help me with some other problems
and optimizing my SPs. But in this case the same statement, sent from the
same frontend application, need sometimes round about 200ms, sometimes 5
Secs. In this part of the application I don't use my own Stored-Procs, all
Values in the Where-Clause are hardcoded - somthing stupid simple like:
"select * from v_myview where col12 like '%test%' or col13 like '%test%"

Greetings...

Jan Schmitz