Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Enterprise Manager v.slow

Status
Not open for further replies.

moonshadow

Programmer
Joined
Oct 9, 2001
Messages
181
Location
GB
I have a server (twin processor 2GB RAM, 70GB hard disk) running SQL Server 7 & an instance of SQL2000. If I try and do any task on the SQL 2K instance via Enterprise Manager (either remotely or on the box), the response time can be measured in minutes. The SQL2000 instance has 58 databases, and by using SQL Profiler I can see that most of my tasks (e.g. backup/restore), result in running the following query a lot of times:
select name, DATABASEPROPERTY(name, N'IsDetached'), (case when DATABASEPROPERTY(name, N'IsShutdown') is null then -1 else DATABASEPROPERTY(name, N'IsShutdown') end), DATABASEPROPERTY(name, N'IsSuspect'), DATABASEPROPERTY(name, N'IsOffline'), DATABASEPROPERTY(name, N'IsInLoad'), (case when DATABASEPROPERTY(name, N'IsInRecovery') is null then -1 else DATABASEPROPERTY(name, N'IsInRecovery') end), (case when DATABASEPROPERTY(name, N'IsNotRecovered') is null then -1 else DATABASEPROPERTY(name, N'IsNotRecovered') end), DATABASEPROPERTY(name, N'IsEmergencyMode'), DATABASEPROPERTY(name, N'IsInStandBy'), has_dbaccess(name), status, category, status2 from master.dbo.sysdatabases

This is taking a minimum of 12 seconds to run. Additionally the memory on the machine pages like crazy (220 pages/sec), until Enterprise Manager responds to me. Anyone got any ideas why ? and how I can get it to be more responsive?
Many thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top