SQL2000 Developer really slow - any ideas why?
-
Hi,
We're using sql2000 on win2k with 1/2 GB memory and a P3 933 processor. The Developer version was installed and the machine ran fine yesterday. However, this morning it hung for 5 mins, and then seemed to right itself. When I found out it was hanging, I used Enterprise Manager Client on a separate computer, and the EM was locked. Unfortunately I couldn't get to the server maching in time to try some diagnostics. You might think this is a hardware fault, but I have seen the same thing on another computer with it's own sql2000 Developer installation on our network.
Has anyone else seen this type of behaviour
Perhaps it results from many connections being made to the Developer Edition, and using the Enterprise version will cure it. Is this a posibility, because I thought the developer and enterprise editions are meant to be identical
Thanks in advance.
Paul Ibison PhD, MCSD
email: Paul.Ibison@btinternet.com
Paul Ibison
Paul.Ibison@replicationanswers.com -
did leave Enterprise Manager running on the server did you?
Because it has been know on previous versions to tie up memory with its memory leaks, which can cause problems.
Steven
-
Thanks Steven - actually I did leave EM running on the server. However, the situation righted itself with EM still there.
Unfortunately this is a sporadic problem and although I have now stopped EM, all I can now do is wait for a reasonable length of time to check, so if anyone else has suggestions please also add them.
Paul Ibison PhD, MCSD
email: Paul.Ibison@btinternet.com
Paul Ibison
Paul.Ibison@replicationanswers.com -
I run a couple of the developer edition 512m on one of my machines as well, have not had any problems, though I don't usually have more than 1 or 2 connections open.
Try leaving a copy of QA connected - that way if it happens again you can try sp_who, maybe see what is going on.
Andy
-
Have tried using QA over the last couple of days and unfortunately it also hangs for 30 seconds or so. In terminal server I am able to monitor the activity on the SQL server machine, and I notice the SQL memory useage going up in blocks of 2mb each second, until it stops at some arbitrary value and control is resumed. This happened yesterday when I tried to open up a stored procedure in EM and I was the only actice connection! I'll be installing the Enterprise Edition this evening and will see it this helps.
Paul Ibison PhD, MCSD
email: Paul.Ibison@btinternet.com
Paul Ibison
Paul.Ibison@replicationanswers.com -
You might want to monitor in more detail - couple ideas I have - one, it could be doing a lot of disk reads to cache data based on a recent request, another is it could be extending the mdf or ldf. Just guesses!
Andy
-
Couple of things that I have found:
1. EM uses a ton of tempdb. If tempdb is getting hammered, you may have a disk IO issue. Look at the disk usage during this time, epecially the current queue length in Performance Monitor. sp_lock and sp_who2 use tempdb so they may not be of help.
2. The memory increase indicate a query using more memory. It could be DBCC or another process but is most likely a user query. You may need to use Profiler to seee all the queries taking place on the server and stop it once the server starts hanging.
I have found that it is nessasary at times to query the syslocks, sysprocesses and sysusages directly as sp_who does not give enough info and the others use tempdb. It takes a bit more work to debug but at least you get through.
-
Why would EM use tempdb? Keep in mind EM is just an interface, the only reason for it to use a temp table would be if's using a work table or a cursor. But if you've got examples -- show me:-)
Andy
-
I have had something similar with Desktop edition sql2000 with win2000. Several people had freezes up to 30sec. Our databases are about 6GB.
I hardly ever have EM open. I dont even use QAs browser much. So EM is not the cause of my freezes. May or may not be the same ? I havent worked out cause.
-
Thanks guys - I believe I've sussed it
. I did a profile which traced almost all objects and events and when the database went slow this lunchtime I checked the results. At the time of the slowdown there are loads of ShrinkFile commands being carried out. The databases all have autoshrink on, and I've now turned it off. There is a related MS article which explains that DBCC SHRINKDATABASE causes a memory leak which can slow down SQL 7.0 and eventually stop it, so I guess this is still a problem in 2000. See Article ID: Q289290.Paul Ibison PhD, MCSD
email: Paul.Ibison@btinternet.com
Paul Ibison
Paul.Ibison@replicationanswers.com -
Interesting. I always turn auto shrink off, probably explains why I've never seen this one:-)
Andy
. I did a profile which traced almost all objects and events and when the database went slow this lunchtime I checked the results. At the time of the slowdown there are loads of ShrinkFile commands being carried out. The databases all have autoshrink on, and I've now turned it off. There is a related MS article which explains that DBCC SHRINKDATABASE causes a memory leak which can slow down SQL 7.0 and eventually stop it, so I guess this is still a problem in 2000. See Article ID: Q289290.Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply