slow remote desktop and query on 64bit sql2005 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

slow remote desktop and query on 64bit sql2005

Hi, We are moving from SQL Server 2000 to SQL Server 2005. Wondering if you have the same problem or can share some of your experience to solve some issue. We have brand new clustered database servers which have Quad Duo CPUs and 8GB memory each running SQL Server 2005 64bit Enterprise edition with SP1 applied and on Win2K3 server x64 Enterprise edition. Only database engine component are installed for now. We have some strange behaviors from time to time that :
When we have some query running from some local PC Management Studio, the remote desktop control is either takes a long time to connect or very slow to react the requests. When it happened, sometime we can not connect to the database server from Management Studio with the connection timeout which was OK couple minutes ago. Interestingly at those moments, the query is still running , no process blocking found, very light CPU usage and low disk I/O detected from the monitoring tool and it looks like the SQL server is not busy at all. No 3rd party/identifiable process can be found running on the database server also, the ‘system idle process’ is the main one with CPU count. And overall the query just take very long than expected to finish. We could have very normal days for a while and randomly we will encountered the symptom above. Each time query we ran is different. Some are light and some are heavy. The query we recently ran was to create indexes on many big partitioned tables. Thanks in advance. Eugene