用 Transact-SQL 语句监视

SQL Server 提供一些 Transact-SQL 语句和系统存储过程，用于对 SQL Server 实例进行特殊监视。当想要快速查看有关服务器性能和活动的信息时，可以使用这些语句。

[@more@]

1. 查看当前的锁

语法

sp_lock [[@spid1 =] 'spid1'] [,[@spid2 =] 'spid2']

参数

[@spid1 =] 'spid1'

是来自 master.dbo.sysprocesses 的 Microsoft® SQL Server™ 进程 ID 号。spid1 的数据类型为 int，默认值为 NULL。执行 sp_who 可获取有关该锁的进程信息。如果没有指定 spid1，则显示所有锁的信息。

[@spid2 =] 'spid2'

是用于检查锁信息的另一个 SQL Server 进程 ID 号。spid2 的数据类型为 int，默认设置为 NULL。spid2 为可以与 spid1 同时拥有锁的另一个 spid，用户还可获取有关它的信息。

说明 sp_who 可含有 0 个、1 个或 2 个参数。这些参数确定存储过程是显示全部、1 个还是 2 个 spid 进程的锁定信息。

权限

执行权限默认授予 public 角色。

示例

A. 列出所有锁

下面的示例显示 SQL Server 中当前持有的所有锁的信息。

USE master

EXEC sp_lock

B. 列出单个服务器进程的锁

下例显示进程 ID 53 的信息（其中包括锁信息）。

USE master

EXEC sp_lock 53

锁的类型：

DB：数据库
FIL：文件
IDX：索引
PG：页
KEY：键
TAB：表
EXT：扩展盘区
RID：行标识符

锁的请求状态 :

GRANT
WAIT
CNVRT

2. 查看当前用户活动

语法

sp_who [[@login_name =] 'login']

SQL Server 2000 保留从 1 到 50 的 SPID 值以便内部使用，而 51 或更大的 SPID 值则代表用户会话。

权限

执行权限默认授予 public 角色。

示例

A. 列出全部当前进程

此示例使用没有参数的 sp_who 报告所有当前用户。

USE master

EXEC sp_who

下面是结果集：

spid ecid status loginame hostname blk dbname cmd

---- ---- ------ ------------ -------- --- ------ -----

1 0 background sa 0 pubs LAZY WRITER

2 0 sleeping sa 0 pubs LOG WRITER

3 0 background sa 0 master SIGNAL HANDLER

4 0 background sa 0 pubs RA MANAGER

5 0 background sa 0 master TASK MANAGER

6 0 sleeping sa 0 pubs CHECKPOINT SLEEP

7 0 background sa 0 master TASK MANAGER

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

51 0 runnable DOMAINloginX serverX 0 Nwind BACKUP DATABASE

51 2 runnable DOMAINloginX serverX 0 Nwind BACKUP DATABASE

51 1 runnable DOMAINloginX serverX 0 Nwind BACKUP DATABASE

52 0 sleeping DOMAINloginX serverX 0 master AWAITING COMMAND

53 0 runnable DOMAINloginX serverX 0 pubs SELECT

(16 row(s) affected)

B. 列出特定用户的进程

此示例显示如何通过登录名查看有关单个当前用户的信息。

USE master

EXEC sp_who 'janetl'

C. 显示所有活动进程

USE master

EXEC sp_who 'active'

D. 通过进程 ID 显示特定进程

USE master

EXEC sp_who '10' --specifies the process_id

3. 查看用户上次提交的批命令

语法

DBCC INPUTBUFFER (spid)

权限

DBCC INPUTBUFFER 权限默认授予 sysadmin 固定服务器角色的成员，该成员可以看到任何 SPID。其他用户可以看到自己拥有的 SPID。权限不可转让。

4. 查看表或数据库使用的数据空间

显示行数、保留的磁盘空间以及当前数据库中的表所使用的磁盘空间，或显示由整个数据库保留和使用的磁盘空间。

权限

执行权限默认授予 public 角色。

语法

sp_spaceused [[@objname =] 'objname']
[,[@updateusage =] 'updateusage']

参数

[@objname =] 'objname'

是为其请求空间使用信息（保留和已分配的空间）的表名。objname 的数据类型是 nvarchar(776)，默认设置为 NULL。

[@updateusage =] 'updateusage'

表示应在数据库内（未指定 objname 时）还是在特定的对象上（指定 objname 时）运行 DBCC UPDATEUSAGE。值可以是 true 或 false。updateusage 的数据类型是 varchar(5)，默认设置为 FALSE。

示例

A. 有关表的空间信息

