导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

数据库每天有15分钟左右的缓慢期

wljie1010 2007-12-28 04:18:30
http://topic.csdn.net/u/20071204/16/6957a340-3009-4018-b8d7-9feea8168e2d.html
上次的贴子

今天有出现了的时候
1按潇洒老乌龟的方法还是一样

2按子陌红尘红尘的方法,执行下面的脚本,总共执行6分半钟,800多条记录


--查看锁信息
create table #t(req_spid int,obj_name sysname)

declare @s nvarchar(4000)
,@rid int,@dbname sysname,@id int,@objname sysname

declare tb cursor for
select distinct req_spid,dbname=db_name(rsc_dbid),rsc_objid
from master..syslockinfo where rsc_type in(4,5)
open tb
fetch next from tb into @rid,@dbname,@id
while @@fetch_status=0
begin
set @s='select @objname=name from ['+@dbname+']..sysobjects where id=@id'
exec sp_executesql @s,N'@objname sysname out,@id int',@objname out,@id
insert into #t values(@rid,@objname)
fetch next from tb into @rid,@dbname,@id
end
close tb
deallocate tb

select 进程id=a.req_spid
,数据库=db_name(rsc_dbid)
,类型=case rsc_type when 1 then 'NULL 资源(未使用)'
when 2 then '数据库'
when 3 then '文件'
when 4 then '索引'
when 5 then '表'
when 6 then '页'
when 7 then '键'
when 8 then '扩展盘区'
when 9 then 'RID(行 ID)'
when 10 then '应用程序'
end
,对象id=rsc_objid
,对象名=b.obj_name
,rsc_indid
from master..syslockinfo a left join #t b on a.req_spid=b.req_spid

go
drop table #t

------------------------------------------



补充说明:

1当出现问题的时候 ,我去服务器上打开其他表返回所有行都没有问题,就是BI_Employee返回所有行打不开.报操作已取消
2在查询分析器里面执行什么语句都不好使,就像上面那段脚本一样,执行的过程就像死了一样.但是经过很长的时间执行完之后,一切都好使了.什么表才都能打开了,什么查询都能执行了

----------------------------------------------------

上面查询死锁的结果:

59 TOPISDB71 页 2018366505 sysindexes 1
59 TOPISDB71 页 2018366505 ServiceRequistion 1
59 TOPISDB71 页 2018366505 SRBudget 1
59 TOPISDB71 页 2018366505 SRProProve 1
59 TOPISDB71 页 2018366505 SRWorkload 1
59 TOPISDB71 页 2018366505 BI_Employee 1
59 TOPISDB71 页 2018366505 SRSchedule 1
59 TOPISDB71 页 2018366505 SRMaterial 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 SystemLog 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysproperties 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 syscolumns 1
59 TOPISDB71 页 2018366505 syscomments 1
59 TOPISDB71 页 2018366505 syspermissions 1
59 TOPISDB71 页 2018366505 BI_Employee 1
59 TOPISDB71 页 2018366505 sysobjects 1
59 TOPISDB71 页 2018366505 sysindexes 1
59 TOPISDB71 页 2018366505 ServiceRequistion 1
59 TOPISDB71 页 2018366505 SRBudget 1
59 TOPISDB71 页 2018366505 SRProProve 1
59 TOPISDB71 页 2018366505 SRWorkload 1
59 TOPISDB71 页 2018366505 BI_Employee 1
59 TOPISDB71 页 2018366505 SRSchedule 1
59 TOPISDB71 页 2018366505 SRMaterial 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 SystemLog 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysproperties 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 sysdepends 1
59 TOPISDB71 页 2018366505 syscolumns 1
59 TOPISDB71 页 2018366505 syscomments 1
59 TOPISDB71 页 2018366505 syspermissions 1
59 TOPISDB71 页 2018366505 BI_Employee 1
59 TOPISDB71 页 2018366505 sysobjects 1
59 TOPISDB71 页 2018366505 sysindexes 1
68 tempdb 表 88750698 NULL 0
...全文
149 点赞 收藏 15
写回复
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
w2jc 2007-12-29
数据库每天有15分钟左右的缓慢期
---------------------------------
每天都固定时间发生?还是随机的?
就是一个表有问题?
回复
-狙击手- 2007-12-29
1、考虑锁
2、 DBCC CHECKTABLE tablename
  DBCC INDEXDEFRAG





回复
云中客 2007-12-29
还是感觉是锁的问题

楼主不要急于从论坛中得到答案,自己仔细分析一下问题!!!

