Hi,

I use sp_who to look at how many connections are made to our production
server. Normally there are about 300 connections, but sometimes the
connections will jump up rapidly to more than 1000 for several minutes, then
gradually come down to normal again. I need to investigate why this happens,
can someone give me directions on how-to? We are using SQL Server 2005.

TIA

> I use sp_who to look at how many connections are made to our production
> server. Normally there are about 300 connections, but sometimes the
> connections will jump up rapidly to more than 1000 for several minutes,
> then gradually come down to normal again. I need to investigate why this
> happens, can someone give me directions on how-to? We are using SQL Server
> 2005.

These symptoms could be due to long-running queries, perhaps due to
blocking. Do you see blocking in sp_who when the number of connections
jumps?

You might try a Profiler trace of login/logout events and also include batch
and rpc completed events with a duration filter >= 1000. This might show
the correlation between the connection spike and the activity at the time.

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Danny Ni" <dndn@yahoo.com> wrote in message
news:Oif8L$84IHA.4988@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> I use sp_who to look at how many connections are made to our production
> server. Normally there are about 300 connections, but sometimes the
> connections will jump up rapidly to more than 1000 for several minutes,
> then gradually come down to normal again. I need to investigate why this
> happens, can someone give me directions on how-to? We are using SQL Server
> 2005.
>
> TIA
>

The first easy step beyond sp_who is to use sp_who2, which provides a
bit more information. For greater detail, back in SQL Server 2000 and
earlier I would have told you to query master.dbo.sysprocesses. Now
in 2005 we have three system views that expand on this:
sys.dm_exec_connections, sys.dm_exec_sessions and
sys.dm_exec_requests. Look them up in the Books On Line and query
them to find out about the connections - who is connecting, from what
machine, running what queries, etc.

Roy Harvey
Beacon Falls, CT

On Fri, 11 Jul 2008 20:31:44 -0700, "Danny Ni" <dndn@yahoo.com> wrote:

>Hi,
>
>I use sp_who to look at how many connections are made to our production
>server. Normally there are about 300 connections, but sometimes the
>connections will jump up rapidly to more than 1000 for several minutes, then
>gradually come down to normal again. I need to investigate why this happens,
>can someone give me directions on how-to? We are using SQL Server 2005.
>
>TIA
>