Prev: SSRS 2008
Next: How to Convert Char to Datetime
From: Marcus de Leon on 6 Apr 2010 10:41
Hi,

I am connecting to SQL Server 2005 via .NET application using
connection pooling.

My question is when looking at my connection in sys.sysprocesses, my
login_time is always changing when I run a query, but the spid stays
the same. Is this correct if pooling is used? I'm wondering if my
pooling is working correctly even when the login_time changes. I would
expect the login_time to stay constant from the initial login until
the connection pool timed out.

Thanks in Advance.

Marcus de Leon
From: Bob Barrows on 6 Apr 2010 10:54
Marcus de Leon wrote:
> Hi,
>
> I am connecting to SQL Server 2005 via .NET application using
> connection pooling.
>
> My question is when looking at my connection in sys.sysprocesses, my
> login_time is always changing when I run a query, but the spid stays
> the same. Is this correct if pooling is used? I'm wondering if my
> pooling is working correctly even when the login_time changes. I would
> expect the login_time to stay constant from the initial login until
> the connection pool timed out.
>
Yes. The current user of the connection has to log out before the
connection can be reused by subsequent users. Each subsequent user uses
the connection to log in.
--
HTH,
Bob Barrows


From: Marcus de Leon on 6 Apr 2010 11:17
On Apr 6, 12:54 pm, "Bob Barrows" <reb01...(a)NOyahoo.SPAMcom> wrote:
> Marcus de Leon wrote:
> > Hi,
>
> > I am connecting to SQL Server 2005 via .NET application using
> > connection pooling.
>
> > My question is when looking at my connection in sys.sysprocesses, my
> > login_time is always changing when I run a query, but the spid stays
> > the same. Is this correct if pooling is used? I'm wondering if my
> > pooling is working correctly even when the login_time changes. I would
> > expect the login_time to stay constant from the initial login until
> > the connection pool timed out.
>
> Yes. The current user of the connection has to log out before the
> connection can be reused by subsequent users. Each subsequent user uses
> the connection to log in.
> --
> HTH,
> Bob Barrows

Oh ok, so that is the correct way and this is efficient?
From: jgurgul on 6 Apr 2010 12:10
Hi,

I believe the following article is helpful as it explains how and when
connections are used and how they are pooled.

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

you could check to see if the connection is being pooled by capturing the
logon event.

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


login_time is when the session was established.


Jon

"Marcus de Leon" wrote:

> Hi,
>
> I am connecting to SQL Server 2005 via .NET application using
> connection pooling.
>
> My question is when looking at my connection in sys.sysprocesses, my
> login_time is always changing when I run a query, but the spid stays
> the same. Is this correct if pooling is used? I'm wondering if my
> pooling is working correctly even when the login_time changes. I would
> expect the login_time to stay constant from the initial login until
> the connection pool timed out.
>
> Thanks in Advance.
>
> Marcus de Leon
> .
>
From: Bob Barrows on 6 Apr 2010 13:06
Marcus de Leon wrote:
> On Apr 6, 12:54 pm, "Bob Barrows" <reb01...(a)NOyahoo.SPAMcom> wrote:
>> Marcus de Leon wrote:
>>> Hi,
>>
>>> I am connecting to SQL Server 2005 via .NET application using
>>> connection pooling.
>>
>>> My question is when looking at my connection in sys.sysprocesses, my
>>> login_time is always changing when I run a query, but the spid stays
>>> the same. Is this correct if pooling is used? I'm wondering if my
>>> pooling is working correctly even when the login_time changes. I
>>> would expect the login_time to stay constant from the initial login
>>> until the connection pool timed out.
>>
>> Yes. The current user of the connection has to log out before the
>> connection can be reused by subsequent users. Each subsequent user
>> uses the connection to log in.
>
> Oh ok, so that is the correct way and this is efficient?

Yes. Re-using connections is quite a bit more efficient than making each
process spawn a new connection. Each connection consumes memory on the
sql server. Minimizing the number of connections via pooling is a good
thing.
http://support.microsoft.com/?kbid=328476

--
HTH,
Bob Barrows


 | 
Pages: 1
Prev: SSRS 2008
Next: How to Convert Char to Datetime