100% User Connection - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

100% User Connection

Author

Topic

rpc86
Posting Yak Master

200 Posts

Posted - 2004-12-29 : 03:13:47

Hi there,

Our SQL Server Performance Monitor shows that our user connection is 100%. Before, even the User Time use to climb to 100% but when we added Set CN = Nothing in VB6 for all open connections, then the User Time never reach 100% anymore. Our problem now is the User Connection that stays on 100% and our user experience slow-down in our system even in a very simple SELECT Top 10 statement.

What shall we do?

Thanks.

Kristen
Test

22859 Posts

Posted - 2004-12-29 : 04:24:26

Have you got lots and LOTS of users connected concurrently?


SELECT	cntr_value,
	[Name] = RTRIM(counter_name) + ' (' + RTRIM(object_name) + ')'
FROM	master..sysperfinfo PageSplitsSec (nolock)
WHERE	   (object_name = 'SQLServer:General Statistics' 
		AND counter_name = 'User Connections' 
		AND instance_name = '')

Kristen

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-12-29 : 13:19:00

It's not 100%. It's 100 User connections. 100 connection's isn't terrible, but. As far as disconnecting your VB6 stuff, post some code. I can give you some pointers on the best way to disconnect things.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Kristen
Test

22859 Posts

Posted - 2004-12-30 : 02:06:16

I expect its more than 100, isn't it Michael? (I'm thinking that 100 is the "maximum" in performance monitor, and then the scale needs to be changed to see the actual number)

Kristen

rpc86
Posting Yak Master

200 Posts

Posted - 2005-01-02 : 20:15:49

Here is the sample code requested by MichaelP:


Public Function CheckAssignment_CreateCancelCheck(sCvNumber_OLD As String, sCvNumber_NEW As String, sBankCode As String, sCheckNumber As String)
Set cn = ADOConnect(sDBServer, sDBName)
cn.Execute ("pr_CheckAssignment_CreateCancelCheck '" & sCvNumber_OLD & "','" & sCvNumber_NEW & "','" & sBankCode & "','" & sCheckNumber & "'")
cn.Close
Set cn = Nothing
End Function

-------------------------
Public Function ADOConnect(s_DBServer As String, s_DBName As String) As ADODB.Connection
  On Error GoTo ErrorTrap
  Set cn = Nothing
  Set ADOConnect = Nothing
  Dim cnNew As New ADODB.Connection
  cnNew.CursorLocation = adUseClient
' cnNew.ConnectionTimeout = 40
  cnNew.CommandTimeout = 0
  cnNew.Provider = "SQLOLEDB"
  cnNew.Open "Server=" & s_DBServer & ";Database=" & s_DBName, "SA", ""
  If cnNew.State = adStateClosed Then
    GoTo ErrorTrap
  Else
    Set ADOConnect = cnNew
  End If
Exit Function
ErrorTrap:
  Set ADOConnect = Nothing
  ErrorHandler "E0001"
End Function

Please help me before I get terminated.

Thanks.

Kristen
Test

22859 Posts

Posted - 2005-01-03 : 08:01:41

Can you run my code to check User Count [above] from Query Analyser, or similar, please?

Kristen

rpc86
Posting Yak Master

200 Posts

Posted - 2005-01-03 : 19:30:56

I ran your code and it show more than 100 users connected

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-03 : 20:22:23

The usual cause of this is maintaining references for the connection in the app.
This could be a reference held in a global/public or the activeconnection in a resultset or command.

I implement the database access layer in a separate class which returns a disconnected resultset so that the developers don't have the option of holding connections.

see
http://www.mindsdoor.net/VB/VB6DataAccessLayer.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rpc86
Posting Yak Master

200 Posts

Posted - 2005-01-07 : 02:19:51

Our User Connection in Performance Monitor shows more than 100 connections. I use the following s/p to determine how many user connection are currently active.


SELECT	cntr_value,
	[Name] = RTRIM(counter_name) + ' (' + RTRIM(object_name) + ')'
FROM	master..sysperfinfo PageSplitsSec (nolock)
WHERE	   (object_name = 'SQLServer:General Statistics' 
		AND counter_name = 'User Connections' 
		AND instance_name = '')

cntr_value  Name
---------------------------------------------------------- 
352         User Connections (SQLServer:General Statistics)

(1 row(s) affected)

Do number of user connections contribute slow-down in our server?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-07 : 02:31:56

i'm not sure about YOUR SERVER but 100 users don't really slow down our servers

--edited for clarity, yap referring to connections (sorry), we usually get an average of 300 connections during off peak hours and this maxes up to 700 or more during peak hours

--------------------
keeping it simple...

rpc86
Posting Yak Master

200 Posts

Posted - 2005-01-07 : 02:54:17

Actually its not the Users. Its the Connection itself.

Kristen
Test

22859 Posts

Posted - 2005-01-08 : 06:30:42

I would get pretty nervous about 352 connections.

You probably need to monitor it over time to see if it is changing.

