如何查看SQL SERVER数据库当前连接数

Question

如何查看SQL SERVER数据库当前连接数
1.通过管理工具
开始->管理工具->性能（或者是运行里面输入 mmc）然后通过添加计数器添加 SQL 的常用统计然后在下面列出的项目里面选择用户连接就可以时时查询到数据库的连接数了。不过此方法的话需要有访问那台计算机的权限，就是要通过Windows账户登陆进去才可以添加此计数器。

2.通过系统表查询

SQL code

SELECT * FROM
[Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN ( SELECT
   [DBID]
FROM
   [Master].[dbo].[SYSDATABASES]
WHERE
   NAME='DBName'
)

DBName 是需要查看的数据库，然后查询出来的行数，就是当前的连接数。不过里面还有一些别的状态可以做参考用。

例如：连到master这个数据库写如下语句

SQL code

select * from sysprocesses where dbid in (select dbid from sysdatabases where name='MyDatabase')

将所有连接MyDatabase这个数据库的连接记录都求出来。
sysprocesses这个表记录所以连接到SQL SERVER数据库的连接。

3.通过系统的存储过程来查找

SQL code

SP_WHO 'UserName'

UserName 是当然登陆Sql的用户名，一般程序里面都会使用一个UserName来登陆SQL这样通过这个用户名就能查看到此用户名登陆之后占用的连接了。
如果不写UserName，那么返回的就是所有的连接。

SQL code

SP_WHO 'sa'
spid   ecid   status                         loginame                                                                                                                         hostname                                                                                                                         blk   dbname                                                                                                                           cmd            
------ ------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----- -------------------------------------------------------------------------------------------------------------------------------- ----------------
1      0      background                     sa                                                                                                                                                                                                                                                                0     NULL                                                                                                                             LAZY WRITER   
2      0      background                     sa                                                                                                                                                                                                                                                                0     master                                                                                                                           SIGNAL HANDLER
3      0      sleeping                       sa                                                                                                                                                                                                                                                                0     NULL                                                                                                                             LOG WRITER     
4      0      background                     sa                                                                                                                                                                                                                                                                0     NULL                                                                                                                             LOCK MONITOR   
5      0      background                     sa                                                                                                                                                                                                                                                                0     master                                                                                                                           TASK MANAGER   
6      0      background                     sa                                                                                                                                                                                                                                                                0     master                                                                                                                           TASK MANAGER   
7      0      sleeping                       sa                                                                                                                                                                                                                                                                0     NULL                                                                                                                             CHECKPOINT SLEEP
8      0      background                     sa                                                                                                                                                                                                                                                                0     master                                                                                                                           TASK MANAGER   
9      0      background                     sa                                                                                                                                                                                                                                                                0     master                                                                                                                           TASK MANAGER   
10     0      background                     sa                                                                                                                                                                                                                                                                0     master                                                                                                                           TASK MANAGER   
11     0      background                     sa                                                                                                                                                                                                                                                                0     master                                                                                                                           TASK MANAGER   
12     0      background                     sa                                                                                                                                                                                                                                                                0     master                                                                                                                           TASK MANAGER   
13     0      background                     sa                                                                                                                                                                                                                                                                0     master                                                                                                                           TASK MANAGER   
51     0      runnable                       sa                                                                                                                               CHINA-668C6A66D                                                                                                                  0     master                                                                                                                           SELECT         
52     0      sleeping                       sa                                                                                                                               CHINA-668C6A66D                                                                                                                  0     master                                                                                                                           AWAITING COMMAND

（所影响的行数为 15 行）

Answer 1

IT 专家中有一种普遍的误解，就是认为“锁定是不好的东西”，你必须尽一切可能保证数据库锁定不会使得进程无法正常运行。为了能够确保一个一致的数据库环境，在对资源进行修改时，数据库引擎必须利用一种机制来获得对资源的独占权。

SQL Server中也用锁定，它们是指为了达到这种一致性，数据库引擎用来保证每一次只有一个线程同时访问同一个资源的对象。如果不用锁定的话，各个进程同时进行数据修改就可能发生，这就会使数据库处于一种不一致的状态。这样看来，锁定就成了好东西；但是，你应该以特定的方式来计划你的应用程序，让涉及的锁定的数量降到最少。在这篇文章中，我将讨论一个让你能够分析数据库锁定问题的存储过程。

找出什么被锁定了

系统的反应迟缓意味着你应该做一些调查了。你的查找最好从测定系统发生锁定的数量和频率开始。如果你的系统环境处理事务性很高的话，这样各个应用程序争夺资源就会很常见，从而引起锁定。解决这些问题的关键就在于能够确定被锁定的资源和争夺资源的进程。

sp_lock

sp_lock 这个系统存储过程与SQL Server 2000 打包在一起，它将使你对在你系统中发生的锁定有深入的了解。这个程序会从主数据库中的syslockinfo中返回与锁定相关的大量信息，而主数据库是一个包括了所有允许、转换和等待锁定请求信息的系统工作台。

让我们来看一下运行 sp_lock 程序之后，它会为我们提供什么信息：

EXECUTE sp_lock
在我的系统中，这是该存储过程返回的内容。sp_lock 返回的信息并不是一目了然的，要获得有用的数据，还需要做一些查找。但是，你也可以复制该存储过程的文本，然后创建一个新的，从而得到关于系统进程的更好的解释。（在这篇文章中，我们将集中讨论sp_lock返回的数据。）

从上面的结果我们可以看到spid、dbid、objid、 indid、type、resource、mode和status字段。spid是进程标识号码，用于识别到SQL 服务器的连接。要发现哪些用户和该spid相连，你就要执行存储过程sp_who，并将spid作为一个参数传输给该程序。dbid是锁定发生的数据库，你可以在主数据库中的sysdatabases表格中找到它。字段objid用来显示在数据库中锁定发生所在的对象。要查看这个对象，你可以在主数据库中的sysobjects表格中查询指定的objid。

在以上的屏幕截图中产生的单一记录并不一定能显示正在你的工作环境中发生的真实情况。在运行这个程序时，你想要找到500到1000个甚至更多结果。每一次你执行sp_lock，都将有可能得到不同的结果，因为又发生了新的锁定，而部分旧的锁定已经被解除了。如果你发现sp_lock返回的结果中，大量的结果都有着相同的spid，很有可能该进程正在进行大型的处理，同时这些锁定可能开始阻止新事务的发生。

当你发现一个spid 获得了大量的数据库锁定时，这将有助于确定什么存储过程或语句正在运行。为了达到这个目的，运行以下 DBCC 命令：

DBCC INPUTBUFFER(spid)
这个DBCC命令将返回正在EventInfo字段中运行的语句的相关信息。

一个可靠的起点

系统运行缓慢可能说明你的表格上有大量的锁定。造成这些锁定的原因较多，如某个用户正在你的系统中运行一个相当长的查询，一个进程占用大量资源或者两个关键进程争夺同一资源，经常造成死锁。

一旦发现你认为正在减缓你系统速度的进程，应该怎么办？在大多数情况下，不能采取任何措施，只能监控系统。结束这个进程并不是明智之举，因为它包括了很多系统锁定，除非你完全肯定不会有其他的负面影响。不然的话，你就应该想办法自动分析锁定状况。还有一个解决办法就是想出一种方法，使得在一天的特定时间内，当系统锁数量达到极限时，发出通知。

你对自己的系统信息收集的越多，在解决问题时，你的优势就越大。

Answer 2

SQL Server] sp_who, sp_who2和sp_who3 收藏
[SQL Server]  sp_who, sp_who2和sp_who3