回复
SCLIREN 2007-12-29
我也来顶下,15分钟左右的缓慢期是什么意思?
回复
lzhs 2007-12-28
是固定的时间吗?
会不会其他应用锁定了这张表?
回复
dawugui 2007-12-28
至于具体的问题.就只有你自己去找了.
回复
dawugui 2007-12-28
--查看锁信息

exec sp_who

----------------------------------------------
create table #t(req_spid int,obj_name sysname)

declare @s nvarchar(4000)
,@rid int,@dbname sysname,@id int,@objname sysname

declare tb cursor for
select distinct req_spid,dbname=db_name(rsc_dbid),rsc_objid
from master..syslockinfo where rsc_type in(4,5)
open tb
fetch next from tb into @rid,@dbname,@id
while @@fetch_status=0
begin
set @s='select @objname=name from ['+@dbname+']..sysobjects where id=@id'
exec sp_executesql @s,N'@objname sysname out,@id int',@objname out,@id
insert into #t values(@rid,@objname)
fetch next from tb into @rid,@dbname,@id
end
close tb
deallocate tb

select 进程id=a.req_spid
,数据库=db_name(rsc_dbid)
,类型=case rsc_type when 1 then 'NULL 资源(未使用)'
when 2 then '数据库'
when 3 then '文件'
when 4 then '索引'
when 5 then '表'
when 6 then '页'
when 7 then '键'
when 8 then '扩展盘区'
when 9 then 'RID(行 ID)'
when 10 then '应用程序'
end
,对象id=rsc_objid
,对象名=b.obj_name
,rsc_indid
from master..syslockinfo a left join #t b on a.req_spid=b.req_spid

go
drop table #t
回复
dawugui 2007-12-28
我现在的问题是

1当出现问题的时候 ,我去服务器上打开其他表返回所有行都没有问题,就是BI_Employee返回所有行打不开.报操作已取消
----------------

2在查询分析器里面执行什么语句都不好使,就像上面那段脚本一样,执行的过程就像死了一样.但是经过很长的时间执行完之后,一切都好使了.BI_Employee表才能打开了,什么查询也都没有问题


只能说明bi_employee被锁住了.

查看一下锁信息.
回复
wljie1010 2007-12-28
潇洒老乌龟:

800多条记录是执行那段 sql脚本的记录数目,应该是死锁的记录吧

你这个表总共有多少条记录?
我这个表总共是1024条记录

我现在的问题是

1当出现问题的时候 ,我去服务器上打开其他表返回所有行都没有问题,就是BI_Employee返回所有行打不开.报操作已取消
2在查询分析器里面执行什么语句都不好使,就像上面那段脚本一样,执行的过程就像死了一样.但是经过很长的时间执行完之后,一切都好使了.BI_Employee表才能打开了,什么查询也都没有问题
回复
dawugui 2007-12-28
你现在的问题还是
SELECT * FROM Employee WHERE LoginName='admin'

这句慢?
回复
dawugui 2007-12-28
哦, 800多条.
回复
dawugui 2007-12-28
SELECT * FROM Employee WHERE LoginName='admin'
---------------------------------------
1执行了10分钟没有反映.
2I/O也不大.
3偶尔发生的.大概一星期发生一次吧
4Employee 存在主建 IDKey nvarchar(64) 聚集索引,LoginName 上有非聚集索引
5IDKey 被40 多张表引用为外建
-----------
于是我取消查询分析后,再执行这句语句就1秒不到.请问是什么原因导致这种偶然问题的发生

----------------------------------------------------------------------

你这个表总共有多少条记录?
回复
wljie1010 2007-12-28
海兄说的网络问题和硬件问题也应该不存在


发生时我去数据库服务器上有健打开这个BI_Employee表,
返回所有行,它基本上显示不出来.
打开其他表没有任何问题


回复
wljie1010 2007-12-28
rsc_indid 也有好多是3的记录

59 TOPISDB71 键 597577167 ServiceRequistion 3
59 TOPISDB71 键 597577167 SRBudget 3
59 TOPISDB71 键 597577167 SRProProve 3
59 TOPISDB71 键 597577167 SRWorkload 3
59 TOPISDB71 键 597577167 BI_Employee 3
59 TOPISDB71 键 597577167 SRSchedule 3
59 TOPISDB71 键 597577167 SRMaterial 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 SystemLog 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysproperties 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 sysdepends 3
59 TOPISDB71 键 597577167 syscolumns 3
59 TOPISDB71 键 597577167 syscomments 3
59 TOPISDB71 键 597577167 syspermissions 3
59 TOPISDB71 键 597577167 BI_Employee 3
59 TOPISDB71 键 597577167 sysobjects 3
59 TOPISDB71 键 597577167 sysindexes 3
回复
liangCK 2007-12-28
帮顶,接分.
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告