Pooling

Im using SQL Express and ADO.

The connection string im using is

"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=mydatabase;Data Source=TESTBOX\SQLEXPRESS"

The problem i have is there are lots of alerts generated in the security evenlog which are login events almost every second. Our app has many different threads and processes accesing the same database in a Queue fashion. What im concerned by looking at these logs and also looking at the connections opened is that i see all the connections open and close every second and so no connection pooling is happening.

Am i missing something ?

The connection pool is security context specific. When you are using integrated security, each individual user will have their own connection pool.

It is not unusual for a heavily used production server to generate hundreds of login alerts (maybe even thousands) per second. If appropriate for your situation, you may choose to change the alerts to log only Login Failures (and ignore Login successes. )

|||

Thanks for the reply. The services are the ones doing all the calls into SQL. It is always only one user ( The service is running as this user) And i dont see any connection being active for more than a few ms. And i think that means that the connection pool is not even getting created . no ?

but if connection pooling is happening then how can see thousands of logins ? is it not supposed to use the existing connection in the pool and not relogin to SQL ?

Arnie Rowland wrote:

The connection pool is security context specific. When you are using integrated security, each individual user will have their own connection pool.

It is not unusual for a heavily used production server to generate hundreds of login alerts (maybe even thousands) per second. If appropriate for your situation, you may choose to change the alerts to log only Login Failures (and ignore Login successes. )

|||

Having the Login Success/Failure events write to Event Log has little direct relationship with the connection. It has everything to do with security.

One connection pool will still generate an Event Log entry every time there is a query from any user. Every incoming query will have the users' access and/or permissions validated.

If you don't want to have Login Successes filling up your Event Log, then you can change that to Log only Failures. -Or not at all.

|||

ok. but if i set the username password as userid=sa , password then i dont see any events.

btw how do i even see if connection pool is being created or not ?

|||

Take look at this site

http://support.microsoft.com/default.aspx/kb/166083

|||

To determine if connection pooling is happening in the ADO/OLEDB you can enable tracing using the following whitepaper: http://msdn2.microsoft.com/en-us/library/aa964124.aspx.From your connection string you’re using SQLOLEDB which will require that you’re at least on MDAC 2.8 SP2 to get complete tracing.

Hope this helps.

|||Use SQL Profiler and see if you get sp_reset_connection calls. If you do, then you have connection pooling working, otherwise you will see new connections.

You might be destroying the pool if you do not hold onto at least one connection.
There is a performance optimization for COM+ and IIS scenarios, but for a standalone application make sure you do not release everything. For instance in the following ADO code:

For i = 1 To 100

c.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;OLE DB Services=-1; Data Source=.; Initial Catalog=pubs;"

Set r = New ADODB.Recordset

r.Open "SELECT * FROM Authors", c

Set r = Nothing

c.Close

Set c = Nothing

Next I

As soon as you set c to Nothing and don't have other active connections you are going to destroy the whole oledb session pool.