SQL 2000 Slow Response Times

Question

SQL 2000 Slow Response Times

joepin

SSC Enthusiast

Points: 147
More actions
January 13, 2004 at 12:04 pm

#83292

Hi
I'm running SQL 2000 (SP 1) on Win XP with 512 RAM.
I've got 15 databases loaded. Everything has been running great for a couple of months but since last week the general performance has gone down tremendously. Simple things like opening the Database Drop-down in Query Analyzer or displaying the databases in Enterprise Manager takes ages to complete.
To my knowledge nothing has changed on the system and no additional software has been loaded. Also note that the databases are only used on my local machine and do not have a lot of activity.
Any suggestions?
Regards
Joepin

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

Use Windows Task Manager to check how CPU performs and how much memory being used And Which processes take most resources. The performance degrade may not be caused by SQL Server.

joepin SSC Enthusiast Points: 147 More actions · Answer 2

I have already checked. SQL Server takes up the most memory with 21mb. After that it's just the normal iexplore (18mb) and explorer (15mb).

No other processes seem to be causing problems.

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 3

Is the auto-close dboption set. Sounds like this could be your problem.

Thanks

Tom

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

Memory seems not the issue. What about CPU usage and which processes consume the CPU?

joepin SSC Enthusiast Points: 147 More actions · Answer 5

My CPU Usage is on average at 20%. System Idle Process: 97 then : iexplore, OUTLOOK.exe ... The rest seem fine.

The auto-close db option is set to ON.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 6

I agreed Tom made right direction. Try to disable auto-close to see how the performance improves.

joepin SSC Enthusiast Points: 147 More actions · Answer 7

I disabled "auto-close" but it makes no difference.

The GUI is still reacting very slow as are any other connections to the DB.

Any further help will be greatly appreciated.

Thanks

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 8

Is the sqlserver running locally on your machine or just the application. When was the last time you booted the Sql machine. Did a configuration on the network change. full duplex to half duplex?

Tom