The other things we look at is the CPU_BUSY setting. When this, or the number of connections, goes over some threshold [set according to server hardware/performance] we stop accepting new sessions [in our application] - this allows all the currently connected users to finish-up.

We have a scheduled SProc that runs each minute and records the info. and sets a "flag" when the server is too-busy, which is then available to the routine which allocates a new session.

The data we capture is as follows (the slightly convoluted logic is because other, more "obvious", methods we tried imposed too much overhead):


-- Local variables
DECLARE	@PageSplitsSec int,
	@BufferCacheHitRatio int,
	@TransactionsSec int,
	@UserConnections int,
	@DeadlocksSec int,
	@TotalServerMemory int,
	@TargetServerMemory int

SELECT	@PageSplitsSec = CASE WHEN (object_name = 'SQLServer:Access Methods' 
		AND counter_name = 'Page Splits/sec' 
		AND instance_name = '') THEN cntr_value ELSE @PageSplitsSec END,
	@BufferCacheHitRatio = CASE WHEN (object_name = 'SQLServer:Buffer Manager' 
		AND counter_name = 'Buffer cache hit ratio' 
		AND instance_name = '') THEN cntr_value ELSE @BufferCacheHitRatio END,
	@TransactionsSec = CASE WHEN (object_name = 'SQLServer:Databases' 
		AND counter_name = 'Transactions/sec' 
		AND instance_name = '_Total') THEN cntr_value ELSE @TransactionsSec END,
	@UserConnections = CASE WHEN (object_name = 'SQLServer:General Statistics' 
		AND counter_name = 'User Connections' 
		AND instance_name = '') THEN cntr_value ELSE @UserConnections END,
	@DeadlocksSec = CASE WHEN (object_name = 'SQLServer:Locks' 
		AND counter_name = 'Number of Deadlocks/sec' 
		AND instance_name = '_Total') THEN cntr_value ELSE @DeadlocksSec END,
	@TotalServerMemory = CASE WHEN (object_name = 'SQLServer:Memory Manager' 
		AND counter_name = 'Total Server Memory (KB)' 
		AND instance_name = '') THEN cntr_value ELSE @TotalServerMemory END,
	@TargetServerMemory = CASE WHEN (object_name = 'SQLServer:Memory Manager' 
		AND counter_name = 'Target Server Memory(KB)' 
		AND instance_name = '') THEN cntr_value ELSE @TargetServerMemory END
FROM	master..sysperfinfo PageSplitsSec (nolock)
WHERE	   (object_name = 'SQLServer:Access Methods' 
		AND counter_name = 'Page Splits/sec' 
		AND instance_name = '')
	OR (object_name = 'SQLServer:Buffer Manager' 
		AND counter_name = 'Buffer cache hit ratio' 
		AND instance_name = '')
	OR (object_name = 'SQLServer:Databases' 
		AND counter_name = 'Transactions/sec' 
		AND instance_name = '_Total')
	OR (object_name = 'SQLServer:General Statistics' 
		AND counter_name = 'User Connections' 
		AND instance_name = '')
	OR (object_name = 'SQLServer:Locks' 
		AND counter_name = 'Number of Deadlocks/sec' 
		AND instance_name = '_Total')
	OR (object_name = 'SQLServer:Memory Manager' 
		AND counter_name = 'Total Server Memory (KB)' 
		AND instance_name = '')
	OR (object_name = 'SQLServer:Memory Manager' 
		AND counter_name = 'Target Server Memory(KB)' 
		AND instance_name = '')

	INSERT INTO dbo.MyCPU_BusyTable (DateAndTime, CPU_Busy, PageSplitsSec,
		BufferCacheHitRatio, TransactionsSec, UserConnections, DeadlocksSec,
		TotalServerMemory, TargetServerMemory)
	SELECT	GetDate(), 
		@@cpu_busy,
		@PageSplitsSec,
		@BufferCacheHitRatio,
		@TransactionsSec,
		@UserConnections,
		@DeadlocksSec,
		@TotalServerMemory,
		@TargetServerMemory

NOTE: Only CPU_Busy and UserConnections are actually used below, the others are "for monitoring only", so you could leave them off, or add others that you are interested in monitoring.

Then the code to FlagUp that the server is too busy is as follows:


-- Check if over "busy" threshold
DECLARE	@intCPUVector int,
	@intUserConnections int
SELECT	@intCPUVector = ((MAX(CPU_Busy) - MIN(CPU_Busy)) * 10000)
		/ DATEDIFF(Millisecond, MIN(DateAndTime), MAX(DateAndTime)),
	@intUserConnections = AVG(UserConnections)
FROM
(
	SELECT	TOP 5
		[ID],
		DateAndTime, 
		CPU_Busy,
		UserConnections
	FROM	dbo.MyCPU_BusyTable
	ORDER BY [ID] DESC
) X

IF (
	-- Set these limits as appropriate for your hardware
	   @intCPUVector > 2000
	OR @intUserConnections > 200
)
BEGIN
	-- Server Too Busy
	... code to handle too_busy scenario ...
	.. e.g. deny new sessions, or send alert to DBA ...
END

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources