January 16, 2002 at 4:21 pm
I have just taken over a client that is having a severe performance issue re their master database. eg. it takes forever to drop down the database list in Enterprise Manager (15 minutes!).
They have a under-spec server (dual PIII, 2GB RAM, but only mirrored boot drive and mirred data drive - both UW SCSI 3, 10,000 RPM).
At present I have little choice but to have all data and log files on their data drive (boot disk not large enough to store log files).
Master DB is currently on the boot disk as per default setup.
There are approximately 30 databases (all 5-20 MB). The nature of the app they are running is that DB's get created, and their structure modified quite continuously.
I know they should improve the spec of thier server. But I do not believe that the server spec fully accounts for how slow some tasks are to perform.
Generally the operational performance of their server is OK. Its just that administering the thing is a nightmare.
Anyone have any suggestions?? Much appreciated.
January 16, 2002 at 4:26 pm
If the 'auto close' option is selected for some or all of the db's on the server, you'll see a long delay in getting the list of databases. You need to check the option for each db to be sure.
HTH
Steve Armistead
Steve Armistead,
Database Administration
Panther Systems Northwest, Inc.
Vancouver, WA
Steve
January 16, 2002 at 8:27 pm
Thanks - 'Auto Close' was set to true. I'll let you know if the speed is improved.
January 17, 2002 at 5:11 am
Is master included in the standard maintenance job? Also, is the slow perf on client machines only, or do you see the same thing if you run EM on the server itself. Nothing wrong with their hardware for the size data they have.
Andy
January 28, 2002 at 7:42 pm
'Auto Close' did not really help issue.
Master is included in standard maintenance job.
Any other ideas??
January 28, 2002 at 8:38 pm
PS. Above is slow in EM on server itself. I thought hardware insufficient as they have up to 100 concurrent users (off an intranet app).
January 28, 2002 at 9:05 pm
Out of curiousity, how is performance accessing via Query Analyzer?
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 28, 2002 at 9:27 pm
Much faster than EM.
Also Query Analyser gets blocked by EM if it is currently in the midst of dropping down the list (quite annoying!).
Its looking like I may have to rebuild the SQL Server?
January 29, 2002 at 12:54 pm
IS this SQL2K? Havne't seen this before.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 5, 2002 at 5:19 am
Make sure auto update statistics and auto create statistics are on (I had a developer turn update off and started seeing delays). Also what does the maintainence plan actually do and how large is the master DB?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply