osql/sqlcmd 工具是一个 SqlServer的命令提示符工具，我们可以使用它运行 Transact-SQL 语句和脚本文件。该工具所在目录：C:\Program Files\Microsoft SQL Server\110\Tools\Binn
交互式输入 Transact-SQL 语句，如下
C:\Users\Administrator>osql -E
1> set nocount on;
2> go
1> select getdate();
2> go -----------------------
2015-06-20 18:02:18.053 1> exit C:\Users\Administrator>
osql的作用不仅仅局限于此，其他参数如下
强大之处在于：该工具可以结合存储过程，检查并输出锁、闩等资源的阻塞情况。
- 创建存储过程：sp_blocker_pss80
- 创建SQL脚本：定时执行sp_blocker_pss80存储过程
- 在osql命令中调用该SQL脚本，并将结果输出到文件
以下分别是sp_blocker_pss80、SQL脚本、osql命令示例及对应的输出结果
use master
GO
if exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss80') and sysstat & 0xf = 4)
drop procedure dbo.sp_blocker_pss80
GO
create procedure dbo.sp_blocker_pss80 (@latch int = 0, @fast int = 1, @appname sysname='PSSDIAG')
as
--version 16SP3
if is_member('sysadmin')=0
begin
print 'Must be a member of the sysadmin group in order to run this procedure'
return
end set nocount on
SET LANGUAGE 'us_english'
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime
declare @dbname nvarchar(128)
declare @status sql_variant
declare @useraccess sql_variant set @time = getdate()
declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,
ignore_app tinyint, primary key (blocked, spid, ecid))
insert @probclients select spid, ecid, blocked, waittype, dbid,
case when convert(varchar(128),hostname) = @appname then 1 else 0 end
from sysprocesses where blocked!=0 or waittype != 0x0000 if exists (select spid from @probclients where ignore_app != 1 or waittype != 0x020B)
begin
set @time2 = getdate()
print ''
print '8.2 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2)) insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from @probclients
where blocked not in (select spid from @probclients) and blocked != 0 if (@fast = 1)
begin
print ''
print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion) select spid, status, blocked, open_tran, waitresource, waittype,
waittime, cmd, lastwaittype, cpu, physical_io,
memusage, last_batch=convert(varchar(26), last_batch,121),
login_time=convert(varchar(26), login_time,121),net_address,
net_library, dbid, ecid, kpid, hostname, hostprocess,
loginame, program_name, nt_domain, nt_username, uid, sid,
sql_handle, stmt_start, stmt_end
from master..sysprocesses
where blocked!=0 or waittype != 0x0000
or spid in (select blocked from @probclients where blocked != 0)
or spid in (select spid from @probclients where blocked != 0) print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) print ''
print 'SYSPROC FIRST PASS'
select spid, ecid, waittype from @probclients where waittype != 0x0000 if exists(select blocked from @probclients where blocked != 0)
begin
print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
print ''
print 'SPIDs at the head of blocking chains'
select spid from @probclients
where blocked = 0 and spid in (select blocked from @probclients where spid != 0)
if @latch = 0
begin
print 'SYSLOCKINFO'
select @time2 = getdate() select spid = convert (smallint, req_spid),
ecid = convert (smallint, req_ecid),
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
Type = case rsc_type when 1 then 'NUL'
when 2 then 'DB'
when 3 then 'FIL'
when 4 then 'IDX'
when 5 then 'TAB'
when 6 then 'PAG'
when 7 then 'KEY'
when 8 then 'EXT'
when 9 then 'RID'
when 10 then 'APP' end,
Resource = substring (rsc_text, 1, 16),
Mode = case req_mode + 1 when 1 then NULL
when 2 then 'Sch-S'
when 3 then 'Sch-M'
when 4 then 'S'
when 5 then 'U'
when 6 then 'X'
when 7 then 'IS'
when 8 then 'IU'
when 9 then 'IX'
when 10 then 'SIU'
when 11 then 'SIX'
when 12 then 'UIX'
when 13 then 'BU'
when 14 then 'RangeS-S'
when 15 then 'RangeS-U'
when 16 then 'RangeIn-Null'
when 17 then 'RangeIn-S'
when 18 then 'RangeIn-U'
when 19 then 'RangeIn-X'
when 20 then 'RangeX-S'
when 21 then 'RangeX-U'
when 22 then 'RangeX-X'end,
Status = case req_status when 1 then 'GRANT'
when 2 then 'CNVT'
when 3 then 'WAIT' end,
req_transactionID As TransID, req_transactionUOW As TransUOW
from master.dbo.syslockinfo s,
@probclients p
where p.spid = s.req_spid print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))
end -- latch not set
end
else
print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
print ''
end -- fast set else
begin -- Fast not set
print ''
print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion) select spid, status, blocked, open_tran, waitresource, waittype,
waittime, cmd, lastwaittype, cpu, physical_io,
memusage, last_batch=convert(varchar(26), last_batch,121),
login_time=convert(varchar(26), login_time,121),net_address,
net_library, dbid, ecid, kpid, hostname, hostprocess,
loginame, program_name, nt_domain, nt_username, uid, sid,
sql_handle, stmt_start, stmt_end
from master..sysprocesses print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) print ''
print 'SYSPROC FIRST PASS'
select spid, ecid, waittype from @probclients where waittype != 0x0000 if exists(select blocked from @probclients where blocked != 0)
begin
print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
print ''
print 'SPIDs at the head of blocking chains'
select spid from @probclients
where blocked = 0 and spid in (select blocked from @probclients where spid != 0)
if @latch = 0
begin
print 'SYSLOCKINFO'
select @time2 = getdate() select spid = convert (smallint, req_spid),
ecid = convert (smallint, req_ecid),
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
Type = case rsc_type when 1 then 'NUL'
when 2 then 'DB'
when 3 then 'FIL'
when 4 then 'IDX'
when 5 then 'TAB'
when 6 then 'PAG'
when 7 then 'KEY'
when 8 then 'EXT'
when 9 then 'RID'
when 10 then 'APP' end,
Resource = substring (rsc_text, 1, 16),
Mode = case req_mode + 1 when 1 then NULL
when 2 then 'Sch-S'
when 3 then 'Sch-M'
when 4 then 'S'
when 5 then 'U'
when 6 then 'X'
when 7 then 'IS'
when 8 then 'IU'
when 9 then 'IX'
when 10 then 'SIU'
when 11 then 'SIX'
when 12 then 'UIX'
when 13 then 'BU'
when 14 then 'RangeS-S'
when 15 then 'RangeS-U'
when 16 then 'RangeIn-Null'
when 17 then 'RangeIn-S'
when 18 then 'RangeIn-U'
when 19 then 'RangeIn-X'
when 20 then 'RangeX-S'
when 21 then 'RangeX-U'
when 22 then 'RangeX-X'end,
Status = case req_status when 1 then 'GRANT'
when 2 then 'CNVT'
when 3 then 'WAIT' end,
req_transactionID As TransID, req_transactionUOW As TransUOW
from master.dbo.syslockinfo print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))
end -- latch not set
end
else
print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
print ''
end -- Fast not set print 'DBCC SQLPERF(WAITSTATS)'
dbcc sqlperf(waitstats) Print ''
Print '*********************************************************************'
Print 'Print out DBCC Input buffer for all blocked or blocking spids.'
Print '*********************************************************************' declare ibuffer cursor fast_forward for
select distinct cast (spid as varchar(6)) as spid
from @probclients
where (spid <> @@spid) and
((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))
or spid in (select blocked from @probclients where blocked != 0))
open ibuffer
fetch next from ibuffer into @spid
while (@@fetch_status != -1)
begin
print ''
print 'DBCC INPUTBUFFER FOR SPID ' + @spid
exec ('dbcc inputbuffer (' + @spid + ')') fetch next from ibuffer into @spid
end
deallocate ibuffer Print ''
Print '*******************************************************************************'
Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'
Print '*******************************************************************************'
declare ibuffer cursor fast_forward for
select distinct cast (dbid as varchar(6)) from @probclients
where dbid != 0
open ibuffer
fetch next from ibuffer into @spid
while (@@fetch_status != -1)
begin
print ''
set @dbname = db_name(@spid)
set @status = DATABASEPROPERTYEX(@dbname,'Status')
set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')
print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'
if @Status = N'ONLINE' and @UserAccess != N'SINGLE_USER'
dbcc opentran(@dbname)
else
print 'Skipped: Status=' + convert(nvarchar(128),@status)
+ ' UserAccess=' + convert(nvarchar(128),@useraccess) print ''
if @spid = '2' select @blocked = 'Y'
fetch next from ibuffer into @spid
end
deallocate ibuffer
if @blocked != 'Y'
begin
print ''
print 'DBCC OPENTRAN FOR DBID 2 [tempdb]'
dbcc opentran ('tempdb')
end print 'End time: ' + convert(varchar(26), getdate(), 121)
end -- All
else
print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '
+ convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)')
GO
创建一个SQL脚本文件：checkblk.sql
WHILE 1=1
BEGIN
EXEC master.dbo.sp_blocker_pss80
-- Or for fast mode
-- EXEC master.dbo.sp_blocker_pss80 @fast=1
-- Or for latch mode
-- EXEC master.dbo.sp_blocker_pss80 @latch=1
WAITFOR DELAY '00:00:15'
END
GO
osql命令示例如下：
C:\>osql -E -S server -i checkblk.sql -o checkresult.txt -w 2000
server：表示服务器的机器名
checkblk.sql：表示输入的脚本文件
checkresult.txt：表示输出的结果文件
参数详情请使用命令 osql /? 查看
执行结果如下：
SQL Server锁、闩等资源的阻塞诊断---osql/sqlcmd，sp_blocker_pss80的更多相关文章
-
SQL SERVER 锁资源问题
1204: cannot obtain a LOCK resource 在sql server 锁资源的限制基本是自动优化调整.如果调整过参数,可能在系统大批量查询的时候出现以上错误,或者是 alwa ...
-
sql server 锁与事务拨云见日(上)
一.概述 讲到sql server锁管理时,感觉它是一个大话题,因为它不但重要而且涉及的知识点很多,重点在于要掌握高并发要先要掌握锁与事务,涉及的知识点多它包括各式各样的锁,锁的组合,锁的排斥,锁延伸 ...
-
SQL Server锁类型
SQL Server锁类型(SQL)收藏 1． HOLDLOCK: 在该表上保持共享锁,直到整个事务结束,而不是在语句执行完立即释放所添加的锁. 2． NOLOCK:不添加共享锁和排它锁,当这个选项生 ...
-
转：sql server锁知识及锁应用
sql server锁(lock)知识及锁应用 提示:这里所摘抄的关于锁的知识有的是不同sql server版本的,对应于特定版本时会有问题. 一 关于锁的基础知识 (一). 为什么要引入锁 当多个用 ...
-
sql server 锁与事务拨云见日(中)
一.事务的概述 上一章节里,重点讲到了锁,以及锁与事务的关系.离上篇发布时间好几天了,每天利用一点空闲时间还真是要坚持.听<明朝那些事儿>中讲到"人与人最小的差距是聪明,人与人最 ...
-
SQL SERVER锁（LOCK）知识及锁应用
提示:这里所摘抄的关于锁的知识有的是不同sql server版本的,对应于特定版本时会有问题. 一 关于锁的基础知识 (一). 为什么要引入锁 当多个用户同时对数据库的并发操作时会带来以下数据不一致的 ...
-
sql server 性能调优 资源等待之网络I/O
原文:sql server 性能调优 资源等待之网络I/O 一.概述 与网络I/O相关的等待的主要是ASYNC_NETWORK_IO,是指当sql server返回数据结果集给客户端的时候,会先将结果 ...
-
[转]了解SQL Server锁争用：NOLOCK 和 ROWLOCK 的秘密_Mr_Indigo的空间
了解SQL Server锁争用:NOLOCK 和 ROWLOCK 的秘密 关系型数据库,如SQL Server,使用锁来避免多用户修改数据时的并发冲突.当一组数据被某个用户锁定时,除非第一个用户结束修 ...
-
了解SQL Server锁争用：NOLOCK 和 ROWLOCK 的秘密
关系型数据库,如SQL Server,使用锁来避免多用户修改数据时的并发冲突.当一组数据被某个用户锁定时,除非第一个用户结束修改并释放锁,否则其他用户就无法修改该组数据. 有些数据库,包括SQL Se ...
随机推荐
-
Performance Test of List<;T>;, LinkedList<;T>;, Queue<;T>;, ConcurrentQueue<;T>;
//Test Group 1 { var watch = Stopwatch.StartNew(); var list = new List<int>(); ; j < ; j++) ...
-
UML简易看懂
这是一堂关于UML基础知识的补习课:现在我们做项目时间都太紧了,基本上都没有做过真正的class级别的详细设计,更别提使用UML来实现规范建模了:本篇主要就以前自己一直感觉很迷糊的几种class之间的 ...
-
ORA-00911:无效字符错误
ORA-00911:无效字符错误--造成构建环境的一个小错误 实施某功能脚本语句.编译时,出现了ORA-00911错误,当时有些疑惑,之前生产库使用是没有问题的,经过一番检查后发现原来是一个非常细微的 ...
-
Java常见运算符整理
本文是在学习中的总结,欢迎转载但请注明出处:http://blog.csdn.net/pistolove/article/details/44724267 本文主要介绍Java中常见的运算符,重点介绍 ...
-
codeblocks添加编译选项
-std=c++0x
-
linux进程虚拟地址空间
转载源 在多任务操作系统中,每个进程都运行在属于自己的内存沙盘中.这个沙盘就是虚拟地址空间(Virtual Address Space),在32位模式下它是一个4GB的内存地址块.在Linux系统中, ...
-
luogu 1972 小H的项链 莫队
1.莫队算法 TLE 80 #include<bits/stdc++.h> #define rep(i,x,y) for(register int i=x;i<=y;i++) usi ...
-
fmod()函数 (对浮点数取模)
头文件:#include <math.h> fmod() 用来对浮点数进行取模(求余),其原型为: double fmod (double x); 设返回值为 ret,那么 x = ...
-
统计apk或jar中方法数量
一.apk中方法数量 ./android-sdk-linux/build-tools/23.0.2/dexdump -f apk路径 | grep method_ids_size 二.jar中方法数 ...
-
shiro学习笔记_0600_自定义realm实现授权
博客shiro学习笔记_0400_自定义Realm实现身份认证 介绍了认证,这里介绍授权. 1,仅仅通过配置文件来指定权限不够灵活且不方便.在实际的应用中大多数情况下都是将用户信息,角色信息,权限信息 ...