高分求助,急求各位帮助SQL IP远程稳定连接!

[zhouxujian]

问题是这样的,我有几个网站,
WEB程序是用net开发的,数据库是用Sqlserver2000数据库的.
WEB程序是放在杭州电信,数据库是架在上海电信的,然后,两个是用IP,帐户和密码,这样远程相联的.

这样运行了大概有三个月了,都没有出现什么问题,因为是新站人气不是很高,现在因为大概所有站加起来流量大概有一天有一万IP左右,最新老是高峰期出现NET与数据库超时,连不上.

现在我解决的方法就是,上服务器,重启SQLserver服务器,有的时候,重启了也不行,要先停止,再重启,这样反复好多次才行.

因为,这种情况,一天至少有二三十次,所以我想求大家给一个解决方法,帮忙分析一下问题也可以,提供思路也可以,在此谢谢大家了.

[OracleRoob]

带宽不够？

[子陌红尘]

自己找一个测试环境，运行WEB程序打开不同的页面，并在查询分析器中检查进程的状况。

[CathySun118]

1.可能是带宽的问题
2.可能是服务器的配置相对较低

[zhouxujian]

首先多谢你们回复,带宽都是电信机房的,100M共用的,带宽应该不是问题吧,有没有办法如何看SQL资源用在哪里,

[zhouxujian]

服务器还算可以的,2G内存.

[OracleRoob]

如果在高峰时段经常出现，且较有规律，应该就是带宽问题。

或者你的程序的实现方式有待改进。

[子陌红尘]

楼主的程序中有没有使用连接池？

在查询分析器中执行SP_WHO，判断是那些进程占用了系统资源没有释放。

[zhouxujian]

我只是用了普通的连接方法,
请问楼上,如何执行sp_who,具体如何查看资源释放

[OracleRoob]

查询分析器中执行系统SP：

exec sp_who

或

sp_who

[zhouxujian]

请问查出来,怎样是正常的,怎样的不正常的.

[子陌红尘]

请问查出来,怎样是正常的,怎样的不正常的.
-------------------------------------------------------------------------------------
如果存在大量的进程没有释放，而实际上Web页面的所有连接已经关闭，则说明程序存在问题

[OracleRoob]

--用如下存储过程查一下，是哪些进程锁定了资源



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 @sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int

declare @charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)

declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(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

[zhouxujian]

还是一个明显的情况,
就是,当我网站打不开时,出现超时的时候,我去服务器上打开SQL的时候,SQL企业管理器,就变得非常非常的慢,就是卡死一样,基本上就是打不开,但是,我把SQL服务器停止后,又重新启动一下,打开SQL企业管理器就变得正常了.这样,我再去连接网站,就可台正常访问了,但是也坚持不了几分钟.

这样,应该不是带宽问题吧.

[zhouxujian]

回楼上这位大哥，我用了你的存储，我把结果发上来，执行时间大概在33秒，注，这个时候的结果，是网站可以正常访问的，等一下，我把网站不能正常访问的也发上来．

[zhouxujian]

51 sqmaster Blog_PersonLog_zh_cn TAB IS GRANT . sqmaster .Net SqlClient Data Provider sleeping 0
53 sqmaster Blog_PersonLog_zh_cn PAG S GRANT . sqmaster .Net SqlClient Data Provider RUNNABLE 21 1:249353
53 sqmaster Blog_PersonLog_zh_cn PAG S GRANT . sqmaster .Net SqlClient Data Provider RUNNABLE 21 1:249355
53 sqmaster Blog_PersonLog_zh_cn PAG S GRANT . sqmaster .Net SqlClient Data Provider RUNNABLE 21 1:249367
53 sqmaster Blog_PersonLog_zh_cn PAG S GRANT . sqmaster .Net SqlClient Data Provider RUNNABLE 21 1:249360
53 sqmaster Blog_PersonLog_zh_cn PAG S GRANT . sqmaster .Net SqlClient Data Provider RUNNABLE 21 1:249362
53 sqmaster Blog_PersonLog_zh_cn PAG S GRANT . sqmaster .Net SqlClient Data Provider RUNNABLE 21 1:249364
53 sqmaster Blog_PersonLog_zh_cn PAG S GRANT . sqmaster .Net SqlClient Data Provider RUNNABLE 21 1:249358
53 sqmaster Blog_PersonLog_zh_cn TAB IS GRANT . sqmaster .Net SqlClient Data Provider RUNNABLE 0
54 sqmaster sysobjects KEY S GRANT . sqmaster .Net SqlClient Data Provider RUNNABLE 2 (9e038b2bd5c1)
54 sqmaster sysobjects KEY S GRANT . sqmaster .Net SqlClient Data Provider RUNNABLE 2 (320367c4343a)
54 sqmaster Blog_Menu_zh_cn TAB IS GRANT . sqmaster .Net SqlClient Data Provider RUNNABLE 0
54 sqmaster Blog_PersonLog_zh_cn TAB Sch-S GRANT . sqmaster .Net SqlClient Data Provider RUNNABLE 0
55 sqmaster Blog_PersonLog_zh_cn TAB IS GRANT . sqmaster .Net SqlClient Data Provider sleeping 0
55 sqmaster Blog_PersonLog_zh_cn PAG S GRANT . sqmaster .Net SqlClient Data Provider sleeping 23 1:253982
56 sqmaster Blog_PersonLog_zh_cn PAG S GRANT . sqmaster .Net SqlClient Data Provider RUNNABLE 21 1:249458
56 sqmaster Blog_PersonLog_zh_cn PAG S GRANT . sqmaster .Net SqlClient Data Provider RUNNABLE 21 1:249441