进程详细信息，请教大虾，救急

waternewboy 2011-09-29 02:27:39

请教大虾：在SQL2005的活动监视器中，“进程详细信息-上一Transact-SQL命令批处理”中得内容怎么为空，追踪不到该进程执行的SQL语句呢，这个进程造成其它进程阻塞。救急，请各位多指导。

...全文

169 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

dawugui 2011-09-29

打赏
举报

SQL Server 2005锁的问题

SQL Server通过锁定资源来保证数据库的一致性。SQL Server中的锁不会对行、页、表或索引等资源有实际影响，它更像一个预订系统，所有任务在数据库内预订某些资源时都遵守它。过多的锁或长时间持有的锁会导致阻塞和其他问题，但锁本身也可能产生一些问题。

1 解决锁内存问题



为了确定SQL Server中锁使用的内存量，可以监视SQL Server中的“锁内存（KB）”计数器和系统监视器（Perfmon）中的“内存管理”对象。通过设置sp_configure中的锁选项，可以修改SQL Server中锁的内存配额。使用SQLServer：Locks计数器，可以了解更多关于锁行为的细节。



如果系统中的锁内存消耗完了，SQL Server不能分配更多的锁内存，session会收到消息1204：



 



The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time.



Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.



 



这个消息说得很清楚：需要增加锁的内存配额，或者减少系统中锁的数量。



如果锁占用很大的内存，应该首先尝试找出造成这么多锁的根本原因。例如，可能是SQL Server的锁升级不充分。如果是这样，就需要修改锁的配置。一旦修改了锁动态配置，就影响了锁升级的行为，由此可能造成意外的影响。



如果数据库不需要任何写访问，建议将其设置为只读的。这会降低系统中产生的锁的数量。在一个只读的数据库中，SQL Server仍会发行数据库的共享锁和读表的意向共享锁，但行锁、页锁及SERIALIZABLE隔离级别的行锁，都不会被发行。例如，对于只在夜间更新的报表数据库，用户可以将在白天对数据库的查询设置为只读的。这样做对锁内存的影响会降低，这也是SQL Server的锁管理器必须做的。还可以在同一台服务器上对只读数据库创建数据库快照，SQL Server不会在数据库快照上发行共享锁。



为了减少锁内存，同样建议将读操作与写操作分开。一种方法是把报表从一个OLTP系统中分开，通过创建报表服务器和使用事务复制或SQL Server集成服务（SSIS）来为另一个用户查询读操作的服务器获取数据。这会去掉OLTP主服务器的共享锁。如果数据库服务器能够支持这种方法，可以考虑用一个数据库快照来定期卸载读操作。在本章后面我们还可以看到使用一种基于行版本的快照隔离级别来减少读数据查询产生的锁。



2 锁超时



默认状态下，一个被阻塞的查询会无限地等待一个未被满足的锁的请求。通过使用LOCK_TIMEOUT设置，可以指定一个session锁等待的时间。当锁超时发生时，session会收到消息1222：



Lock request time out period exceeded.



使用LOCK_TIMEOUT给事务带来了问题，因为错误1222发生后，SQL Server只是退出当前程序语句，而并没有中止事务。因此需要在Transact-SQL代码中使用TRY/CATCH模块来捕获1222错误。若发生了超时，可能需要回滚事务。若要了解更多内容，可以参考Inside SQL Server 2005：The Storage Engine（《Microsoft SQL Server 2005：存储引擎》，电子工业出版社，2007）第8章的“设置锁超时”。



3 锁升级



SQL Server经常会锁定表中独立的行，尤其当更新和删除比较少的行时。但执行大规模更新时，SQL Server选择表中某行或某页的锁进行升级，以更好的使用锁内存资源。但有时锁升级会造成阻塞，我们希望减少锁升级的数量（参考KB文章323630《如何解决SQL Server会中由锁升级引起的阻塞问题》，获取更多详细信息）。



检测锁升级有很多方法，最简单的方法是使用SQL Trace/Profiler中的Lock：Escalation事件类。当升级发生时，该事件被触发。但一个升级会有多个触发，所以将它们绑定在一起很重要。



确保选择Lock:Escalation事件类的默认列，这些列提供基本信息。但我们添加以下列可能也很有用：TransactionID、DatabaseID、DatabaseName和ObjectID。因为可能看到trace中一个升级事件的多行，可以使用TransactionID将它们绑定在一起，特定对象（即表）可以使用ObjectID。



通过监视表锁的数目或者和它们的持续时间，可以检测到正在发生的升级。如果可以估计应用系统很少需要（或者曾经需要）表上的共享锁或独占锁，就可以推断无论什么时候我们看到这样的锁，它都由锁升级产生。可以通过sys.dm_tran_locks DMV在给定的时间点探测表锁。下面的查询显示了一个实例：



 



SELECT



request_session_id,



resource_type,



DB_NAME(resource_database_id) AS DatabaseName,



OBJECT_NAME(resource_associated_entity_id) AS TableName,



