October 5, 2012 at 1:10 pm
We have an OLTP database. It is not very well designed, and is hit by several different applications. One thing I'm wondering about, however, is the impact to performance if connection pooling is not handled well, and how I can tell. I created an event notification to log on login, and running for just half an hour (blocking began happening in the DB and i had to drop the queue) I got the following numbers:
From 17 App servers using IIS, a total of 1,610,187 logins.
There were 62 distinct hosts, 425 distinct logins, and another 708,985 logins seperate from the 17 servers above.
It seems like this may explain why I have to grant so much memory to the OS - 15GB on a 64GB system. Login memory is non-buffer-pool, correct?
My questions are:
is this typical
what kind of impact could it be causing
what do i tell the developers so they know where to look?
October 5, 2012 at 2:28 pm
What's the version of SQL server?
October 5, 2012 at 2:49 pm
The maximum concurrent connections is 32767 (its an integer) but due to connection pooling these tend to get recycled very rapidly.
You can monitor the number of concurrent connections using this query.
SELECT * FROM sys.dm_os_performance_counters dmv with(nolock) WHERE counter_name = 'User Connections'
October 5, 2012 at 2:52 pm
Rottengeek (10/5/2012)
One thing I'm wondering about, however, is the impact to performance if connection pooling is not handled well, and how I can tell. I created an event notification to log on login, and running for just half an hour (blocking began happening in the DB and i had to drop the queue) I got the following numbers:
The number of connections is unlikely to be the root cause of your blocking issue. Its the accessing/updating of the same resources in a single transaction. You will need to find what object locks are getting escalated and then establish what is using those objects.
EDIT- Sorry i read it as the problem was blocking.
Concurrent connections are not going to cause any issues by themselves.
October 6, 2012 at 4:18 am
It wasn't so much the connections, as the number of logins happening in such a short amount of time..it seems like a lot for only 427 users, over half an hour. We rarely see more than 50 active connections, but always at least 5000 sleeping.
It seems to me that there is something a tad off...l'llpost a spreadsheet with more detail, and the info on non buffer pool memory after coffee. 🙂
October 6, 2012 at 4:20 am
Oh - thought i included this : sql 2008 r2 sp1 enterprise.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply