Here we are using some CRM based applications, which are linked to seperate databases say 10 different CRM applications & 10 different databases.
* The problem is when all the CRM applications are running and users are logged in and if someone makes a query from local machine or remote machine using Enterprise Manager or Query Analyser on one database say DB1 then all the users logged onto DB1 based CRM applications got hanged till the query is executing, all other CRM applications & databases are working fine at this moment. As soon as the query is completed everything works fine.
Proper indexing is there against the fields where we are making queries.
* Another thing is when all the CRM applications are running load on CPU is less say always less than 5%, but the physical memory available is constantly decreasing by a factor of say 20-50 KB per 2-3 seconds against available physical memory of 1GB. And SQL server is not releasing memory when all the users got logged out from the server, we have to restart the SQL server to get the RAM back.
I don't know if this will help, but there's a new network monitoring and flow analysis tool available, called AppDancer/FA. It won the Best of Interop at Networld+Interop in Network Management this past May. It allows you to look at the user transactions at the application level, rather than the data or network level, in real-time. There is a 14 day free trial available on their website, www.appdancer.com, so it might be worth a try in order to solve your problem. Hope this helps!