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?What version of SQL are you using? Also indicate the service pack
please.
Johnny Ruin wrote:
Quote:
Originally Posted by
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?
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/|||"Johnny Ruin" <schafer.dave@.gmail.comwrote in message
news:1161612846.335787.281480@.h48g2000cwc.googlegr oups.com...
Quote:
Originally Posted by
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.
Quote:
Originally Posted by
>
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:
Quote:
Originally Posted by
"Johnny Ruin" <schafer.d...@.gmail.comwrote in messagenews:1161612846.335787.281480@.h48g2000cwc.g ooglegroups.com...
>
Quote:
Originally Posted by
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 wrote:
Quote:
Originally Posted by
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|||Thanks for your replies. I checked the version on the production
Quote:
Originally Posted by
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" <schafer.dave@.gmail.comwrote in message
news:1161696467.830677.213770@.i42g2000cwa.googlegr oups.com...
Quote:
Originally Posted by
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:
Quote:
Originally Posted by
>"Johnny Ruin" <schafer.d...@.gmail.comwrote in
>messagenews:1161612846.335787.281480@.h48g2000cwc.g ooglegroups.com...
>>
Quote:
Originally Posted by
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.
>>
>>
>
No comments:
Post a Comment