SQL connection pool and logon / logoff

Question

When I view my application using SQL Server Profiler, I see many audit and audit messages to connect to the same database. I wonder if this indicates that something is wrong with my connection pool? The reason I am asking is because I found this in the MSDN documentation regarding pooling:

Login and logout events will not be raised on the server when a connection is retrieved from the connection pool. This is because the connection does not actually close when it returns to the connection pool. For more information, see Logging on to the Audit Event and Logout Event Class in SQL Server Books Online.

http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

Also, does anyone have any tips on determining the effectiveness of the connection pool for a given SQL server? I have many databases on one server, and I know that this can have a huge impact, but I'm wondering if there is an easy way to get the performance indicators of my connection pool. Thanks in advance!

Answer 1

Remember that connections are combined into a connection string. If you have many databases and connect using many connections, your application will create a new connection if the correct connection string does not exist. Then it will merge this connection and, if the pool is full, hit the existing connection. By default, Max Pool Size is 100 connections, so if you regularly skip over more than 100 databases, you close and open connections all the time.

This is not ideal, but you can solve this problem by always connecting to the same database (one connection string), and then switch to the db context "USE [DBName]". There are disadvantages:

• You lose the ability to specify a user / password for each connection string (the user of your application requires permission for all databases).
• Your SQL is becoming more complex (especially if you use ready-made ORMs or stored procedures).

You can experiment with increasing the Max Pool Size if the number of your databases is not large. Otherwise, if some databases are often used and others not, you can disable them on infrequent dbs. Both elements are configured using connectionstring .

In terms of metrics, tracking SQL Server login and logout events is a good start. If your application blends well, you shouldn't see a lot of them.

Answer 2

Although the MSDN article states that the event will be raised only for unused connections, the SQL Server documentation contradicts this statement:

"The Audit Login event class indicates that the user has successfully logged on to Microsoft SQL Server. Events in this class are triggered by new connections or connections that are reused from the connection pool."

The best way to measure the effectiveness of a join is to collect the time spent connecting with and without the join. When using the pool, you should see that the first connection is slow, and the subsequent ones are very fast. Without joining, each connection will take a long time.

If you want to monitor an Audit Logon event, you can use the EventSubClass data column to indicate whether the login is a reusable connection or a new connection. The value will be 1 for the real connection and 2 for the reused connection from pool.application.