十步优化SQL Server中的数据访问
关于死锁:
- 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性能优化
- 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.
执行下面的SQL语句就知道了(下面的语句可以在SQL Server 2005及后续版本中运行,用你的数据库名替换掉这里:
- USE master
- go
- SELECT
- object_name(dt.OBJECT_ID,db_id('AdventureWorks')) Tablename,
- si.name
- IndexName,dt.avg_fragmentation_in_percent AS ExternalFragmentation,
- dt.avg_page_space_used_in_percent AS InternalFragmentation
- FROM
- (
- SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
- FROM sys.dm_db_index_physical_stats (db_id('AdventureWorks'),null,null,null,'DETAILED')
- WHERE index_id <> 0
- ) AS dt INNER JOIN AdventureWorks.sys.indexes si ON si.object_id=dt.object_id
- AND si.index_id=dt.index_id
- AND dt.avg_fragmentation_in_percent>10
- AND dt.avg_page_space_used_in_percent<75
- ORDER BY avg_fragmentation_in_percent DESC