July 29, 2008 at 12:36 pm
Hi I was wondering if anyone knows how to trace and determine if connection pooling is happening on the DB or if the DB is assigning different connections each time.
Right now we have a visual basic program connecting through ADO to SQL server which the application uses a super user (SA) to login and connect where multiple users on multiple machines connect the same way each time.
July 30, 2008 at 10:42 am
How about the Profiler in SQL Server? We can trace login name and spid there.
July 30, 2008 at 11:28 am
I was able to trace it through the profiler... just the question is... how do you determine if connection pooling is working or not? Right now each query from the app will open a connection and close the connection so I see a lot of the same spid's all over the trace
July 30, 2008 at 1:59 pm
Look for a bunch of exec sp_reset_connection calls - a pooled connection will call this proc when it wants to reset a connection pulled from the pool. If you are using .Net, you can also look at some of the perfmon counters from the client side, but I haven't been able to really figure out how reliable those are (maybe someone else has more experience there). In 2K5 you can use the event subclass in your profile trace to see if it is pooled (this was the QOD for 11/5/07), but I'm guessing that won't work for you since you are in the SQL7/2000 forum. 🙁 Sorry!
I just re-read your post... Connection pooling happens on the client side, not on the server, so if you have multiple clients (machines) hitting the server, they won't pool amongst themselves. Pooling will step in when you have multiple connections coming from a single client. Think of a webserver hitting a database - it will open a connection and re-use it for different requests (even different pages if configured correctly) without the overhead of having to open and close a connection each time. Since you have multiple machines, you can get connections pooled from within each one, but they won't share a pool with each other. For example, MachineA will pool it's connections together, but it won't be sharing a pool with MachineB.
Oh - and if you can, get them an account besides the SA account, and lock it down to the access it needs to have. It might save some headaches later.
Hope this helps!
Chad
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply