sqlserver分析,sqlserver分析存储过程

如何分析SQLServer中的deadlocktrace

首先我们来看一个简单的例子，大结构非常简单：

创新互联公司服务项目包括新疆网站建设、新疆网站制作、新疆网页制作以及新疆网络营销策划等。多年来，我们专注于互联网行业，利用自身积累的技术优势、行业经验、深度合作伙伴关系等，向广大中小型企业、政府机构等提供互联网行业的解决方案，新疆网站推广取得了明显的社会效益与经济效益。目前，我们服务的客户以成都为中心已经辐射到新疆省份的部分城市，未来相信会继续扩大服务区域并继续获得客户的支持与信任！

1，process-list显示了两个进程之间发生了死锁process60fb88和processd11902c8。

2，vistim-list显示了process60fb88被选为了牺牲者。

2，后面的resource-list显示了两个进程争取并导致死锁的资源。

[html] view plain copy

deadlock

victim-list

victimProcess id="process60fb88" /

/victim-list

process-list

process id="process60fb88" taskpriority="0" logused="0" waitresource="KEY: 9:72057597664231424 (7506ff9b7b0d)" waittime="4376" ownerId="2656658629" transactionname="SELECT" lasttranstarted="2014-04-09T23:01:35.743" XDES="0x80059940" lockMode="S" schedulerid="4" kpid="10640" status="suspended" spid="80" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2014-04-09T23:01:35.657" lastbatchcompleted="2014-04-09T23:01:35.657" clientapp=".Net SqlClient Data Provider" hostname="BODCPRODVSQL128" hostpid="10088" loginname="PROD\s-propdata" isolationlevel="read committed (2)" xactid="2656658629" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"

executionStack

frame procname="" line="9" stmtstart="336" stmtend="874" sqlhandle="0x030009003d00da3fa6087c0182a200000100000000000000" /

frame procname="" line="20" stmtstart="1022" stmtend="1206" sqlhandle="0x03000900941f284ed5929e00aba200000100000000000000" /

frame procname="" line="9" stmtstart="464" stmtend="642" sqlhandle="0x03000a006502e0715df5af00aba200000100000000000000" /

frame procname="" line="4" stmtstart="224" stmtend="420" sqlhandle="0x01000a00b6fca934509742900b0000000000000000000000" /

/executionStack

inputbuf

DECLARE @logText NVARCHAR(MAX)

EXEC IntegratedService_ProcessLatestCommand @logText OUTPUT

SELECT @logText /inputbuf

/process

process id="processd11902c8" taskpriority="0" logused="232" waitresource="KEY: 9:72057596808265728 (ed2e944beff9)" waittime="4379" ownerId="2656658630" transactionname="UPDATE" lasttranstarted="2014-04-09T23:01:35.743" XDES="0x80048570" lockMode="X" schedulerid="8" kpid="6620" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-04-09T23:01:34.650" lastbatchcompleted="2014-04-09T23:01:34.650" clientapp=".Net SqlClient Data Provider" hostname="BODCPRODVSQL128" hostpid="10088" loginname="PROD\s-propdata" isolationlevel="read committed (2)" xactid="2656658630" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"

executionStack

frame procname="" line="22" stmtstart="1230" stmtend="1496" sqlhandle="0x030009003d00da3fa6087c0182a200000100000000000000" /

frame procname="" line="20" stmtstart="1022" stmtend="1206" sqlhandle="0x03000900941f284ed5929e00aba200000100000000000000" /

frame procname="" line="9" stmtstart="464" stmtend="642" sqlhandle="0x03000a006502e0715df5af00aba200000100000000000000" /

frame procname="" line="4" stmtstart="224" stmtend="420" sqlhandle="0x01000a00b6fca934509742900b0000000000000000000000" /

/executionStack

inputbuf

DECLARE @logText NVARCHAR(MAX)

EXEC IntegratedService_ProcessLatestCommand @logText OUTPUT

SELECT @logText /inputbuf

/process

/process-list

resource-list

keylock hobtid="72057597664231424" dbid="9" objectname="" indexname="" id="lockc99859500" mode="X" associatedObjectId="72057597664231424"

owner-list

owner id="processd11902c8" mode="X" /

/owner-list

waiter-list

waiter id="process60fb88" mode="S" requestType="wait" /

