Intermittent Slowdown Of SQL Server 2000

Hi,
I have been struggling to identify the cause of an application go slow
that happens perhaps once a month or a day or two after the server has
been rebooted. The application is a web based ASP app that uses SQL
server 2000, both on the same box. This has been occuring for at least
6 months, probably longer and the only way I have found to overcome
this problem without a server reboot is to restart to the SQL service.
>From my investigations I am assuming that the problem is SQL server
related but I am unable to identify the root cause of the problem and
was wondering if anyone can offer suggestions to help me fix this
issue.
Many ThanksThis sounds like this is a blocking problem. You can try to identify
the blocking SPID. If you run this SQL Statement, you'll get the heads
for the blocking chain:
select blocker.spid
from master..sysprocesses blocker inner join master..sysprocesses
blocked
on blocker.spid = blocked.blocked
where blocker.blocked = 0
After you get the blocking spid you can chek what the process is doing
and who is running it with sp_who2 and dbcc inputbuffer.
Adi
Ham wrote:
> Hi,
> I have been struggling to identify the cause of an application go slow
> that happens perhaps once a month or a day or two after the server has
> been rebooted. The application is a web based ASP app that uses SQL
> server 2000, both on the same box. This has been occuring for at least
> 6 months, probably longer and the only way I have found to overcome
> this problem without a server reboot is to restart to the SQL service.
> >From my investigations I am assuming that the problem is SQL server
> related but I am unable to identify the root cause of the problem and
> was wondering if anyone can offer suggestions to help me fix this
> issue.
> Many Thanks|||Hi,
Try using fixed memory area for SQL Server.
Also to start with basics
Use SQL Profiler to capture the events at that time.
Thanks
Ajay
Adi wrote:
> This sounds like this is a blocking problem. You can try to identify
> the blocking SPID. If you run this SQL Statement, you'll get the heads
> for the blocking chain:
> select blocker.spid
> from master..sysprocesses blocker inner join master..sysprocesses
> blocked
> on blocker.spid = blocked.blocked
> where blocker.blocked = 0
> After you get the blocking spid you can chek what the process is doing
> and who is running it with sp_who2 and dbcc inputbuffer.
> Adi
> Ham wrote:
> > Hi,
> >
> > I have been struggling to identify the cause of an application go slow
> > that happens perhaps once a month or a day or two after the server has
> > been rebooted. The application is a web based ASP app that uses SQL
> > server 2000, both on the same box. This has been occuring for at least
> > 6 months, probably longer and the only way I have found to overcome
> > this problem without a server reboot is to restart to the SQL service.
> >
> > >From my investigations I am assuming that the problem is SQL server
> > related but I am unable to identify the root cause of the problem and
> > was wondering if anyone can offer suggestions to help me fix this
> > issue.
> >
> > Many Thanks|||Thanks all, I will try these when the problem next occurs.
Thanks Again
On 23 Jan, 10:50, "Adi" <adic...@.hotmail.com> wrote:
> This sounds like this is a blocking problem. You can try to identify
> the blocking SPID. If you run thisSQLStatement, you'll get the heads
> for the blocking chain:
> select blocker.spid
> from master..sysprocesses blocker inner join master..sysprocesses
> blocked
> on blocker.spid = blocked.blocked
> where blocker.blocked = 0
> After you get the blocking spid you can chek what the process is doing
> and who is running it with sp_who2 and dbcc inputbuffer.
> Adi
>
> Ham wrote:
> > Hi,
> > I have been struggling to identify the cause of an application go slow
> > that happens perhaps once a month or a day or two after the server has
> > been rebooted. The application is a web based ASP app that usesSQL
> > server 2000, both on the same box. This has been occuring for at least
> > 6 months, probably longer and the only way I have found to overcome
> > this problem without a server reboot is to restart to theSQLservice.
> > >From my investigations I am assuming that the problem isSQLserver
> > related but I am unable to identify the root cause of the problem and
> > was wondering if anyone can offer suggestions to help me fix this
> > issue.
> > Many Thanks- Hide quoted text -- Show quoted text -