sql语句大全之sqlserver2008 死锁解决方法及性能优化

当前位置:

首页 > 数据库 > sql数据库 >

sql语句大全之sqlserver2008 死锁解决方法及性能优化

关于死锁:
sp_who active --看看哪个引起的死锁, blk里面即阻塞的spid；
dbcc inputbuffer(@blk) — 可以查看是那个sql语句造成的死锁；
sp_lock --看看锁住了那个资源，objid即被锁住的资源id;
select object_name(objid) 可得到受影响的表名；
综合以上功能，我们可以写一个自动查找死锁的存储过程，如下：
查找死锁.sql
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Find_Lock
AS
BEGIN
SET NOCOUNT ON;
declare @spid int,@blk int
DECLARE cur CURSOR FOR
select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
OPEN cur
FETCH NEXT FROM cur INTO @spid,@blk
WHILE @@FETCH_STATUS = 0
begin
if @spid =0
select ' 引起死锁的进程号是 : '+ CAST(@blk AS VARCHAR(10)) + ', 其执行的 SQL 语法如下 '
else
select ' 进程号 SPID ： '+ CAST(@spid AS VARCHAR(10))+ ' 被 ' + ' 进程号 SPID ： '+ CAST(@blk AS VARCHAR(10)) +'
阻塞 , 其当前进程执行的 SQL 语法如下 '
DBCC INPUTBUFFER (@blk )
FETCH NEXT FROM cur INTO @spid,@blk
end
CLOSE cur
DEALLOCATE cur
END
GO
sqlserver性能优化

EXEC sp_configure 'show advanced option', '1' --只有这个高级选项被打开的时候，才有权限修改其他配置。
go
RECONFIGURE --运行RECONFIGURE语句进行安装，也就是说，使以上语句生效
go
sp_configure 'user connections','0' --
go
RECONFIGURE
go

go
exec sp_configure 'awe enabled','1'--内存可以支持64g
exec sp_configure 'lightweight pooling','0'--不使用nt纤程
exec sp_configure 'priority boost','1'--增加sqlserver优先级
exec sp_configure 'network packet size (b)','8192'--增加sqlserver网络包的大小
reconfigure with override
--优化数据库设置
declare @currentdatabase sysname
select @currentdatabase = db_name((select dbid from master.dbo.sysprocesses wherespid = @@spid))
exec sp_dboption @currentdatabase, 'select into/bulkcopy', 'true' --对大容量数据操作不记录日志
exec sp_dboption @currentdatabase, 'trunc. log on chkpt.', 'true' --自动截断日志
exec sp_dboption @currentdatabase, 'auto create statistics', 'true'--自动创建统计
exec sp_dboption @currentdatabase, 'auto update statistics', 'true'--自动更新统
go
如果你不做日常维护，请立即开始。
经常用 DBCC showcontig(表名) 查看表的碎片，主要查看扫描密度，如果主要的大表扫描密度 < 40%, 意味着你有严重的碎片可以用DBCC indexDefrag (数据库名，表名)整理碎片或者用DBCC DBREINDEX（表名）重建 index.

栏目列表