Enterprise Manager takes 10+ min to open

[Johnny Ruin]

Hi,
I've got a machine with about 750 databases on it, and growing.
Enterprise Manager is very slow coming up on this machine and I was
wondering what I could do about it. What's it doing, connecting to
every database?

[Kart]

What version of SQL are you using? Also indicate the service pack
please.

Johnny Ruin wrote:

Hi,
I've got a machine with about 750 databases on it, and growing.
Enterprise Manager is very slow coming up on this machine and I was
wondering what I could do about it. What's it doing, connecting to
every database?

[Alex Kuznetsov]

Johnny,

You can start a profiler session, start EM, and just observe what is
going on under the hood.

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

[Greg D. Moore \(Strider\)]

"Johnny Ruin" <sc**********@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

Hi,
I've got a machine with about 750 databases on it, and growing.
Enterprise Manager is very slow coming up on this machine and I was
wondering what I could do about it. What's it doing, connecting to
every database?

For one thing make sure you don't have the databases set to autoclose.

That can take a long time.

>

[Johnny Ruin]

Hi everyone,

Thanks for your replies. I checked the version on the production
machine, it's "Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug
6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) "

The profiler idea was a good one. EM goes through it's 16 lines of
'get config stuff' that it does every time it starts, but then it
executes the following query many times. I didn't count the times,
but it looks like it's once per database.

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

As I paged through the profiler output (can't seem to make that filter
thing work correctly) I realized that a) there's a lot going on this
machine and b) I haven't got a basic sql performance monitoring in my
skill set. I'll work toward that and I'll checkout auto close too.
Thanks again.

On Oct 23, 7:01 pm, "Greg D. Moore \(Strider\)"
<[e-mail]:

"Johnny Ruin" <[e-mail] in messagenews:11**********************@h48g2000cwc.g ooglegroups.com...

Hi,
I've got a machine with about 750 databases on it, and growing.
Enterprise Manager is very slow coming up on this machine and I was
wondering what I could do about it. What's it doing, connecting to
every database?For one thing make sure you don't have the databases set to autoclose.

That can take a long time.

[lucm]

Johnny Ruin wrote:

Hi,
I've got a machine with about 750 databases on it, and growing.
Enterprise Manager is very slow coming up on this machine and I was
wondering what I could do about it. What's it doing, connecting to
every database?

Go in Options and uncheck the "Server state polling" option in the
General tab. Also in you server registration uncheck the "Display SQL
Server state in console" option.

Regards,
lucm

[Dan Guzman]

Thanks for your replies. I checked the version on the production

machine, it's "Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug
6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) "

This is the RTM (unpatched) release of SQL Server. I strongly suggest you
consider upgrading to SP4.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Johnny Ruin" <sc**********@gmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...

Hi everyone,

Thanks for your replies. I checked the version on the production
machine, it's "Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug
6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) "

The profiler idea was a good one. EM goes through it's 16 lines of
'get config stuff' that it does every time it starts, but then it
executes the following query many times. I didn't count the times,
but it looks like it's once per database.

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

As I paged through the profiler output (can't seem to make that filter
thing work correctly) I realized that a) there's a lot going on this
machine and b) I haven't got a basic sql performance monitoring in my
skill set. I'll work toward that and I'll checkout auto close too.
Thanks again.

On Oct 23, 7:01 pm, "Greg D. Moore \(Strider\)"
<[e-mail]:

>"Johnny Ruin" <[e-mail] in
messagenews:11**********************@h48g2000cwc. googlegroups.com...

Hi,
I've got a machine with about 750 databases on it, and growing.
Enterprise Manager is very slow coming up on this machine and I was
wondering what I could do about it. What's it doing, connecting to
every database?For one thing make sure you don't have the databases set
to autoclose.

That can take a long time.