首页 > 代码库 > sqlserver2008 死锁解决方法及性能优化方法

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

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

原文: http://blog.csdn.net/kuui_chiu/article/details/48621939

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

http://tech.it168.com/a2009/1125/814/000000814758_2.shtml

 

关于死锁:

 

[sql] view plain copy
 
  1. sp_who active  --看看哪个引起的死锁, blk里面即阻塞的spid;  
  2. dbcc inputbuffer(@blk) -- 可以查看是那个sql语句造成的死锁;  
  3. sp_lock  --看看锁住了那个资源,objid即被锁住的资源id;  
  4. select object_name(objid) --可得到受影响的表名;  
 


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

 

查找死锁.sql

[sql] view plain copy
 
  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  



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

经常用 

 

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



 

 

http://bbs.51cto.com/thread-1024392-1.html 

 

 

执行下面的SQL语句就知道了(下面的语句可以在SQL Server 2005及后续版本中运行,用你的数据库名替换掉这里的AdventureWorks):

 

 

[sql] view plain copy
 
  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  

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