/waiter-list

/keylock

keylock hobtid="72057596808265728" dbid="9" objectname="" indexname="" id="lock2f4de2d00" mode="S" associatedObjectId="72057596808265728"

owner-list

owner id="process60fb88" mode="S" /

/owner-list

waiter-list

waiter id="processd11902c8" mode="X" requestType="wait" /

/waiter-list

/keylock

/resource-list

/deadlock

下面是详细分析。

1，victim-list没什么可分析的。

2，process-list中关于各个process的详细信息很重要。

waitresource="KEY: 9:72057597664231424 (7506ff9b7b0d)"

当前process正在等待的资源。通常我们在resource-list中可以看到同样的信息。使用下面的sql查询等待的资源是什么：

下面使用的hobtid是heap or b-tree id的缩写。详细见sys.partotions的解释。

[sql] view plain copy

SELECT o.name, i.name

FROM sys.partitions p

JOIN sys.objects o ON p.object_id = o.object_id

JOIN sys.indexes i ON p.object_id = i.object_id

AND p.index_id = i.index_id

WHERE p.hobt_id = 72057597664231

name name

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

MatchService PK_Matcher_ID

从结果我们就可以知道，等待的资源是一个表MatchService的主键PK_Matcher_ID。考察另外一个process的waitresource我们可以得知等待的资源是同一个表的另外一个索引。至此我们找到了直接导致死锁的资源是什么。

同时可以看到两个process一个是x lock，一个是s lock。因此可以判定发生在该表上的一个修改语句和一个查询语句之间发生了死锁。

另外，上例中可以清晰的看到是keylock导致的死锁，因此查询partitions可以找到对应的object (sys.partitions contains a row for each partition of all

the tables and most types of indexes in the database.)。但有时是其他类型的资源发生了死锁，例如pagelock, waitresource="PAGE: 9:1:28440841" 。 9是dbid; 1是fileid; 28440841是pageid。对于这种情况，使用下面的语句查询对应的资源：

[sql] view plain copy

DBCC TRACEON(3604)

GO

DBCC PAGE (9, 1, 28440841)

GO

DBCC TRACEOFF(3604)

GO

从返回的Metadata: objectId找到对应的objectid。

3，再看process中的inputbuf。这个tag表明了process正在运行的语句，因此对于定位死锁非常重要。但这里有一个问题，比如

上例中，inputbuf是一个存储过程，其中又嵌套了很多其他的存储过程，但inputbuf是用户直接发出的sql，而我们需要在其中找出直接导致死

锁的语句并优化，从而解决或减少死锁。自此我们已经有的信息是：导致死锁的语句由inputbuf中的语句调用，同时导致死锁的语句必定是对表

MatchService的修改语句。如果存储过程很简单，到此DBA已经能够找到直接导致死锁的sql了，分析过程到此结束。而如果存储过程很复杂，则

需要进一步分析。

4，现在再进一步考察tag, executionStack。executionStack表明了死锁发生时，由inputbuf调用的一系列

sql。上例中有4条sql。同时仔细观察上例可以发生，两个process的executionStack是完全相同的，因此考察一个就可以了。另外，

如果procname不为空则直接得到了sql，但上例中该tag为空。

自此我们希望把executionStack中的所有sql显示出来。使用下面的sql找出sqlhandle对应的在内存中的sql。需要注意的

是，如果deadlock已经过去了一段时间，sqlhandle可能已经被从内存中清除掉了，这时就不可查了。还有sqlhandle是

varbinaryd，所以查询时不可加引号。

另外还有一个有趣的地方：和其他程序语言报错时一样，stack最上的一条是最直接的错误，后面的错误都是该错误的上一层错误(这么解释可能有点

乱，写过代码的同学能理解哈)。因此在上面说的存储过程调用存储过程的情况中，executionStack中第一条是直接导致死锁的sql，第二条是调

用该sql的sql，以此类推，最后一条理论上就是inputbuf中的sql。

[sql] view plain copy

SELECT sql_handle AS Handle,

SUBSTRING(st.text, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2) + 1) AS Text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

where sql_handle = 0x030009003d00da3fa6087c0182a200000100000000000000

order by sql_handle

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

0x030009003D00DA3FA6087C0182A200000100000000000000SELECT

