My App temporary freezes with SQL 2005 but not with SQL 2000
Hi Everybody,
I am having a very weird problem, and I was wondering if you have any idea about what I could do to fix this.
In a nutshell:
* I have this application that uses ADODB and ODBC to connect to an SQL database.
* We have two installations: one running SQL 2000 sp 3, and the other SQL 2005 sp 1.
* The one that runs on SQL 2000, my app performs 8,500 transactions a day with no problem.
* The one that runs on SQL 2005, my app performs 200 transactions a day, and goes down at least once a day.
Trying to figure this out, I noticed that, from time to time, when working with SQL 2005, the application kind of freezes when it tries to read or write to the database, and after a while (say 20 seconds or more), it continues as if nothing had happened. Trying to debug this issue, I noticed that this wait time period is ended if I close one of the other open connections that I have (say, I close the query analyzer)
As people may say, ADODB and ODBC should no longer be used... however, I have noticed this same kind of behavior with other tools, like the query analyzer from the SQL 2000 client tools when connecting to the SQL 2005 server.
I suspected a Licensing issue... however, I haven't been able to replicate this problem by opening several query analyzer instances.
Any help is greatly appreciated!!!!!
Best Regards,
Manuel
[1438 byte] By [
Tarhik] at [2008-1-4]
You have estimate reason of these freezes:
It can be:
Server hardware bottleneck:
a) CPU,
b) Memory
c) IO
d) Network
And long locks (or waits) followed by bottleneck
So you can use performance monitor and standart sql server counters like buffer pool cache hit ratio, or upgrade your sql server to sp2, and install performance dashboard - which can produce detailed reports about all aspects for your instance.
Hi Alexandr,
Thank you very much for your suggestions. I was unable to make an analysis of the situation, but apparently I'm on the clear. The performance of the server as a whole kept degradating every day a little more, so I came to the conclusion that it was not my application that was causing the problem. The system administrator is now looking for alternate solutions to get the performance back, so he installed the SQL 2005 Service Pack 2.
The problem is serious. According to what he told me, everything was well when working with SQL 2000. Then he migrated to SQL 2005 along with a new patch for his ERP system, and that's when the nightmare started. My application is in the middle, so I was the first one to put on the stand.
I already suggested getting this Database Certified Consultant that I know that made the other site with 8,500 transactions a day to work like clockwork. Since he charges by the hour, my suggestion got rejected. So I'm going to suggest to invest in the current hardware, specifically changing the network so the three main servers would be on the same 1Gbps network hub, as well as changing the database server to this Dell PowerEdge 6850 with tons of Ram (Long Live Dell!!!!). I'm quite sure that they will call this Database Certified Consultant that I know instead.
Thanks for the info!!!
Best Regards,
Manuel
.