request_mode,



request_type,



request_status



FROM sys.dm_tran_locks AS TL



JOIN sys.all_objects AS AO



ON TL.resource_associated_entity_id = AO.object_id



WHERE request_type = ’LOCK’



AND request_status = ’GRANT’



AND request_mode IN (’X’,’S’)



AND AO.type = ’U’



AND resource_type = ’OBJECT’



AND TL.resource_database_id = DB_ID();



 



上面用来查找表锁的查询引用了sys.all_objects的目录视图，所以返回信息的范围限制在查询运行的数据库上。由于sys.dm_tran_locks没有返回锁定对象更详细的信息，就没有办法得知这个对象是否是表。这样一来，就必须加入返回那些信息的数据库的一些东西，而在这种情况下，sys.all_objects包含这些信息，而且OBJECT_NAME()函数可以返回表的名称。（实例见第1章“性能故障检修方法”。）但是，它们都只返回当前数据库的信息。因此，查询过滤器的最后一个条件限制了当前数据库中那些资源的返回行。



另一种策略是使用sp_lock系统存储过程，它返回锁类型，从而可以查看表锁。不幸的是，为了过滤sp_lock，必须抓取临时数据，然后查询它并在一个WHERE子句中过滤。可以从sp_lock存储过程中提取key并执行它，但是它只适合于查询sys.dm_tran_locks DMV并对其过滤。

解决锁升级



防止多余的锁升级的最简单的方法是减少以下批量操作的批次大小：插入、更新或删除。例如，若必须执行批量更新，可以限制行数，或锁数量的最大值5 000。我们需要尝试找到防止升级的合适数值。SQL Server的查询优化器可以检测到表遍历和任何的锁升级。



目前，对每个表防止锁升级最有趣的方法是在表上创建意向锁，这样SQL Server就不能升级锁。通常锁升级只影响很少的查询或只影响某个查询，我们可以关注有问题的表。微软KB文章323630《如何解决SQL Server中由锁升级引起的阻塞问题》给出了一个很好的例子。对于SQL Server实例数据库AdventureWorks中的表Sales.SalesOrderDetail，若要防止锁升级，如下代码可以使这张表在1小时内防止锁升级：



 



BEGIN TRAN；



SELECT *



FROM Sales.SalesOrderDetail WITH (UPDLOCK, HOLDLOCK)



WHERE 1=0;



WAITFOR DELAY ’1:00:00’；



COMMIT；