TOP 1 @matcherQueueID = lhs.MatcherService_MatcherQueue_ID,

@rootOperationUID = Root_Operation_UID FROM

MatcherService_MatcherQueue lhs WHERE lhs.Processing_State

= 'MATCHING' OR lhs.Processing_State = 'MATCHED' ORDER BY

Last_Execution_Date ASC

0x030009003D00DA3FA6087C0182A200000100000000000000

SELECT Top 1 @ticketID = OperationLog_ID FROM GEDemo.dbo.OperationLog

WHERE @rootOperationUID = Root_Operation_UID AND Status = 0

ORDER BY OperationLog_ID ASC

0x030009003D00DA3FA6087C0182A200000100000000000000

UPDATE MatcherService_MatcherQueue SET Last_Execution_Date =

GETDATE() WHERE MatcherService_MatcherQueue_ID = @matcherQueueID

注意看起来一个sql_handle有三条语句，原因是这三条sql是属于同一个存储过程的。

如果一个sql_handle包含的语句很多，比如是一个很长的存储过程，那么我们还可以使用一个有力的信息：executionStack中的

line

tag.这条语句表明了到底是哪一个sql直接导致了死锁。如果一条statement中又包含了很多表，那么还需要和死锁的资源结合起来判断是哪个表或

索引的数据发生了死锁。

如何提取并分析sqlserver的日志中的modify日志

定期分析sqlserver日志是DBA很重要的任务,那如何才能查看sqlserver日志呢?

在SQL Server 7.0和SQL Server2000中，可以用下面的命令查看：

DBCC log ( {dbid|dbname}, [, type={0|1|2|3|4}] )

参数:

Dbid or dbname - 任一数据库的ID或名字

type - 输出结果的类型:

0 - 最少信息(operation, context, transaction id)

1 - 更多信息(plus flags, tags, row length)

2 - 非常详细的信息(plus object name, index name,page id, slot id)

3 - 每种操作的全部信息

4 - 每种操作的全部信息加上该事务的16进制信息

默认 type = 0

要查看MSATER数据库的事务日志可以用以下命令：

DBCC log (master)

释放日志空间

1.清空日志

DUMP TRANSACTION 库名 WITH NO_LOG

2.截断事务日志：

BACKUP LOG 数据库名 WITH NO_LOG

