sqlserver2008 死锁解决方法及性能优化方法

待我称王封你为后i 2024-02-19 18:50 40阅读 0赞

十步优化SQL Server中的数据访问

关于死锁:

1.  sp\_who active --看看哪个引起的死锁, blk里面即阻塞的spid；
2.  dbcc inputbuffer(@blk) -- 可以查看是那个sql语句造成的死锁；
3.  sp\_lock --看看锁住了那个资源，objid即被锁住的资源id;
4.  select object\_name(objid) --可得到受影响的表名；

综合以上功能，我们可以写一个自动查找死锁的存储过程，如下：

查找死锁.sql

1.  SET ANSI\_NULLS ON
2.  GO
3.  SET QUOTED\_IDENTIFIER ON
4.  GO
5.  CREATE PROCEDURE Find\_Lock
6.  AS
7.  BEGIN
8.  SET NOCOUNT ON;
9.  declare @spid int,@blk int
10. DECLARE cur CURSOR FOR
11. select 0 ,blocked
12. from (select \* from sysprocesses where blocked>0 ) a
13. where not exists(select \* from (select \* from sysprocesses where blocked>0 ) b
14. where a.blocked=spid)
15. union select spid,blocked from sysprocesses where blocked>0
16. OPEN cur
17. FETCH NEXT FROM cur INTO @spid,@blk
18. WHILE @@FETCH\_STATUS = 0
19. begin
20. if @spid =0
21. select '引起死锁的进程号是 : '+ CAST(@blk AS VARCHAR(10)) + ', 其执行的 SQL 语法如下 '
22. else
23. select '进程号 SPID ：'+ CAST(@spid AS VARCHAR(10))+ '被进程号 SPID ： '+ CAST(@blk AS VARCHAR(10)) +'阻塞 , 其当前进程执行的 SQL 语法如下'
24. DBCC INPUTBUFFER (@blk )
25. FETCH NEXT FROM cur INTO @spid,@blk
26. end
27. CLOSE cur
28. DEALLOCATE cur
29. END
30. GO
31. \--sqlserver性能优化
32. go
33. exec sp\_configure ”awe enabled”,”1″–内存可以支持64g
34. exec sp\_configure ”lightweight pooling”,”0″–不使用nt纤程
35. exec sp\_configure ”priority boost”,”1″–增加sqlserver优先级
36. exec sp\_configure ”network packet size (b)”,”8192″–增加sqlserver网络包的大小
37. reconfigure with override
38. –优化数据库设置
39. declare @currentdatabase sysname
40. select @currentdatabase = db\_name((select dbid from master.dbo.sysprocesses wherespid = @@spid))
41. exec sp\_dboption @currentdatabase, ‘select into/bulkcopy’, ‘true’ –对大容量数据操作不记录日志
42. exec sp\_dboption @currentdatabase, ‘trunc. log on chkpt.’, ‘true’ –自动截断日志
43. exec sp\_dboption @currentdatabase, ‘auto create statistics’, ‘true’–自动创建统计
44. exec sp\_dboption @currentdatabase, ‘auto update statistics’, ‘true’–自动更新统
45. go

如果你不做日常维护，请立即开始。

经常用

1.  DBCC showcontig(表名) 查看表的碎片， 主要查看扫描密度 ，如果主要的大表扫描密度 < 40%, 意味着你有严重的碎片可以用
2.  DBCC indexDefrag (数据库名，表名)整理碎片或者用
3.  DBCC DBREINDEX（表名） 重建 index.

执行下面的SQL语句就知道了(下面的语句可以在SQL Server 2005及后续版本中运行，用你的[数据库][Link 1]名替换掉这里：

1.  USE master
2.  go
3.  SELECT
4.  object\_name(dt.OBJECT\_ID,db\_id('AdventureWorks')) Tablename,
5.  si.name
6.  IndexName,dt.avg\_fragmentation\_in\_percent AS ExternalFragmentation,
7.  dt.avg\_page\_space\_used\_in\_percent AS InternalFragmentation
8.  FROM
9.  (
10. SELECT object\_id,index\_id,avg\_fragmentation\_in\_percent,avg\_page\_space\_used\_in\_percent
11. FROM sys.dm\_db\_index\_physical\_stats (db\_id('AdventureWorks'),null,null,null,'DETAILED')
12. WHERE index\_id <> 0
13. ) AS dt INNER JOIN AdventureWorks.sys.indexes si ON si.object\_id=dt.object\_id
14. AND si.index\_id=dt.index\_id
15. AND dt.avg\_fragmentation\_in\_percent>10
16. AND dt.avg\_page\_space\_used\_in\_percent<75
17. ORDER BY avg\_fragmentation\_in\_percent DESC

[Link 1]: http://lib.csdn.net/base/mysql