这个查询可以防止表Sales.SalesOrderDetail上的锁升级(虽然事务日志不会比没有它增加更多）。当升级尝试发生时，仍然可以在SQL Profiler中看到Lock:Escalation事务，但是通过检查sys.dm_tran_locks，我们能验证事务只采取了行锁。不幸的是，这个请求使表的一个事务无期限地open，即使它并不锁定任何行。而且，如果这张表有其他表的外键或触发器，SQL Server也会升级它的锁，所以阻止一张表的锁升级不像想象中那么简单。



一种有风险的选择是完全关掉锁升级。例如，可以设置跟踪标识1211，它禁止整个SQL Server实例的锁升级。问题是虽然这个选项可以减少阻塞，但是它会造成更多的锁，因此锁内存增加。如果系统的锁内存消耗完，会使SQL Server停止或降低它的性能。还可以使用跟踪标识1224，它禁止锁升级，直到锁管理器使用掉SQL Server实例的40%的非AWE动态分配内存。如果锁内存的数量达到可用非AWE内存的60%，锁内存就会被消耗完。



另一种降低锁升级的方法是使用查询的“行锁”或“页锁”提示。这必须在每张表每个查询上完成。遇到的问题和所有锁提示的问题相同：查询不能使用更优化的计划。如果指定“页锁”提示，SQL Server仍然会在表锁级别进行锁升级，而且在其他不需要升级的情况下禁止使用行锁，行锁会有更好的行为。



还可以设置索引选项，通过SQL Server 2005的CREATE/ALTER INDEX语句可以改善sp_indexoption系统存储过程的性能。从根本上说，通过SET选项设置ALLOW_ROW_LOCKS或ALLOW_PAGE_LOCKS为OFF，可以防止索引上的行锁或页锁。这两个选项的默认值是ON。这些选项有效地控制索引叶子节点的颗粒度，迫使SQL Server在一个较高的颗粒度起点使用锁。重申一下，通常来说这些选项没有很高的价值，因为它们阻止了可能受益于较低锁颗粒度的查询计划。



如果由于读操作产生锁升级，我们可以试着去掉数据库中的读活动，通过使用数据库快照，复制到报表数据库上，或使用一个“快照隔离”级别。

dawugui 2011-09-29

打赏
举报

在SQL Server 2005中解决死锁问题



作者：佚名  2007-05-29 



    数据库操作的死锁是不可避免的，本文并不打算讨论死锁如何产生，重点在于解决死锁，通过SQL Server 2005, 现在似乎有了一种新的解决办法。 



    将下面的SQL语句放在两个不同的连接里面，并且在5秒内同时执行，将会发生死锁。 



use Northwind

begin tran

　　insert into Orders(CustomerId) values(@#ALFKI@#)

　　waitfor delay @#00:00:05@#

　　select * from Orders where CustomerId = @#ALFKI@#

commit

print @#end tran@# 



    SQL Server对付死锁的办法是牺牲掉其中的一个，抛出异常，并且回滚事务。在SQL Server 2000，语句一旦发生异常，T-SQL将不会继续运行，上面被牺牲的连接中， print @#end tran@#语句将不会被运行，所以我们很难在SQL Server 2000的T-SQL中对死锁进行进一步的处理。 



    现在不同了，SQL Server 2005可以在T-SQL中对异常进行捕获，这样就给我们提供了一条处理死锁的途径： 



    下面利用的try ... catch来解决死锁。 



SET XACT_ABORT ON

declare @r int

set @r = 1

while @r <= 3

begin

　　begin tran

　　

　　begin try　　　

　　　　insert into Orders(CustomerId) values(@#ALFKI@#)

　　　　waitfor delay @#00:00:05@#

　　　　select * from Orders where CustomerId = @#ALFKI@#

　　　　

　　　　commit

　　　　break

　　end try

　　　　

　　begin catch

　　　　rollback

　　　　waitfor delay @#00:00:03@#

　　　　set @r = @r + 1

　　　　continue

　　end catch

end 



    解决方法当然就是重试，但捕获错误是前提。rollback后面的waitfor不可少，发生冲突后需要等待一段时间，@retry数目可以调整以应付不同的要求。 



    但是现在又面临一个新的问题: 错误被掩盖了，一但问题发生并且超过3次，异常却不会被抛出。SQL Server 2005 有一个RaiseError语句，可以抛出异常，但却不能直接抛出原来的异常，所以需要重新定义发生的错误，现在，解决方案变成了这样: 



declare @r int

set @r = 1

while @r <= 3

begin

　　begin tran

　　

　　begin try　　　

　　　　insert into Orders(CustomerId) values(@#ALFKI@#)

　　　　waitfor delay @#00:00:05@#

　　　　select * from Orders where CustomerId = @#ALFKI@#

　　　　

　　　　commit

　　　　break

　　end try

　　　　

　　begin catch

　　　　rollback

　　　　waitfor delay @#00:00:03@#

　　　　set @r = @r + 1

　　　　continue

　　end catch

end

if ERROR_NUMBER() <> 0

begin

　　declare @ErrorMessage nvarchar(4000);

　　declare @ErrorSeverity int;

　　declare @ErrorState int;

　　select

　　　　@ErrorMessage = ERROR_MESSAGE(),

　　　　@ErrorSeverity = ERROR_SEVERITY(),

　　　　@ErrorState = ERROR_STATE();

　　raiserror (@ErrorMessage,

　　　　　　　 @ErrorSeverity,

　　　　　　　 @ErrorState

　　　　　　　 );

end 



    我希望将来SQL Server 2005能够直接抛出原有异常，比如提供一个无参数的RaiseError。 



    因此方案有点臃肿，但将死锁问题封装到T-SQL中有助于明确职责，提高高层系统的清晰度。现在，对于DataAccess的代码，或许再也不需要考虑死锁问题了。

claro 2011-09-29

打赏
举报

参考：http://topic.csdn.net/u/20110929/14/f9e1ff99-99bc-477c-ab00-83de2ac70e8c.html?8679

claro 2011-09-29

打赏
举报

获取不到，是因为必须即时查询，当SPID仍存在的情况下，才可以查到。

方式一：
1、找到该进程的spid。
比如spid 是10
2、根据spid查询语句。

dbcc inputbuffer(10)

方式二：

--查询客户端连接的服务器信息，比如IP、端口、数据包大小以及查询内容。

/***************************************

--Version:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 

    Apr  2 2010 15:48:46 

    Copyright (c) Microsoft Corporation

    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

--By clArO (引用请保留此信息)--

****************************************/

select session_id,connect_time

,client_net_address --与此服务器连接的客户端的主机地址

,client_tcp_port --与该连接关联的客户端计算机上的端口号

,local_net_address --显示此连接的目标服务器的 IP 地址[只对使用 TCP 传输提供程序的连接可用]

,local_tcp_port --如果此连接使用 TCP 传输，则显示该连接的目标服务器的 TCP 端口

,net_transport --该连接使用的物理传输协议

,auth_scheme --此连接使用的 SQL Server/Windows 身份验证方案

,num_reads --此连接中已发生的读包次数

,num_writes --此连接中已发生的写数据包次数

,net_packet_size --用于信息和数据的网络包的大小

,[text] --此连接上执行的上一个请求的 SQL（句柄）语句

from sys.dm_exec_connections

CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) 

where session_id <> @@SPID --忽略自连接

order by client_net_address