3.收缩数据库文件(如果不压缩,数据库的文件不会减小

企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件

--选择日志文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了

--选择数据文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了

也可以用SQL语句来完成

--收缩数据库

DBCC SHRINKDATABASE(客户资料)

--收缩指定数据文件,1是文件号,可以通过这个语句查询到:select * from sysfiles

DBCC SHRINKFILE(1)

4.为了最大化的缩小日志文件(如果是sql 7.0,这步只能在查询分析器中进行)

a.分离数据库:

企业管理器--服务器--数据库--右键--分离数据库

b.在我的电脑中删除LOG文件

c.附加数据库:

企业管理器--服务器--数据库--右键--附加数据库

此法将生成新的LOG，大小只有500多K

或用代码：

下面的示例分离 pubs，然后将 pubs 中的一个文件附加到当前服务器。

a.分离

E X E C sp_detach_db @dbname = 'pubs'

b.删除日志文件

c.再附加

E X E C sp_attach_single_file_db @dbname = 'pubs',

@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

5.为了以后能自动收缩,做如下设置:

企业管理器--服务器--右键数据库--属性--选项--选择"自动收缩"

--SQL语句设置方式:

E X E C sp_dboption '数据库名', 'autoshrink', 'TRUE'

6.如果想以后不让它日志增长得太大

企业管理器--服务器--右键数据库--属性--事务日志

--将文件增长限制为xM(x是你允许的最大数据文件大小)

--SQL语句的设置方式:

alter database 数据库名 modify file(name=逻辑文件名,maxsize=20)

特别注意：

请按步骤进行,未进行前面的步骤,请不要做后面的步骤

否则可能损坏你的数据库.

一般不建议做第4,6两步

第4步不安全,有可能损坏数据库或丢失数据

第6步如果日志达到上限,则以后的数据库处理会失败,在清理日志后才能恢复.

另外提供一种更简单的方法，建议大家使用。

更简单的方法：

1。右建数据库属性窗口--故障还原模型--设为简单

2。右建数据库所有任务--收缩数据库

简述SQL Server企业管理器和查询分析器的作用

它是用来对本地或者远程服务器进行管理操作的服务器应用程序查询分析器：sqlserver2000查询分析器是一种图形工具，它允许用户输入和执行sql语句，并返回语句的执行结果。

一、企业管理器是SQLServer2000中最重要的一个产品组件。用户和系统管理员通过企业管理器不仅能够配置系统环境和管理SQLServer，而且所有SQLServer对象的建立与管理都可以通过它来完成。企业管理器的具体功能包括:注册和管理SQLServer服务器;管理SQLServer服务;创建和管理数据库及各种数据库对象;备份和恢复数据库;对SQLServer系统进行安全管理;编写和执行T-SQL脚本等。

二、企业管理器，它具有一个遵从微软管理控制台(MMC)的管理界面。左窗格以层叠列表的形式(树型)显示注册的所有SQLServer服务器，以及每个服务器中存储的数据库对象和提供的服务;右窗格显示树型目录中所选择目录项的具体内容。

三、企业管理器和查询分析器都是服务器端集成的工具，我们可以通过企业管理器查看数据库的结构和相关的对象，而用查询分析器模拟客户端的功能，这就类似联接数据库的操作，用查询分析器可以在本地就把数据库联接的问题解决。在“控制台根目录”下，有着我们要管理的SQLServer服务器，顺着它逐级展开，展开每一个节点时，右边的主界面中都会显示这个节点的内容。我们可以一直看到我们的SQLServer数据库连接甚至更多。

四、菜单，选中“SQLServer服务器”，查看一下“操作”菜单上的内容；再看一下“SQLServer服务器”的右键菜单，会发现它们完全一样。我们再选中“数据库”这个节点，查看一下右键菜单和“操作”菜单的子菜单，它们还是完全一样。这说明，“控制台”中的菜单，它们的内容不是一成不变的，而是由“控制台”所管理的内容来决定的。

五、“查看”菜单，它包含有“大图标”、“小图标”、“列表”、“详细信息”几个选项，我们可以通过它来设定界面中的内容以什么方式来显示。下面的“自定义”命令可以打开“自定义视图”对话框，我们可以通过这个对话框来更改选项以显示或者隐藏MMC中的项目，例如“控制台树”或者“标准工具栏”等。“工具”菜单里面的内容是SQLServer所特有的，单独的“控制台”不会有这项功能，它里面的内容全部是针对“SQLServer企业管理器”的。我们可以通过它来调度作业、打开“SQL查询分析器”、备份和还原数据库以及管理SQLServer消息等。而且，“SQLServer企业管理器”自带了许多向导，我们也可以在这里启动这些向导(事实上我们绝大多数情况下是通过这里启动这些向导)。

sqlserver executionstack怎么分析

1，victim-list没什么可分析的。

2，process-list中关于各个process的详细信息很重要。

3，再看process中的inputbuf。这个tag表明了process正在运行的语句，因此对于定位死锁非常重要。但这里有一个问题，比如上例中，inputbuf是一个存储过程，其中又嵌套了很多其他的存储过程，而我们需要在其中找出直接导致死锁的语句并优化，从而解决或减少死锁。自此我们已经有的信息是：导致死锁的语句由inputbuf中的语句调用，同时导致死锁的语句必定是对表MatchService的修改语句。如果存储过程很简单，到此DBA已经能够找到直接导致死锁的sql了，分析过程到此结束。而如果存储过程很复杂，则需要进一步分析。

4，现在再进一步考察tag, executionStack。executionStack表明了死锁发生时，由该process调用的，正在运行的所有sql。上例中有4条sql。同时仔细观察上例可以发生，两个process的executionStack是完全相同的，因此考察一个就可以了。另外，如果procname不为空则直接得到了sql，但上例中该tag为空。

我们可能还需要找出包含该sql的具体存储过程，然后进行优化。出了用sql查询外，推荐使用一个叫“SQL Search”的第三方工具，很方便，免费的。

如何显示SQLServer 查询分析器的行号

只需要在查询分析器中设置，

操作如下

工具-

选项-

文本编辑器-

所有语言-

常规-

显示-

行号

也可以参考下列图片：