SQL Server High Number of User Connections found on PERFMON, how to resolve it?
I was working with one of the user project to monitor the Performance Audit of their SQL Server estate. One SQL instance among the SQL farm is very important to their day-to-day business and cannot sustain even 5 minutes of downtime which is termed as 24/7 kind of application.
During the troubleshooting exercise I have noticed a peculiar issue that while number of connection value in master.dbo.sysprocesses (or sys.processes) is relatively showing nearly 100 user connections, where as while collecting the statistics using PERFMON (SYSMON) tool or with DMV sys.dm_os_performance_counters it shows the counter value as over 1400. Further to this to ensure the connections value is correct I have executed the command netstate -ano from command prompt, a little about netstat ("Netstat" allows anyone to instantly see what current Internet connections and listening ports any system has open and operating. Mastering the power of this little-known command will greatly empower any security-conscious computer user).
So whenever you have the opportunity to identify the number of user connections always excute as follows:
--SQL 2000
select count(*) from master..sysprocesses where spid>50
--SQL 2005
SELECT * FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:General Statistics'
I have seen many users depending upon the Task Manager on the server assuming that something is really causing issue that is due to SQL Server usage. Hold on your thoughts and before confirming your thoughts it is better to collect correct statistics using DMVs (SQL 2005) or PERFMON (pre SQL 2005) methods for optimum values.
Further to this I have found the following error messages:
Logon Login failed for user LoginName'. [CLIENT: IPAddress]
Logon Error: 18456, Severity: 14, State: 23.
spid 1371 The client was unable to reuse a session with SPID SPID, which had been reset for connection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Also when the high user connections value is shown I can observe high CPU usage and frquent context switches from Operating System (referring to Task Manager for highlights). The root cause of this issue is when SQL Server handles many concurrent connections on a computer that has many processors installed. Additionally, a time-out may occur when you run a query. So to proceed further you should determine exactly what those user connections are performing or do they need to be open on SQL Server side whereby resources such as memory is occupied and will not be disconnected unless it is initiated by the user or you kill it. Also it is best to identify the total amount of memory currently allocated for connection by using DBCC MEMORYSTATUS statement.
Finally we have identified the root cause of the issue is due to the activity on the database causing heavy usage of MARS and to talk about this refer to Multiple Active Result Sets (MARS) in SQL Server 2005, Using Multiple Active Result Sets (MARS) and this blog post http://blogs.msdn.com/angelsb/archive/2005/01/13/352718.aspx & http://blogs.msdn.com/dataaccess/archive/2005/08/02/446894.aspx links explains more about the root cause. To resolve further issues when you are using MARS type of activity review the content from this Improving-smart_client_performance event from Microsoft.