Performance on MSSQL server
Posted on 2005-04-20
We are running SQL Server 2000 SP3A on a W2000 cluster (passive failover) on a LAN. Occasionally (every week) we have severe performance issues : The time to execute our main query rises from 300 ms to 4000-6000 ms (we build in a logging feature), and as sudden as it rises it can drop again to normal 300 ms response. We even did experience normal transaction responses from other clients using SQL Query with on the same query when our application did show 4000 ms responses) In the past we solved several problems (memory leak : SP3A, wrong execution path). The application is the only one on the cluster, and has only 5 clients using the database direct.
We also placed the application on a other database server (single non clustered) and the problem also occurred on that database machine. We can rule out virus updates, backup and so on, and the network is also good responding on times with high response times.
The last thing we tried is shutting down named pipes on the clients. Since then the problem didn’t occur anymore, but we are very sure its only a question off time.
We are convinced it must be a software problem, but we don’t know anymore where to look.