Connection pooling question.

[Frank Rizzo]

Hello,

My app opens and closes connections fairly often, sometimes on different
threads. How can I be certain that the connections actually use the
connection pool?

I cranked up the SQL Profiler and I keep seeing sp_reset_connection
every 5-10 lines or so. What does that mean?

Thanks

 

[William \(Bill\) Vaughn]

I wish you could read the chapter on connecting in my new book... it
explains it all.
As long as you're using the same process, the same connection string and the
same transaction you should be sharing the same pool.
Each time the connection is opened, the connection is reset--that's normal
and by default behavior. The reset makes sure you have a "virgin" connection
to work with.
You can try looking at the CLR performance counters using PerfMon, but these
are notoriously unreliable.
ADO.NET 2.0 has better support for all of this.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Frank Rizzo]

I wish you could read the chapter on connecting in my new book... it
explains it all.

I have all your books, but not on me. I'll check it out tonight.
Which one are you referring to?

As long as you're using the same process, the same connection string and the
same transaction you should be sharing the same pool.
Each time the connection is opened, the connection is reset--that's normal
and by default behavior. The reset makes sure you have a "virgin" connection
to work with.

So then the behaviour I am experiencing means that my connections are
using the pool?

You can try looking at the CLR performance counters using PerfMon, but these
are notoriously unreliable.

Yep, I found this out the hard way.

 

[William \(Bill\) Vaughn]

The one I'm writing now...

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Frank Rizzo]

The one I'm writing now...

Why don't you post it here. Will review and edit here for you. You
know, comb for errors.

 

[William \(Bill\) Vaughn]

Ah, right.
But seriously, I'll let you know if I need some additional eyes a bit later
in the process if you (or anyone) wants to take a look.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Frank Rizzo]

Ah, right.
But seriously, I'll let you know if I need some additional eyes a bit later
in the process if you (or anyone) wants to take a look.

Fantastic, look forward to it.