SQL Activity Monitor

从实质上说, Actvity Monitor不过是SQL Server中系统View sys.sysprocesses的一种展现. SP_Who2应该也是从这里查询到的数据。下面是view sysprocesses的SELECT内容：

 SELECT   
 spid,  
 kpid,  
 blocked,  
 waittype,  
 waittime,  
 lastwaittype,  
 waitresource,  
 dbid,  
 uid = convert(smallint, uid),  
 cpu,  
 physical_io,  
 memusage,  
 login_time,  
 last_batch,  
 ecid,  
 open_tran,  
 status,  
 sid,  
 hostname,  
 program_name,  
 hostprocess,  
 cmd,  
 nt_domain,  
 nt_username,  
 net_address,  
 net_library,  
 loginame,  
 context_info,  
 sql_handle,  
 stmt_start,  
 stmt_end,  
 request_id   
 FROM OpenRowSet(TABLE SYSPROCESSES)  

可以使用如下的语句来获得与activity monitor相同的输出.

    select * from sys.sysprocesses where spid > 50

    and status = ‘suspended’

     

    select distinct lastwaittype from sys.sysprocesses where spid > 50

    and status = ‘suspended’

在sys.sysprocesses表中, status列很重要, 不同Process的ID对应的可能的值如下: dormant = SQL Server 正在重置session running = Session正在运行一个或多个批处理(batch). 当Multiple Active Result Sets (MARS) 处于开启的状态下, 一个session可以运行多个batch. background = Session正在运行一个后台任务, 比如说死锁检测(dead lock detection). rollback = Session正在进行一个回滚的操作(rollback) pending = Session正在等待出现一个可用的work thread. runnable = Session中的任务正在定式作业管理器的runnable的队列中, 等待一个时间片, 即等待CPU处理. spinloop = session中的任务正在等待一个自旋锁(spinlock)的释放. suspended = The session is waiting for an event, such as I/O, to complete.Session正在等待一个事件的完成, 比如说IO. SQL系统自己的spid都是小于等于50的, 大于50的spid都是来自于应用程序的. 参考资料: http://msdn.microsoft.com/en-us/library/ms179881.aspx http://msdn.microsoft.com/en-us/library/ms178520%28SQL.90%29.aspx