newbee needs help with trouble-shoot sql server

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Danny Ni - 12 Jul 2008 04:31 GMT

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

Reply to this Message

Dan Guzman - 12 Jul 2008 16:29 GMT

> 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.

Signature

Hope this helps.

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

> Hi,
>
[quoted text clipped - 6 lines]
>
> TIA

Reply to this Message

Roy Harvey (SQL Server MVP) - 12 Jul 2008 16:29 GMT

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

>Hi,
>
[quoted text clipped - 5 lines]
>
>TIA

Reply to this Message