| Groups | Blog | Home |
sql server connect : SQL Server 2000 periodically running slowly
Hi all
We are having intermittent problems with SQL Server 2000 SP3 on a Windows 2000 SP4 server. A couple of times a day the CPU will rise to at least 70% usage and all connections to it will become REALLY slow i.e. three or four minutes apiece. We've run the Profiler several times. Most of the time it looks okay, though occasionally some of the "exec_cursorfetch" and "exec_cursorclose" are taking upwards of 60 seconds (?). There are also frequent "Audit Logins/Logouts" though I assume this is normal. It's been running fine for two or three years and started playing up two weeks ago. Last weekend we re-installed on a new server, re-applying the latest service packs, but to no avail. The network traffic doesn't seem to alter too much either. Can anyone offer any advice on what could cause this, or suggestions on things we could look out for? Incidentally, stopping and restarting the SQL service has stopped the problem on all but one occasion. Many thanks
Hi Andrew
Many thanks for your reply. I've looked at one or two of those links, but will be sure to browse through the remainder immediately. Task Manager does indeed usually report that the "Available Phyical Memory (K)" is down to its last 10,000 (of 1GB), though it's around that figure now and working fine. I ran a trace for 30 minutes and then the "Index Tuning" on it, and that couldn't suggest any index changes, which I thought probably suggested the statements were in order. Is that a reliable tool? Definitely odd and very frustrating. Thank you very much for the info Gary [quoted text, click to view] >-----Original Message-----
>Gary, > >You should also monitor perfmon to see what resources are bottlenecking when >this happens but I suspect you are running low on memory and may be swapping >to disk at the OS level. These links should help: > > >http://www.microsoft.com/sql/techinfo/administration/2000 /perftuning.asp >Performance WP's >http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters >http://www.sql-server- performance.com/sql_server_performance_audit.asp >Hardware Performance CheckList >http://www.sql-server- performance.com/best_sql_server_performance_tips.asp >SQL 2000 Performance tuning tips >http://www.support.microsoft.com/?id=q224587 Troubleshooting App >Performance >http://msdn.microsoft.com/library/default.asp? url=/library/en-us/adminsql/ad_perfmon_24u1.asp >Disk Monitoring > > >The cursor calls indicate your client is not optimized and may be doing a >row by row fetch which can definitely lead to performance issues. It should >return only the rows it needs and all at once, not one at a time with a >cursor. > >-- >Andrew J. Kelly SQL MVP > > >"Gary Broughton" <gbroughton@footymad.net> wrote in message >news:2f85801c46d84$3436be10$a401280a@phx.gbl... >> Hi all >> >> We are having intermittent problems with SQL Server 2000 >> SP3 on a Windows 2000 SP4 server. A couple of times a >> day the CPU will rise to at least 70% usage and all >> connections to it will become REALLY slow i.e. three or >> four minutes apiece. >> >> We've run the Profiler several times. Most of the time >> it looks okay, though occasionally some of >> the "exec_cursorfetch" and "exec_cursorclose" are taking >> upwards of 60 seconds (?). There are also >> frequent "Audit Logins/Logouts" though I assume this is >> normal. >> >> It's been running fine for two or three years and started >> playing up two weeks ago. Last weekend we re-installed >> on a new server, re-applying the latest service packs, >> but to no avail. >> >> The network traffic doesn't seem to alter too much >> either. Can anyone offer any advice on what could cause >> this, or suggestions on things we could look out for? >> >> Incidentally, stopping and restarting the SQL service has >> stopped the problem on all but one occasion. >> >> Many thanks >> Gary > > >.
Gary,
You should also monitor perfmon to see what resources are bottlenecking when this happens but I suspect you are running low on memory and may be swapping to disk at the OS level. These links should help: http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp Performance WP's http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters http://www.sql-server-performance.com/sql_server_performance_audit.asp Hardware Performance CheckList http://www.sql-server-performance.com/best_sql_server_performance_tips.asp SQL 2000 Performance tuning tips http://www.support.microsoft.com/?id=q224587 Troubleshooting App Performance http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp Disk Monitoring The cursor calls indicate your client is not optimized and may be doing a row by row fetch which can definitely lead to performance issues. It should return only the rows it needs and all at once, not one at a time with a cursor. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Gary Broughton" <gbroughton@footymad.net> wrote in message news:2f85801c46d84$3436be10$a401280a@phx.gbl... > Hi all > > We are having intermittent problems with SQL Server 2000 > SP3 on a Windows 2000 SP4 server. A couple of times a > day the CPU will rise to at least 70% usage and all > connections to it will become REALLY slow i.e. three or > four minutes apiece. > > We've run the Profiler several times. Most of the time > it looks okay, though occasionally some of > the "exec_cursorfetch" and "exec_cursorclose" are taking > upwards of 60 seconds (?). There are also > frequent "Audit Logins/Logouts" though I assume this is > normal. > > It's been running fine for two or three years and started > playing up two weeks ago. Last weekend we re-installed > on a new server, re-applying the latest service packs, > but to no avail. > > The network traffic doesn't seem to alter too much > either. Can anyone offer any advice on what could cause > this, or suggestions on things we could look out for? > > Incidentally, stopping and restarting the SQL service has > stopped the problem on all but one occasion. > > Many thanks > Gary
Personally I wouldn't trust the fact that the ITW did not make any
recommendations to say that everything is fine in that area. Profiler and perfmon will give a better indication as to how efficient your queries are especially the Reads and CPU columns of profiler. Do you have other apps running on the server other than SQL Server? If so you may want to limit the amount of memory SQL Server can use by setting the MAX memory setting down fromt he default to avoid a bunch of allocation and reallocation of memory between the OS and SQL. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Gary Broughton" <gbroughton@footymad.net> wrote in message news:042101c46d92$b4ceefd0$a501280a@phx.gbl... > Hi Andrew > > Many thanks for your reply. I've looked at one or two of > those links, but will be sure to browse through the > remainder immediately. > > Task Manager does indeed usually report that > the "Available Phyical Memory (K)" is down to its last > 10,000 (of 1GB), though it's around that figure now and > working fine. > > I ran a trace for 30 minutes and then the "Index Tuning" > on it, and that couldn't suggest any index changes, which > I thought probably suggested the statements were in > order. Is that a reliable tool? > > Definitely odd and very frustrating. > > Thank you very much for the info > Gary > > >-----Original Message----- > >Gary, > > > >You should also monitor perfmon to see what resources > are bottlenecking when > >this happens but I suspect you are running low on memory > and may be swapping > >to disk at the OS level. These links should help: > > > > > >http://www.microsoft.com/sql/techinfo/administration/2000 > /perftuning.asp > >Performance WP's > >http://www.swynk.com/friends/vandenberg/perfmonitor.asp > Perfmon counters > >http://www.sql-server- > performance.com/sql_server_performance_audit.asp > >Hardware Performance CheckList > >http://www.sql-server- > performance.com/best_sql_server_performance_tips.asp > >SQL 2000 Performance tuning tips > >http://www.support.microsoft.com/?id=q224587 > Troubleshooting App > >Performance > >http://msdn.microsoft.com/library/default.asp? > url=/library/en-us/adminsql/ad_perfmon_24u1.asp > >Disk Monitoring > > > > > >The cursor calls indicate your client is not optimized > and may be doing a > >row by row fetch which can definitely lead to > performance issues. It should > >return only the rows it needs and all at once, not one > at a time with a > >cursor. > > > >-- > >Andrew J. Kelly SQL MVP > > > > > >"Gary Broughton" <gbroughton@footymad.net> wrote in > message > >news:2f85801c46d84$3436be10$a401280a@phx.gbl... > >> Hi all > >> > >> We are having intermittent problems with SQL Server > 2000 > >> SP3 on a Windows 2000 SP4 server. A couple of times a > >> day the CPU will rise to at least 70% usage and all > >> connections to it will become REALLY slow i.e. three or > >> four minutes apiece. > >> > >> We've run the Profiler several times. Most of the time > >> it looks okay, though occasionally some of > >> the "exec_cursorfetch" and "exec_cursorclose" are > taking > >> upwards of 60 seconds (?). There are also > >> frequent "Audit Logins/Logouts" though I assume this is > >> normal. > >> > >> It's been running fine for two or three years and > started > >> playing up two weeks ago. Last weekend we re-installed > >> on a new server, re-applying the latest service packs, > >> but to no avail. > >> > >> The network traffic doesn't seem to alter too much > >> either. Can anyone offer any advice on what could > cause > >> this, or suggestions on things we could look out for? > >> > >> Incidentally, stopping and restarting the SQL service > has > >> stopped the problem on all but one occasion. > >> > >> Many thanks > >> Gary > > > > > >. > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |