I have a web service application that connects to an MS SQL 2005 to retrieve data. I have the same web service installed on 2 different servers.
On one of the servers, the response is instantaneous and on the other server, it takes up to 3sec. I have checked the settings on both servers and they are identical. In fact the fast server is only a P3 800Mhz and the slow server is a duo core. The database sits on another server which is duo core.
I ran SQL Profiler to find out what is happening and discovered that the fast server logins in and keep the connection for subsequent queries and hence all is superfast. The slow server however closes the connection immediately after executing eachstore procedure causing a major delay.
I have installed a 3rd server vanila server and having the same problem. How do I make the iis stop closing the connection? all the web service are running the same code and I cannot see any configuration difference. The 3rd server I installed is not a member of the domain but the other 2 are member servers in the domain.
Result from SQL Prefiler
FAST RESPONSE:
Audit login: -- network protocol: TCP/IP
RPC Completed: exec mySP
RPC Completed: exec sp_reset_connection
RPC Completed: exec myotherSP
RPC Completed: exec sp_reset_connection
RPC Completed: exec And_Another_SP
Audit Logout (5 min)
SLOW RESPONSE
Audit login: -- network protocol: TCP/IP
RPC Completed: exec mySP
Audit Logout (Instantly)
Audit login: -- network protocol: TCP/IP
RPC Completed: exec myotherSP
Audit Logout (Instantly)
Audit login: -- network protocol: TCP/IP
RPC Completed: exec And_Another_SP
Audit Logout (Instantly)
I think it is the login/logout that is causing the slowness because the logout happens instantly after the sp has been executed whereas in the case of the faster connection, the logout happens after about 5mins giving all queries instant access to the server. I hooked up performance monitor as well to see the connections and it points to the connection going up and down instantly for the slow connection.
I thought ADO.net handles the pool automatically. It beats me that the brand new install will have the same problem.
Help please anyone.
On one of the servers, the response is instantaneous and on the other server, it takes up to 3sec. I have checked the settings on both servers and they are identical. In fact the fast server is only a P3 800Mhz and the slow server is a duo core. The database sits on another server which is duo core.
I ran SQL Profiler to find out what is happening and discovered that the fast server logins in and keep the connection for subsequent queries and hence all is superfast. The slow server however closes the connection immediately after executing eachstore procedure causing a major delay.
I have installed a 3rd server vanila server and having the same problem. How do I make the iis stop closing the connection? all the web service are running the same code and I cannot see any configuration difference. The 3rd server I installed is not a member of the domain but the other 2 are member servers in the domain.
Result from SQL Prefiler
FAST RESPONSE:
Audit login: -- network protocol: TCP/IP
RPC Completed: exec mySP
RPC Completed: exec sp_reset_connection
RPC Completed: exec myotherSP
RPC Completed: exec sp_reset_connection
RPC Completed: exec And_Another_SP
Audit Logout (5 min)
SLOW RESPONSE
Audit login: -- network protocol: TCP/IP
RPC Completed: exec mySP
Audit Logout (Instantly)
Audit login: -- network protocol: TCP/IP
RPC Completed: exec myotherSP
Audit Logout (Instantly)
Audit login: -- network protocol: TCP/IP
RPC Completed: exec And_Another_SP
Audit Logout (Instantly)
I think it is the login/logout that is causing the slowness because the logout happens instantly after the sp has been executed whereas in the case of the faster connection, the logout happens after about 5mins giving all queries instant access to the server. I hooked up performance monitor as well to see the connections and it points to the connection going up and down instantly for the slow connection.
I thought ADO.net handles the pool automatically. It beats me that the brand new install will have the same problem.
Help please anyone.