sql server2008r2其中一张表不能任何操作

用户的数据库一张高频表，使用select count(*) from t1 竟然一直在转圈，显示开始，而没有end。

找尽原因不得果。把数据库备份后在恢复，可以使用几小时，之后又是老毛病抽风。

用户生产用系统，从而用户几乎停工一天。这个问题就要命了。（原因怀疑其他数据库系统导致CPU100%引起的）。

 

初步怀疑是记录有重复的原因，但是检查没有任何问题。

晚上，网上查找原因，注意到是阻塞死锁引起的。

文章https://www.cnblogs.com/luna-hehe/p/8658900.html现象完全一致。

执行

declare @spid int,@bl int
DECLARE s_cur CURSOR FOR   select  0 ,blocked   from (select * from sys.sysprocesses where  blocked>0 ) a
where not exists(select * from (select * from sys.sysprocesses where  blocked>0 ) b   where a.blocked=spid)
union
select spid,blocked from sys.sysprocesses where  blocked>0   OPEN s_cur   FETCH NEXT FROM s_cur INTO @spid,@bl   WHILE @@FETCH_STATUS = 0
begin   if @spid =0
select ' 引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + ' 进程号, 其执行的SQL 语法如下'   else
select ' 进程号SPID ：'+ CAST(@spid AS VARCHAR(10))+ ' 被' + ' 进程号SPID ：'+ CAST(@bl AS VARCHAR(10)) +' 阻塞, 其当前进程执行的SQL 语法如下'
DBCC INPUTBUFFER (@bl )   FETCH NEXT FROM s_cur INTO @spid,@bl   end   CLOSE s_cur
DEALLOCATE s_cur

出现一大推记录。真的被堵得死死的。

use master
go
--检索死锁进程
select spid, blocked, loginame, last_batch, status, cmd, hostname, program_name
from sysprocesses
where spid in
( select blocked from sysprocesses where blocked <> 0 ) or (blocked <>0)

用kill spid（进程号）杀了几个后再查，没有了。在执行sql语句，流畅的很。

用语句检查

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

结果碎片一地。通过DBCC indexDefrag (数据库名，表名)整理碎片，再查98%。问题至此解决。

和数据库打交道，我们只是简单使用。如何高并发大数据，问题就来了。因此好好研究学习数据库的原理方法维护等显得异常重要了。

==========================

 

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

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

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

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

============================

 

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. 

https://www.cnblogs.com/guo2001china/p/6415871.html

=========================================

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

 

执行下面的SQL语句就知道了需要碎片整理的表(下面的语句可以在SQL Server 2005及后续版本中运行，用你的数据库名替换掉这里的AdventureWorks)：

    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  

 

=======================================

 杀死锁的程序，检查死锁超过2分钟的：

BEGIN
    SET NOCOUNT ON;
　　declare @kill_ID int

　　declare cursor_LockID CURSOR FOR
　　　　select  blocked from sys.sysprocesses
　　　　　　where 
　　　　　　　　(
　　　　　　　　　　(spid in
　　　　　　　　　　　　( select blocked from sys.sysprocesses where blocked <> 0 ) or (blocked <>0)
　　　　　　　　　　)
　　　　　　　　　　and (blocked<>0) and DATEDIFF(minute,last_batch,getdate())>=2 
　　　　　　　　)

　　OPEN cursor_LockID　
　　FETCH NEXT FROM cursor_LockID INTO @kill_ID
　　　　BEGIN
　　　　　　IF @kill_ID<>0  EXEC('KILL '+ @kill_ID)
　　　　END

　　CLOSE cursor_LockID
　　DEALLOCATE cursor_LockID
END