下例报告为 titles 表分配（保留）的空间量、数据使用的空间量、索引使用的空间量以及由数据库对象保留的未用空间量。

USE pubs

EXEC sp_spaceused 'titles'

B. 有关整个数据库的已更新空间信息

下例概括当前数据库使用的空间并使用可选参数 @updateusage。

USE pubs

sp_spaceused @updateusage = 'TRUE'

注释

sp_spaceused 计算数据和索引使用的磁盘空间量以及当前数据库中的表所使用的磁盘空间量。如果没有给定 objname，sp_spaceused 则报告整个当前数据库所使用的空间。

当指定 updateusage 时，Microsoft® SQL Server™ 扫描数据库中的数据页，并就每个表使用的存储空间对 sysindexes 表作出任何必要的纠正。例如会出现这样一些情况：当除去索引后，表的 sysindexes 信息可能不是当前的。该进程在大表或数据库上可能要花一些时间运行。只有当怀疑所返回的值不正确，而且该进程对数据库中的其它用户或进程没有负面影响时，才应使用该进程。如果首选该进程，则可以单独运行 DBCC UPDATEUSAGE。

5. 查看事务日志使用的空间

语法

DBCC SQLPERF ( LOGSPACE )

权限

DBCC SQLPERF 对任何用户默认权限。

示例

下例显示当前安装的所有数据库的 LOGSPACE 信息。

DBCC SQLPERF(LOGSPACE)

GO

下面是结果集：

Database Name Log Size (MB) Log Space Used (%) Status

------------- ------------- ------------------ -----------

pubs 1.99219 4.26471 0

msdb 3.99219 17.0132 0

tempdb 1.99219 1.64216 0

model 1.0 12.7953 0

master 3.99219 14.3469 0

6. 查看数据库中时间最久的活动事务（包括复制的事务）

权限

DBCC OPENTRAN 权限默认授予 sysadmin 固定服务器角色或 db_owner 固定数据库角色的成员且不可转让。

语法

DBCC OPENTRAN
( { 'database_name' | database_id} )
[ WITH TABLERESULTS
[ , NO_INFOMSGS ]
]

示例

下例获得当前数据库和 pubs 数据库的事务信息。

-- Display transaction information only for the current database.

DBCC OPENTRAN

GO

-- Display transaction information for the pubs database.

DBCC OPENTRAN('pubs')

GO

7. 查看I/O、内存和网络吞吐量的性能信息

sysperfinfo

8. 查看过程高速缓存的使用情况

权限

DBCC PROCCACHE 权限默认授予 sysadmin 固定服务器角色或 db_owner 固定数据库角色的成员且不可转让。

语法

DBCC PROCCACHE

注释：

num proc buffs：过程高速缓存中可能有的存储过程数。

num proc buffs used： 容纳存储过程的高速缓存槽数。

num proc buffs active： 容纳正在执行的存储过程的高速缓存槽数。

proc cache size： 过程高速缓存的总大小。

proc cache used： 容纳存储过程的过程高速缓存量。

proc cache active： 容纳正在执行的存储过程的过程高速缓存量。

9. 查看 SQL Server 活动和使用的常规统计信息

如 CPU 用于执行 SQL Server 操作的时间，或 SQL Server 用于执行 I/O 操作的时间，SQL Server 读写取的次数以及读取和写入时遇到的错误数。

权限

执行权限默认赋予 sysadmin 固定服务器角色的成员。

示例

下面的示例报告有关 SQL Server 繁忙程度的信息。

USE master

EXEC sp_monitor

10. 终止进程

语法

KILL {spid | UOW} [WITH STATUSONLY]

使用 @@SPID 可显示当前会话的 SPID 值。

权限

默认情况下，sysadmin 和 processadmin 固定数据库角色的成员具有 KILL 的默认权限，KILL 权限不可转让。

示例

A. 使用 KILL 终止 SPID

下面的示例显示如何终止 SPID 53。

KILL 53

B. 使用 KILL spid WITH STATUSONLY 获得进度报告。

下面的示例为特定的 spid 生成回滚进程的状态。

KILL 54

KILL 54 WITH STATUSONLY

--This is the progress report.

spid 54: Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 10 seconds.

C. 使用 KILL 终止孤立的分布式事务。

下例说明如何使用 UOW = D5499C66-E398-45CA-BF7E-DC9C194B48CF 终止孤立 (SPID = -2) 事务。

KILL 'D5499C66-E398-45CA-BF7E-DC9C194B48CF'

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/66009/viewspace-803282/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/66009/viewspace-803282/