469,648 Members | 1,564 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,648 developers. It's quick & easy.

Enterprise Manager takes 10+ min to open

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?

Oct 23 '06 #1
6 1908
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?
Oct 23 '06 #2
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/

Oct 23 '06 #3

"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.
>

Oct 24 '06 #4
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\)"
<mooregr_deletet...@greenms.comwrote:
"Johnny Ruin" <schafer.d...@gmail.comwrote 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.

Oct 24 '06 #5
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

Oct 24 '06 #6
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\)"
<mooregr_deletet...@greenms.comwrote:
>"Johnny Ruin" <schafer.d...@gmail.comwrote 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.

Oct 26 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.