sp_who可以返回如下信息： (可选参数LoginName, 或active代表活动会话数)
Spid         (系统进程ID)
status      (进程状态)
loginame  (用户登录名)
hostname(用户主机名)
blk           (阻塞进程的SPID)
dbname   (进程正在使用的数据库名)
Cmd        (当前正在执行的命令类型)

sp_who2除了显示上面sp_who的输出信息外，还显示下面的信息：  (可选参数LoginName, 或active代表活动会话数)
CPUTime           (进程占用的总CPU时间)
DiskIO              (进程对磁盘读的总次数)
LastBatch         (客户最后一次调用存储过程或者执行查询的时间)
ProgramName  (用来初始化连接的应用程序名称，或者主机名)
sp_who3是某牛人自定义的存储过程，(可选参数spid)，显示“非系统会话，且是活动的会话”的详细情况。

CREATE PROCEDURE sp_who3  

(  @SessionID int = NULL )  

AS

BEGIN

SELECT

    SPID                = er.session_id  

    ,Status             = ses.status  

    ,[Login]            = ses.login_name  

    ,Host               = ses.host_name  

    ,BlkBy              = er.blocking_session_id  

    ,DBName             = DB_Name(er.database_id)  

    ,CommandType        = er.command  

    ,SQLStatement       = st.text  

    ,ObjectName         = OBJECT_NAME(st.objectid)  

    ,ElapsedMS          = er.total_elapsed_time  

    ,CPUTime            = er.cpu_time  

    ,IOReads            = er.logical_reads + er.reads  

    ,IOWrites           = er.writes  

    ,LastWaitType       = er.last_wait_type  

    ,StartTime          = er.start_time  

    ,Protocol           = con.net_transport  

    ,ConnectionWrites   = con.num_writes  

    ,ConnectionReads    = con.num_reads  

    ,ClientAddress      = con.client_net_address  

    ,Authentication     = con.auth_scheme  

FROM sys.dm_exec_requests er  

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st  

LEFT JOIN sys.dm_exec_sessions ses  

ON ses.session_id = er.session_id  

LEFT JOIN sys.dm_exec_connections con  

ON con.session_id = ses.session_id  

WHERE er.session_id > 50  

    AND @SessionID IS NULL OR er.session_id = @SessionID  

ORDER BY

    er.blocking_session_id DESC

    ,er.session_id  

END

go

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/xiaoxu0123/archive/2010/07/23/5757640.aspx

Answer 3

本帖最后由 liyihongcug 于 2011-03-14 18:22 编辑

dbcc inputbuffer(@@spid)SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
             er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
       THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END -
                                er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
    FROM sys.dm_exec_requests er
    INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
    WHERE session_Id > 50              -- Ignore system spids.
    AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
    ORDER BY 1, 2

select r.session_id, status,qt.text ,qt.dbid,qt.objectid,r.cpu_time,r.total_elapsed_time,r.reads,r.writes,r.logical_reads, r.scheduler_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) as qt
order by r.scheduler_id, r.status, r.session_id