[对比问题]一样的数据库,为何情况会有这么大的不同!
一样的数据库，表结构，存储过程完全相同
一个是4月13日，一个是4月16日的，
表中的数据都差不多，主要的几个表才3-5万数据
最多的一个表中也才11万数据，
出现的奇怪现象是4月13日的数据库上计算报表没问题，报表5秒就出来了
4月16日的数据库上计算报表就超时，1分钟都没反映。
测试环境相同，在同一台机器上。
各位老大，我改如何测试，如何解决这个问题。
[解决办法]
--用sp_wholock监测一下，到底锁定了哪些资源。
CREATE PROCEDURE dbo.sp_wholock
AS
--create procedure sp_WhoLock with ENCRYPTION as
-------------------------
-- 一、根据sp_who改编产生SPID对应的用户#who表
-------------------------
if (object_id( 'tmp_dbuser ') is not null)
drop table tmp_dbuser
if (object_id( 'tempdb..#tb1_sysprocesses ') is not null)
drop table #tb1_sysprocesses
declare @loginame sysname
set @loginame= NULL
set nocount on
declare @retcode int
declare @sidlowvarbinary(85)
,@sidhighvarbinary(85)
,@sid1varbinary(85)
,@spidlowint
,@spidhighint
declare@charMaxLenLoginNamevarchar(6)
,@charMaxLenDBNamevarchar(6)
,@charMaxLenCPUTimevarchar(10)
,@charMaxLenDiskIOvarchar(10)
,@charMaxLenHostNamevarchar(10)
,@charMaxLenProgramNamevarchar(10)
,@charMaxLenLastBatchvarchar(10)
,@charMaxLenCommandvarchar(10)
declare
@charsidlowvarchar(85)
,@charsidhighvarchar(85)
,@charspidlowvarchar(11)
,@charspidhighvarchar(11)
select @retcode = 0 -- 0=good ,1=bad.
--------defaults
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))
select @spidlow = 0 ,@spidhigh = 32767
IF (@loginame IS NULL) --Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED
-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from master.dbo.syslogins where loginname = @loginame)
select @sid1 = sid from master.dbo.syslogins where loginname = @loginame
IF (@sid1 IS NOT NULL) --Parm is a recognized login name.
begin
select @sidlow = suser_sid(@loginame) ,@sidhigh = suser_sid(@loginame)
GOTO LABEL_17PARM1EDITED
end
IF (lower(@loginame) IN ( 'active ')) --Special action, not sleeping.
begin
select @loginame = lower(@loginame)
GOTO LABEL_17PARM1EDITED
end
IF (patindex ( '%[^0-9]% ' , isnull(@loginame, 'z ')) = 0) --Is a number.
begin
select @spidlow = convert(int, @loginame) ,@spidhigh = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end
RaisError(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN
LABEL_17PARM1EDITED:
-------------------- Capture consistent sysprocesses. -------------------
SELECT
spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame)) as loginname
,spid as 'spid_sort '
,substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char '
INTO #tb1_sysprocesses
from master.dbo.sysprocesses (nolock)
--------Screen out any rows?
IF (@loginame IN ( 'active '))
DELETE #tb1_sysprocesses
where lower(status) = 'sleeping ' and
upper(cmd) IN (
'AWAITING COMMAND '
, 'MIRROR HANDLER '
, 'LAZY WRITER '
, 'CHECKPOINT SLEEP '
, 'RA MANAGER '
) and
blocked = 0
--------Prepare to dynamically optimize column widths.
Select
@charsidlow = convert(varchar(85),@sidlow)
,@charsidhigh = convert(varchar(85),@sidhigh)
,@charspidlow = convert(varchar,@spidlow)
,@charspidhigh = convert(varchar,@spidhigh)
SELECT
@charMaxLenLoginName =convert( varchar,isnull( max( datalength(loginname)) ,5))
,@charMaxLenDBName =convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6))
,@charMaxLenCPUTime =convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7))
,@charMaxLenDiskIO=convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6))
,@charMaxLenCommand =convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7))
,@charMaxLenHostName =convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)),@charMaxLenProgramName =convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11))
,@charMaxLenLastBatch =convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9))
from #tb1_sysprocesses
where
--sid > = @sidlow
--and sid <= @sidhigh
--and
spid > = @spidlow and spid <= @spidhigh
--------Output the report.
EXECUTE(
'
SET nocount off
SELECT
SPID = convert(char(5),spid)
,Status =
CASE lower(status)
When ' 'sleeping ' ' Then lower(status)
Else upper(status)
END
,Login = substring(loginname,1, ' + @charMaxLenLoginName + ')
,HostName =
CASE hostname
When Null Then ' ' . ' '
When ' ' ' ' Then ' ' . ' '
Else substring(hostname,1, ' + @charMaxLenHostName + ')
END
,BlkBy =
CASE isnull(convert(char(5),blocked), ' '0 ' ')
When ' '0 ' ' Then ' ' . ' '
Else isnull(convert(char(5),blocked), ' '0 ' ')
END
,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1, ' + @charMaxLenDBName + ')
,Command = substring(cmd,1, ' + @charMaxLenCommand + ')
,CPUTime = substring(convert(varchar,cpu),1, ' + @charMaxLenCPUTime + ')
,DiskIO = substring(convert(varchar,physical_io),1, ' + @charMaxLenDiskIO + ')
,LastBatch = substring(last_batch_char,1, ' + @charMaxLenLastBatch + ')
,ProgramName = substring(program_name,1, ' + @charMaxLenProgramName + ')
into tmp_dbuser --Added line by 王建军
from #tb1_sysprocesses --Usually DB qualification is needed in exec().
where spid > = ' + @charspidlow + '
and spid <= ' + @charspidhigh + '
-- (Seems always auto sorted.) order by spid_sort
SET nocount on
'
)
/*****AKUNDONE: removed from where-clause in above EXEC sqlstr
sid > = ' + @charsidlow + '
and sid <= ' + @charsidhigh + '
and
**************/
LABEL_86RETURN:
if (object_id( 'tempdb..#tb1_sysprocesses ') is not null)
drop table #tb1_sysprocesses
-----------------------
-- 显示锁定资源 选自sp_lock系统存储过程
-----------------------
select lock.spid,
who.dbname as 数据库,
object_name(lock.objId) as 被锁定表,
lock.type as 锁类型,
lock.mode as 锁模式,
lock.status as 锁状态,
who.hostname as 用户主机,
who.login as 登录名,
who.programname as 应用程序,
who.status as 用户状态,
lock.indid,
lock.resource
from
(
select convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR '
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS '
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L '
) lock,tmp_dbuser who
where lock.spid=who.spid and who.dbname=db_name() and lock.objid> 0
order by lock.spid
if (object_id( 'tmp_dbuser ') is not null)
drop table tmp_dbuser
GO
[解决办法]
比较奇怪，会不会跟服务器机器状况有关系？
[解决办法]
出现的奇怪现象是4月13日的数据库上计算报表没问题，报表5秒就出来了
4月16日的数据库上计算报表就超时，1分钟都没反映?
1分钟后有没有出现结果，如果没有出现，SQL2000的情况下重建表索引和整理索引。
由于表的主键反复更新会出现楼主的情况。。。
偶曾遇到过在表20多万的记录，用了一段时间后，查询很慢。。。
用以下语句解决:
http://community.csdn.net/Expert/topic/5428/5428951.xml?temp=.198147
[解决办法]
楼主用事务追踪的方式，查查原因：
在计算报表时在表名后:
select * from 表名 with(nolock)--这样不会受到排它锁的影响
