July 22, 2010 at 10:49 am
I have started working as a DBA with SQL Server 2008 in a small company. We have a lightly/moderately loaded database running SQL Server 2008. During normal operation, I am seeing about 4,000 logins per minute. Nearly all of these are for a couple of in-house .Net applications which use the SqlClient data provider. Is this volume normal? I have worked on other platforms and this seems way too 'chatty' to me. I have alway treated database connections as expensive and tried to minimize their use back when I was programming (with JDBC, ADO and ODBC). Has the cost of making database connections dropped to the point where making and dropping scores of connections per second is efficient?
The log also shows a couple dozen login names that have a few (1-4) connections during this time. This is what I was expecting to see. It is in accord with what I have seen on other databases (e.g. Sybase and PostgreSQL).
What have members with more experience in SQL Server 2008 think?
July 22, 2010 at 11:44 am
I am not sure if I understood your question correctly but here goes my 2 cents
I do not think it is SQL 2008 that is the root cause of high number of log ins and log outs. It is the application. Let us say for example in one page there is a need to get 5 different sets of data for the DB. After every retrieval of data they might do a log out. It is recommended to close the connection after getting the data. Some take it literally and close it after every operation.
I would say, open the connection at the beginning of the page, do all your DB operations and then close it.
-Roy
July 22, 2010 at 11:59 am
I agree that this is not a problem with the database server or its configuration. I am just surprised at this level of connections. I would expect to see connection pooling keeping the number of connections between one application and one database in the single digits or low double digits and to hold those connections for at least a few minutes. But clearly, that is not happening here. Perhaps creating connections is so cheap that it makes sense to close a connection after a single use. That would surprise me, but I have been surprised before.
I was hoping to hear from another DBA who has monitored logins who could share what they consider to be a normal number of logins per minute. My observations are not in agreement with my expectation, and I would be grateful to hear from others with more experience.
July 22, 2010 at 1:19 pm
4000 logins a minute for a small company sounds exhorbitant to me. How many connections on average do you have at any time? At my last job, a small-to-mid-size company, we had around 1000 to 1100 connections at any time. We might get 20,000 login events in a full day, certainly not in 5 minutes. And yes, this can be expensive overhead. Sounds like the app needs some fixing to me.
July 22, 2010 at 1:19 pm
I understand the number sounds high especially if you are a small shop.
I would suggest 2 things one on the IIS server go into perfmon and get a running count of anonymous and nonanonymous users as well as other IIS objects. For example, connecting a session to the Web server and viewing the Web Service\ Current Nonanonymous Users counter will give a current count of nonanonymous connections.
I'm not an IIS guy by a long shot but those where the counters that I saw when I looked on an IIS server.
At the same time start a server side trace and capture the activity for a 10 to 15 minute window, compare the 2.
This will allow you to look at your user base, and look at the code being executed agaisnt the database server.
even though SQL uses connection pooling it is possible that the code can alter the way that SQL connections are utilized.
This is an old but good read http://www.sql-server-performance.com/articles/per/connection_pooling_myths_p1.aspx it uses classic asp so i'm not sure how it would translate over to .net, but I would tend to agree that the code itself and perhaps some setting it is using are creating the aditional pools.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
August 3, 2010 at 9:05 am
This is my understanding: When a connection is opened, a single log in call is needed to establish a secure connection. As long as the connection remains in the pool, it will not require a second log in call. I equate the ADO Open method with log in and the ADO Close method with log out. Is this correct?
If Dispose is called, it is my understanding the the connection string is destroyed after the connection is closed. Is the connection then destroyed rather that returned to the pool?
August 3, 2010 at 4:10 pm
I looked at the number of logged events per login event.
With connection pooling, I would expect this to a large number - perhaps hundreds or thousands of events per login. The lowest reasonable value would be 4, for each connection, there would be one SQL:BatchStart, one SQL:BatchEnd and one logout.
So, this gives me a dimensionless metric: events/login
4 <=> ineffective connection pooling
larger numbers mean more effective pooling (but perhaps with wasted memory to hold the pooled connection).
I tried this on our database. There is a very active user that accounts for nearly all of the logins in my 30 minute test. This user has 5.11 events/login. There are other users that have over a thousand events/login.
Does anyone see an error in this logic?
August 15, 2010 at 5:19 pm
4000 is high ..where are the logins coming in from web pages ? Do you have to keep resetting the IIS Recycle. I thought there was a setting that you could do keep alive in registry - maybe this is so high that it is not destroying the sessions. I didn't think SQL kept them forever either . I would look for the keep alive setting it was a while ago that I had to research this but this may help you.
August 15, 2010 at 9:02 pm
robertfolkerts (7/22/2010)
I have started working as a DBA with SQL Server 2008 in a small company. We have a lightly/moderately loaded database running SQL Server 2008. During normal operation, I am seeing about 4,000 logins per minute. Nearly all of these are for a couple of in-house .Net applications which use the SqlClient data provider. Is this volume normal? I have worked on other platforms and this seems way too 'chatty' to me. I have alway treated database connections as expensive and tried to minimize their use back when I was programming (with JDBC, ADO and ODBC). Has the cost of making database connections dropped to the point where making and dropping scores of connections per second is efficient?The log also shows a couple dozen login names that have a few (1-4) connections during this time. This is what I was expecting to see. It is in accord with what I have seen on other databases (e.g. Sybase and PostgreSQL).
What have members with more experience in SQL Server 2008 think?
Sounds like a "bot" or two has your number. I saw similar activity for one of the better known travel sites... they were "screen scraper" bots in that case.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply