MSSQL DBA 精华总结[处理对象名称中特殊字符]

mikean · 发表于 2012-3-10 19:53

收藏，备用。

Cherish_j_wang · 发表于 2012-3-10 20:37

周应侯发表于 2012-3-10 13:09
这个也有bug~

能不能请您指出来，方便我修正。谢谢

Cherish_j_wang · 发表于 2012-3-10 21:19

本帖最后由 Cherish_j_wang 于 2012-3-10 21:20 编辑

MSSQL DBA 常用脚本总结

之查看MSSQL Block进程关系

场景
在MSSQL DBA日常工作过程中，经常会遇到服务器上某一个简单的查询非常缓慢，作为DBA的职业敏感性，首先应该想到是：该进程是否被其他进程Block住？那么，我们需要搞清楚以下问题：
1.    执行进程被哪一个进程Block住？
2.    这个进程执行的是神马语句块或者存储过程？
3.    该语句块(或者存储过程)执行到那条语句？
4.    Block进程之间的关系（有的时候会存在很多进程的Block，我们需要搞清楚谁阻塞了谁？），找到Block的Root

脚本
带着这个问题，我们写下了下面的语句：

Block_Query_V2.sql (3.65 KB, 下载次数: 284)

USE Master
GO
declare
@spid int
;
select
@spid = null--null:all
;
;WITH DATA(spid,blockRelationship,blocked,spidLevel,hostname,program_name,loginame,login_time,BlockDuration,Status,sqlText,Memo,stmt_start,stmt_end,db_Name)
AS(
SELECT spid
,CONVERT(VARCHAR(256),' ') AS blockRelationship
,blocked
,spidLevel = 1
,hostname
,program_name
,loginame
,A.login_time
,DATEDIFF(MINUTE,A.login_time,GETDATE()) AS BlockDuration
,A.Status
,B.text
,Memo = CONVERT (varchar(128), 'BlockRoot')
,A.stmt_start
,A.stmt_end
,db_name(A.dbid) AS db_Name
FROM sys.sysprocesses AS A WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) AS B
WHERE Blocked = 0
UNION ALL
SELECT
A.spid
,CONVERT(varchar(128),REPLICATE('L' ,B.spidLevel)) + CONVERT (varchar(128), A.blocked) AS Sort
,A.blocked
,spidLevel+1
,A.hostname
,A.program_name
,A.loginame
,A.login_time
,DATEDIFF(MINUTE,A.login_time,GETDATE()) AS BlockDuration
,A.Status
,C.text
,Memo = 'Blocked by ' + CONVERT (varchar(117), A.blocked)
,A.stmt_start
,A.stmt_end
,db_name(A.dbid) AS db_Name
FROM sys.sysprocesses AS A WITH (NOLOCK)
INNER JOIN DATA AS B
ON A.blocked = B.spid
CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) AS C
--WHERE B.blocked = 0
)
SELECT spid
,blockRelationship
,blocked
,login_time
,GETDATE() AS [current_time]
,sql_statement = (SELECT TOP 1 SUBSTRING(sqlText,stmt_start / 2+1 ,
(
(
CASE WHEN stmt_end = -1 THEN (LEN(CONVERT(nvarchar(max),sqlText)) * 2)
ELSE stmt_end END
) - stmt_start) / 2+1
)
)
,db_Name
,spidLevel
,hostname
,loginame
,program_name
--,login_time
,BlockDuration
,status
,sqlText
,Memo
FROM DATA
--the block root spid
WHERE spidLevel = 1
AND spID IN(
SELECT blocked
FROM DATA
)
UNION ALL
SELECT spid
,blockRelationship
,blocked
,login_time
,GETDATE()
,sql_statement = (SELECT TOP 1 SUBSTRING(sqlText,stmt_start / 2+1 ,
(
(
CASE WHEN stmt_end = -1 THEN (LEN(CONVERT(nvarchar(max),sqlText)) * 2)
ELSE stmt_end END
) - stmt_start) / 2+1
)
)
,db_Name
,spidLevel
,hostname
,loginame
,program_name
--,login_time
,BlockDuration
,status
,sqlText
,Memo
FROM DATA
WHERE spidLevel > 1
IF @spid is not null
SELECT
database_name = DB_NAME(s1.dbid)
,sql_statement = (SELECT TOP 1 SUBSTRING(s2.text,stmt_start / 2+1 ,
(
(
CASE WHEN stmt_end = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE stmt_end END
) - stmt_start) / 2+1
)
)
,s2.text
,Duration_min = DATEDIFF(MINUTE,s1.login_time,GETDATE())
,s1.hostname
,s1.status
FROM sys.sysprocesses AS s1 WITH(NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s1.spid = @spid;

复制代码

脚本说明：
该脚本有一个输入参数(默认为null)，@spid，表示我们需要查看的SPid执行的语句情况，如果不输入，则只查看Block情况。

测试
1. 我们打开SSMS，开启一个连接，执行下面的语句（比如SPID为54）：

if OBJECT_ID('tempdb.dbo.##temp','u') is not null
drop table ##temp
GO
begin tran
select *
into ##temp
from sys.tables

复制代码

2. 带一执行完毕后，我们再打开另外一个连接，执行下面的语句（比如SPID为57）

select * from ##temp

复制代码

3. 步骤2中的进程会一致处于执行状态，让我们开启第三个连接，执行上面查看Block关系的脚本
显示结果如下：
不设置如输入参数:

将输入参数设置为54

说明当然大家也可以使用SP_who2稍加改造来查看Block情况，脚本如下：

sp_who2.sql (860 Bytes, 下载次数: 60)

use master
go
declare @t table
(
spid int
,status nchar(30)
,login nchar(128)
,HostName nchar(128)
,BlockBy char(5)
,DbName nchar(128)
,Command nchar(16)
,CpuTime int
,DiskIO int
,LastBatch varchar(16)
,ProgramName nchar(128)
,Spid2 int
,request_id int
)
INSERT INTO @t
EXEC sp_who2
IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp
(
BlockBy INT NULL
);
INSERT INTO #temp
SELECT BlockBy
FROM @t
WHERE LTRIM(BlockBy)<>'.'
select B.Text
,A.hostName
,A.program_name
,A.loginame
,db_name(a.dbid) AS DbName
,A.status
,A.spid
,A.blocked
,A.*,B.*
from sys.sysprocesses AS A
cross apply sys.dm_exec_sql_text(A.sql_handle) aS B
WHERE A.Spid IN(
SELECT BlockBy
FROM #temp
UNION ALL
SELECT spid
FROM @t
WHERE LTRIM(BlockBy)<>'.'
)

复制代码

显示结果如下

Cherish_j_wang · 发表于 2012-3-10 21:26

mikean 发表于 2012-3-10 19:53
收藏，备用。

没啥人气啊~~~都是分享了不少了~

lg0812 · 发表于 2012-3-10 22:18

楼主很强大。对我们这些初学者来说。非常值得学习。我支持你。会一直关注的。

Cherish_j_wang · 发表于 2012-3-10 22:22

lg0812 发表于 2012-3-10 22:18
楼主很强大。对我们这些初学者来说。非常值得学习。我支持你。会一直关注的。

谢谢您~~

您们的关注，是我不断写贴最大的动力

lg0812 · 发表于 2012-3-10 22:27

Cherish_j_wang 发表于 2012-3-10 22:22
谢谢您~~您们的关注，是我不断写贴最大的动力

要谢谢您的无私分享。也是我们这些初学者不断学习的东西。

Cherish_j_wang · 发表于 2012-3-10 22:48

lg0812 发表于 2012-3-10 22:27
要谢谢您的无私分享。也是我们这些初学者不断学习的东西。

好嘛，那就经常来看看吧。如果有时间，我几乎每天都会分享一二~~~多多的提意见才好~

treesofthehill · 发表于 2012-3-11 01:55

mark

lg0812 · 发表于 2012-3-11 07:43

Cherish_j_wang 发表于 2012-3-10 22:48
好嘛，那就经常来看看吧。如果有时间，我几乎每天都会分享一二~~~多多的提意见才好~

抱歉，打错字了。是动力，不是东西。哈哈哈

[精华] MSSQL DBA 精华总结[处理对象名称中特殊字符]

浏览过的版块