Enterprise or Standard edition !

Hello,
Is there any exact database size limitation for standard edition? My
database is almost 5 GB and it's getting too slow. Do i have to switch to
enterprise?
Is there any way to check the database to see it's certainly tuned and
optimized and the only problem is the edition of SQL Server I use?
Thanks in advance,
MathewThere is no database size limitation for Standard Edition.
Have you tried running the Database Tuning Advisor and see if you can create
some additional/better indexes to support your larger database? That's
probably the first thing you should try... You might also investigate
looking at some performance counters to determine where your performance
bottlenecks are.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Mathew" <Mathew@.discussions.microsoft.com> wrote in message
news:EBA743F5-E065-4857-9976-3B1A37CB4AD2@.microsoft.com...
> Hello,
> Is there any exact database size limitation for standard edition? My
> database is almost 5 GB and it's getting too slow. Do i have to switch to
> enterprise?
> Is there any way to check the database to see it's certainly tuned and
> optimized and the only problem is the edition of SQL Server I use?
> Thanks in advance,
> Mathew|||update the statistics in the database, defrag your indexes (set the fill
ratio to 100%)
Defrag your disk
Identify the bottleneck (CPU, Memory, disk...)
How many disks have you and what is the Raid level?
"Mathew" <Mathew@.discussions.microsoft.com> wrote in message
news:EBA743F5-E065-4857-9976-3B1A37CB4AD2@.microsoft.com...
> Hello,
> Is there any exact database size limitation for standard edition? My
> database is almost 5 GB and it's getting too slow. Do i have to switch to
> enterprise?
> Is there any way to check the database to see it's certainly tuned and
> optimized and the only problem is the edition of SQL Server I use?
> Thanks in advance,
> Mathew|||Hi Mathew,
sql server, like any relational database, should be kept tuned up and
properly indexed.....there are lots and lots of books on the topic.
I don't recommend you try and learn how to tune a database one question
at a time on a newsgroup.
5GB is a small database and unless you are doing something strange you
should see good performance from it.
Peter
www.peternolan.com

Enterprise Manager very slow on XP Pro

Hi,
I have SQL 2000 Personal Edition / SP3 on XP Pro, P4/HT. I also have the
same version of SQL installed on W2K / P3. The Enterprise Manager seems to
run much slower on XP than on W2K. A simple click in the left pane (e.g. on
'databases') results in 20-30 second wait (the disk led is lit solid during
that time).
Could anyone please give me any suggestions as to what I should be looking
for on the XP machine?
Thanks,
Bogdan
> I have SQL 2000 Personal Edition / SP3 on XP Pro, P4/HT. I also have the
> same version of SQL installed on W2K / P3. The Enterprise Manager seems
to
> run much slower on XP than on W2K. A simple click in the left pane (e.g.
on
> 'databases') results in 20-30 second wait (the disk led is lit solid
during
> that time).
Do please check if your databases have the autoclose option turned on. This
is default for Personal edition, and it takes time to open all databases.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Also verify that you have no ODBC tracing turned on.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in message
news:u2kKHRKRFHA.3704@.TK2MSFTNGP12.phx.gbl...
> to
> on
> during
> Do please check if your databases have the autoclose option turned on. This
> is default for Personal edition, and it takes time to open all databases.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
|||Neither of the databases had auto-close enabled. The ODBC tracing was not
enabled (although I'm not using ODBC to connect to any of the databases and
I hope that Enterprise Manager does not use it either).
What seemed to work was SP3a. I applied it last night and things appear to
be 'normal' now.
Dejan, Tibor: thanks for your responses.
Bogdan
"Bogdan" <someone@.domain.com> wrote in message
news:%237JkLWIRFHA.1416@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have SQL 2000 Personal Edition / SP3 on XP Pro, P4/HT. I also have the
> same version of SQL installed on W2K / P3. The Enterprise Manager seems
> to run much slower on XP than on W2K. A simple click in the left pane
> (e.g. on 'databases') results in 20-30 second wait (the disk led is lit
> solid during that time).
> Could anyone please give me any suggestions as to what I should be looking
> for on the XP machine?
> Thanks,
> Bogdan
>
|||> I hope that Enterprise Manager does not use it either).
Both EM and QA uses ODBC.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bogdan" <someone@.domain.com> wrote in message news:e3FUs4URFHA.3944@.TK2MSFTNGP10.phx.gbl...
> Neither of the databases had auto-close enabled. The ODBC tracing was not enabled (although I'm
> not using ODBC to connect to any of the databases and I hope that Enterprise Manager does not use
> it either).
> What seemed to work was SP3a. I applied it last night and things appear to be 'normal' now.
> Dejan, Tibor: thanks for your responses.
> Bogdan
>
> "Bogdan" <someone@.domain.com> wrote in message news:%237JkLWIRFHA.1416@.TK2MSFTNGP10.phx.gbl...
>
|||Dejan Sarka wrote:
> Do please check if your databases have the autoclose option turned
> on. This is default for Personal edition, and it takes time to open
> all databases.
I've had a laptop that's been slow for months with QA and SQL EM and
it's been driving me crazy. Even pressing the Cancel button from the
Database properties in SQL EM caused undue database and disk activity.
Checked the databases, and sure enough, there was one user database that
mysteriously had Auto Close turned on. Probably turned it on for testing
and forgot to turn it off.
You fixed me. Thanks.
David Gugick
Imceda Software
www.imceda.com
|||If you want fast access to your database try SQL Admin Studio from
www.simego.com as this tool is just amazingly fast at connecting and
browsing SQL Server Databases.
"Bogdan" <someone@.domain.com> wrote in message
news:%237JkLWIRFHA.1416@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have SQL 2000 Personal Edition / SP3 on XP Pro, P4/HT. I also have the
> same version of SQL installed on W2K / P3. The Enterprise Manager seems
> to run much slower on XP than on W2K. A simple click in the left pane
> (e.g. on 'databases') results in 20-30 second wait (the disk led is lit
> solid during that time).
> Could anyone please give me any suggestions as to what I should be looking
> for on the XP machine?
> Thanks,
> Bogdan
>
|||Another way that I've seen autoclose turned on is when
moving a database from MSDE to MSSQL.
MSDE, IIRC, defaults to autoclose on.
jg

Quote:

Originally posted by David Gugick
Dejan Sarka wrote:
> Do please check if your databases have the autoclose option turned
> on. This is default for Personal edition, and it takes time to open
> all databases.
I've had a laptop that's been slow for months with QA and SQL EM and
it's been driving me crazy. Even pressing the Cancel button from the
Database properties in SQL EM caused undue database and disk activity.
Checked the databases, and sure enough, there was one user database that
mysteriously had Auto Close turned on. Probably turned it on for testing
and forgot to turn it off.
You fixed me. Thanks.
David Gugick
Imceda Software
www.imceda.com

Enterprise Manager Very Slow on one PC

I have had a problem with EM being extremely slow. The first time this
ocurred it was related to one users Windows Logon Profile... i.e. whichever
PC he logged onto, EM was very slow to connect and run. It turned out his
Windows Network roaming profile was corrupt and fixing this solved the
problem.
I now have the same problem on my PC but it is PC dependant... i.e. it
doesn't matter who logs onto this PC, EM is always slow. If I log onto
another PC EM runs fine.
Any ideas what may have gone wrong on my PC?
Could it be Windows registry values that are incorrect? If so is there a way
of checking these registry values?
I have tried creating a new Windows logon profile and uninstalling and
reinstalling EM. I do not want to have to resort to rebuilding the PC.
Any ideas would be great
Thanks
Martyn
Did you check to see if ODBC tracing is turned on? You can
check from the ODBC data source administrator, go to the
tracing tab. If the button on the left has the caption Stop
Tracing Now, the tracing is turned on. Click on the button
and the caption will change to start tracing now - meaning
that tracing is turned off.
-Sue
On Thu, 23 Sep 2004 01:35:03 -0700, "martynhogg"
<martynhogg@.discussions.microsoft.com> wrote:

>I have had a problem with EM being extremely slow. The first time this
>ocurred it was related to one users Windows Logon Profile... i.e. whichever
>PC he logged onto, EM was very slow to connect and run. It turned out his
>Windows Network roaming profile was corrupt and fixing this solved the
>problem.
>I now have the same problem on my PC but it is PC dependant... i.e. it
>doesn't matter who logs onto this PC, EM is always slow. If I log onto
>another PC EM runs fine.
>Any ideas what may have gone wrong on my PC?
>Could it be Windows registry values that are incorrect? If so is there a way
>of checking these registry values?
>I have tried creating a new Windows logon profile and uninstalling and
>reinstalling EM. I do not want to have to resort to rebuilding the PC.
>Any ideas would be great
>Thanks
>Martyn
|||Sue, you are a genius!!! I've posted on several sites and no-one could answer
this one. It worked! Thankyou so much!
Martyn
"Sue Hoegemeier" wrote:

> Did you check to see if ODBC tracing is turned on? You can
> check from the ODBC data source administrator, go to the
> tracing tab. If the button on the left has the caption Stop
> Tracing Now, the tracing is turned on. Click on the button
> and the caption will change to start tracing now - meaning
> that tracing is turned off.
> -Sue
> On Thu, 23 Sep 2004 01:35:03 -0700, "martynhogg"
> <martynhogg@.discussions.microsoft.com> wrote:
>
>
sql

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?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?

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

>

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

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

>

enterprise manager slow to load on shared server with 200 databases

My db is on a shared server with two hundred tiny databases. Query
analyzer connects very fast, buty Enterprise Manager stupidly reads the
schema for each of the 200 db's while it is loading the GUI. Sadly, it
freezes up for about ten minutes while loading all that stuff.
I love the way EM makes admin a breeze. Is there a way (registry tweak
or something) to make it so it will just show the databases my account
has access to (like query analyzer does)
Thanks, Tom
Nope...you can change the system stored procedures that
Enterprise Manager calls. You can learn to use Query
Analyzer more or write your own utility that works like
query analyzer. A lot of it is written using SQL-DMO. You
can also use SQL-NS for some of the user interface
components invoked with Enterprise Manager. Both SQL-DMO and
SQL-NS are documented in books online.
-Sue
On 1 Mar 2005 16:44:39 -0800, thomasamillergoogle@.yahoo.com
wrote:

>My db is on a shared server with two hundred tiny databases. Query
>analyzer connects very fast, buty Enterprise Manager stupidly reads the
>schema for each of the 200 db's while it is loading the GUI. Sadly, it
>freezes up for about ten minutes while loading all that stuff.
>I love the way EM makes admin a breeze. Is there a way (registry tweak
>or something) to make it so it will just show the databases my account
>has access to (like query analyzer does)
>Thanks, Tom
|||In addition to what Sue recommends make sure you don't have Auto Close
turned on with any of the db's.
Andrew J. Kelly SQL MVP
<thomasamillergoogle@.yahoo.com> wrote in message
news:1109724279.921121.305760@.l41g2000cwc.googlegr oups.com...
> My db is on a shared server with two hundred tiny databases. Query
> analyzer connects very fast, buty Enterprise Manager stupidly reads the
> schema for each of the 200 db's while it is loading the GUI. Sadly, it
> freezes up for about ten minutes while loading all that stuff.
> I love the way EM makes admin a breeze. Is there a way (registry tweak
> or something) to make it so it will just show the databases my account
> has access to (like query analyzer does)
> Thanks, Tom
>
|||Sorry, I don't think i have enough time on my hands to rewrite
Enterprise Manager :P
What stored procedures could be changed to speed up the way it loads
the databases when the databases node is expanded?
Sql-NS looks fascinating, i never knew about that. Looks cool!
Thanks, Tom
Sue Hoegemeier wrote:[vbcol=seagreen]
> Nope...you can change the system stored procedures that
> Enterprise Manager calls. You can learn to use Query
> Analyzer more or write your own utility that works like
> query analyzer. A lot of it is written using SQL-DMO. You
> can also use SQL-NS for some of the user interface
> components invoked with Enterprise Manager. Both SQL-DMO and
> SQL-NS are documented in books online.
> -Sue
> On 1 Mar 2005 16:44:39 -0800, thomasamillergoogle@.yahoo.com
> wrote:
the[vbcol=seagreen]
it[vbcol=seagreen]
tweak[vbcol=seagreen]
account[vbcol=seagreen]
|||Hi Tom,
No...don't think anyone has enough time to rewrite it but
thought I'd mention it as it is an option. Maybe not a
realistic one but an option nonetheless.
Anyway...it calls system stored procedures. Changing those
means you will no longer be running a supported system and
could end up with more problems than you solve. It's just
best not to go there. If you want to see what it's calling,
just run profiler and get a trace of what's executed.
Make sure you check for autoclose as Andrew mentioned...I
forgot about that. And make sure you are on the latest
service pack as there have been some fixes in some of the
service packs related to enterprise manager and it's
performance. And also make sure you don't have ODBC tracing
turned on. You can check the ODBC Administrator applet and
check the Tracing tab to see if it's on.
And then...one other option I just thought of. I don't know
about all of the third party tools but maybe one of those
has the functionality you need without some of the overhead
you are experiencing with Enterprise Manager. You may want
to look at those. You can find a list of them at:
http://www.aspfaq.com/show.asp?id=2442
Hope that helps -
-Sue
On 2 Mar 2005 08:37:15 -0800, thomasamillergoogle@.yahoo.com
wrote:
[vbcol=seagreen]
>Sorry, I don't think i have enough time on my hands to rewrite
>Enterprise Manager :P
>What stored procedures could be changed to speed up the way it loads
>the databases when the databases node is expanded?
>Sql-NS looks fascinating, i never knew about that. Looks cool!
>Thanks, Tom
>
>Sue Hoegemeier wrote:
>the
>it
>tweak
>account
|||Thanks Sue, that more than helps. Much appreciated I have learned more
from you reply then in my last hundred posts to usenet .
I passed on the sql-ns and sql-dmo items to a friend who is devleoping
a sql server front end, he *really* likes it !
Tom
|||FYI, DMO and sql-ns is being replaced by SMO in SQL2005. So he may want to
check that out as well.
Andrew J. Kelly SQL MVP
<tom.a.miller@.gmail.com> wrote in message
news:1109881960.302088.148920@.f14g2000cwb.googlegr oups.com...
> Thanks Sue, that more than helps. Much appreciated I have learned more
> from you reply then in my last hundred posts to usenet .
> I passed on the sql-ns and sql-dmo items to a friend who is devleoping
> a sql server front end, he *really* likes it !
> Tom
>
|||Good to hear and thanks!
-Sue
On 3 Mar 2005 12:32:40 -0800, tom.a.miller@.gmail.com wrote:

>Thanks Sue, that more than helps. Much appreciated I have learned more
>from you reply then in my last hundred posts to usenet .
>I passed on the sql-ns and sql-dmo items to a friend who is devleoping
>a sql server front end, he *really* likes it !
>Tom
|||If anybody else is having this problem, I solved it by installing sql
server 2000 service pack 3, which updated the client tool (enterprise
manager). It still freezes up the EM ui but only for about a minute or
so.

Enterprise Manager Slow

We have a SQL Server 2000 loaded on a server with 2 Xeon CPU & 2 GB of RAM.
SQL server is configure to use all the CPU & 1.5 GB of RAM. This SQL server
is acting as a repositary database for all the locational data. No of
databases on the server is 95 including master, model, msdb will be 120+
within next few months. Average database size is 2GB per database, where as
one database is of 40GB+.
My problem is whenever I try to open the database list on Enterprise manager
either in the server or in my pc it takes 10 to 15 minutes to open. where as
in query analyzer there is no issue.
Can anybody help me how i can improve the performance of Enterprise manager.
Thanks.
Check whether you have turned on the "Autoclose" option in your databases.
(http://www.microsoft.com/sql/techinf.../autoclose.asp).
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"satyaki" <satyaki@.discussions.microsoft.com> wrote in message
news:6840A2F2-AD5E-4B3A-9B80-57FB5917381C@.microsoft.com...
> We have a SQL Server 2000 loaded on a server with 2 Xeon CPU & 2 GB of
RAM.
> SQL server is configure to use all the CPU & 1.5 GB of RAM. This SQL
server
> is acting as a repositary database for all the locational data. No of
> databases on the server is 95 including master, model, msdb will be 120+
> within next few months. Average database size is 2GB per database, where
as
> one database is of 40GB+.
> My problem is whenever I try to open the database list on Enterprise
manager
> either in the server or in my pc it takes 10 to 15 minutes to open. where
as
> in query analyzer there is no issue.
> Can anybody help me how i can improve the performance of Enterprise
manager.
> Thanks.
|||Thanks a lot. it works.
"Dejan Sarka" wrote:

> Check whether you have turned on the "Autoclose" option in your databases.
> (http://www.microsoft.com/sql/techinf.../autoclose.asp).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "satyaki" <satyaki@.discussions.microsoft.com> wrote in message
> news:6840A2F2-AD5E-4B3A-9B80-57FB5917381C@.microsoft.com...
> RAM.
> server
> as
> manager
> as
> manager.
>
>

Enterprise Manager Slow

We have a SQL Server 2000 loaded on a server with 2 Xeon CPU & 2 GB of RAM.
SQL server is configure to use all the CPU & 1.5 GB of RAM. This SQL server
is acting as a repositary database for all the locational data. No of
databases on the server is 95 including master, model, msdb will be 120+
within next few months. Average database size is 2GB per database, where as
one database is of 40GB+.
My problem is whenever I try to open the database list on Enterprise manager
either in the server or in my pc it takes 10 to 15 minutes to open. where as
in query analyzer there is no issue.
Can anybody help me how i can improve the performance of Enterprise manager.
Thanks.Check whether you have turned on the "Autoclose" option in your databases.
(http://www.microsoft.com/sql/techin...n/autoclose.asp).
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"satyaki" <satyaki@.discussions.microsoft.com> wrote in message
news:6840A2F2-AD5E-4B3A-9B80-57FB5917381C@.microsoft.com...
> We have a SQL Server 2000 loaded on a server with 2 Xeon CPU & 2 GB of
RAM.
> SQL server is configure to use all the CPU & 1.5 GB of RAM. This SQL
server
> is acting as a repositary database for all the locational data. No of
> databases on the server is 95 including master, model, msdb will be 120+
> within next few months. Average database size is 2GB per database, where
as
> one database is of 40GB+.
> My problem is whenever I try to open the database list on Enterprise
manager
> either in the server or in my pc it takes 10 to 15 minutes to open. where
as
> in query analyzer there is no issue.
> Can anybody help me how i can improve the performance of Enterprise
manager.
> Thanks.|||Thanks a lot. it works.
"Dejan Sarka" wrote:

> Check whether you have turned on the "Autoclose" option in your databases.
> (http://www.microsoft.com/sql/techin...n/autoclose.asp).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "satyaki" <satyaki@.discussions.microsoft.com> wrote in message
> news:6840A2F2-AD5E-4B3A-9B80-57FB5917381C@.microsoft.com...
> RAM.
> server
> as
> manager
> as
> manager.
>
>

Enterprise Manager Slow

We have a SQL Server 2000 loaded on a server with 2 Xeon CPU & 2 GB of RAM.
SQL server is configure to use all the CPU & 1.5 GB of RAM. This SQL server
is acting as a repositary database for all the locational data. No of
databases on the server is 95 including master, model, msdb will be 120+
within next few months. Average database size is 2GB per database, where as
one database is of 40GB+.
My problem is whenever I try to open the database list on Enterprise manager
either in the server or in my pc it takes 10 to 15 minutes to open. where as
in query analyzer there is no issue.
Can anybody help me how i can improve the performance of Enterprise manager.
Thanks.Check whether you have turned on the "Autoclose" option in your databases.
(http://www.microsoft.com/sql/techinfo/tips/administration/autoclose.asp).
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"satyaki" <satyaki@.discussions.microsoft.com> wrote in message
news:6840A2F2-AD5E-4B3A-9B80-57FB5917381C@.microsoft.com...
> We have a SQL Server 2000 loaded on a server with 2 Xeon CPU & 2 GB of
RAM.
> SQL server is configure to use all the CPU & 1.5 GB of RAM. This SQL
server
> is acting as a repositary database for all the locational data. No of
> databases on the server is 95 including master, model, msdb will be 120+
> within next few months. Average database size is 2GB per database, where
as
> one database is of 40GB+.
> My problem is whenever I try to open the database list on Enterprise
manager
> either in the server or in my pc it takes 10 to 15 minutes to open. where
as
> in query analyzer there is no issue.
> Can anybody help me how i can improve the performance of Enterprise
manager.
> Thanks.|||Thanks a lot. it works.
"Dejan Sarka" wrote:
> Check whether you have turned on the "Autoclose" option in your databases.
> (http://www.microsoft.com/sql/techinfo/tips/administration/autoclose.asp).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "satyaki" <satyaki@.discussions.microsoft.com> wrote in message
> news:6840A2F2-AD5E-4B3A-9B80-57FB5917381C@.microsoft.com...
> > We have a SQL Server 2000 loaded on a server with 2 Xeon CPU & 2 GB of
> RAM.
> > SQL server is configure to use all the CPU & 1.5 GB of RAM. This SQL
> server
> > is acting as a repositary database for all the locational data. No of
> > databases on the server is 95 including master, model, msdb will be 120+
> > within next few months. Average database size is 2GB per database, where
> as
> > one database is of 40GB+.
> >
> > My problem is whenever I try to open the database list on Enterprise
> manager
> > either in the server or in my pc it takes 10 to 15 minutes to open. where
> as
> > in query analyzer there is no issue.
> > Can anybody help me how i can improve the performance of Enterprise
> manager.
> >
> > Thanks.
>
>

Enterprise Manager runs slow on some computers

I have a network of WinXP SP1 machines who all connect to
SQL Server 2000 SP3 on a Win2k server. When running
enterprise manager, most of the computers take at least 4
minutes to expand the database tree. Working on databases
(retrieving properties etc) is also painfully slow. On
two computers, however, Enterprise Manager shows no
slowdown and work as I would expect. Can anyone assist me
in diagnosing the problem?I would guess that one of the snap ins on the slower machines might be
causing a problem, I had something similar happen to me only for me the
enterprise manager wouldn't open at all.
On one of the slower machines try opening the Microsoft Management Console
program (C:\windows\system32\mmc.exe) and click on file and add/remove snap
ins. CLick the Add button and then select the Microsoft SQL Enterprise
manager. Click Ok and then try to open the database tree to see if its any
quicker. If so you can then click save as and use this console file instead
of the enterprise manager. If not then I'm afraid i can;t help you.
Nick
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
> I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?|||Check to ensure that the AutoClose properties of the databases are not set
in EM on the slow machines.
Andrew J. Kelly SQL MVP
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
> I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?|||Check for a file called SQL.LOG on the slow machines. If it's there and is
growing, you've got ODBC Tracing turned on.
Otherwise, run a network trace and compare the protocols being used from
the slow vs. fast.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Also, in EM, you can click on File|Options... and choose to delete the
history of changes to the console files. This can speed up performance as
well.
Jacco Schalkwijk
SQL Server MVP
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
>I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?|||Yes you fixed my prob-, SQL TRACING WAS TURNED ON.
I have a very fast computer and it still made E-P-M very slow alymost
unuseable... Found c:\sql.log file 2.5 GB's in size ha ha...
Turned Tracing off works very fast now. cool!
Hummm, Now to find out how it got turned on in the first place ? Who
did it did you ? why you little #&*&#?! get over here!
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Enterprise Manager runs slow on some computers

I have a network of WinXP SP1 machines who all connect to
SQL Server 2000 SP3 on a Win2k server. When running
enterprise manager, most of the computers take at least 4
minutes to expand the database tree. Working on databases
(retrieving properties etc) is also painfully slow. On
two computers, however, Enterprise Manager shows no
slowdown and work as I would expect. Can anyone assist me
in diagnosing the problem?
I would guess that one of the snap ins on the slower machines might be
causing a problem, I had something similar happen to me only for me the
enterprise manager wouldn't open at all.
On one of the slower machines try opening the Microsoft Management Console
program (C:\windows\system32\mmc.exe) and click on file and add/remove snap
ins. CLick the Add button and then select the Microsoft SQL Enterprise
manager. Click Ok and then try to open the database tree to see if its any
quicker. If so you can then click save as and use this console file instead
of the enterprise manager. If not then I'm afraid i can;t help you.
Nick
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
> I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?
|||Check to ensure that the AutoClose properties of the databases are not set
in EM on the slow machines.
Andrew J. Kelly SQL MVP
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
> I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?
|||Check for a file called SQL.LOG on the slow machines. If it's there and is
growing, you've got ODBC Tracing turned on.
Otherwise, run a network trace and compare the protocols being used from
the slow vs. fast.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Also, in EM, you can click on File|Options... and choose to delete the
history of changes to the console files. This can speed up performance as
well.
Jacco Schalkwijk
SQL Server MVP
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
>I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?
|||Yes you fixed my prob-, SQL TRACING WAS TURNED ON.
I have a very fast computer and it still made E-P-M very slow alymost
unuseable... Found c:\sql.log file 2.5 GB's in size ha ha...
Turned Tracing off works very fast now. cool!
Hummm, Now to find out how it got turned on in the first place ? Who
did it did you ? why you little #&*&#?! get over here!
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Enterprise manager is slow!

I have a number of machines connecting to SQL server.
When using Enterprise Manager, half of them take around 5
minutes to expand the database tree, while the other half
are fine. The only difference in the computer I can find
is that the slow ones are running Delphi. Is there a
known issue with EM and Delphi? If not, can anyone
suggest things I can try to sort out this problem?
"Graeme" <anonymous@.discussions.microsoft.com> wrote in message
news:1ba201c49a3e$27b13030$a501280a@.phx.gbl...
> I have a number of machines connecting to SQL server.
> When using Enterprise Manager, half of them take around 5
> minutes to expand the database tree, while the other half
> are fine. The only difference in the computer I can find
> is that the slow ones are running Delphi. Is there a
> known issue with EM and Delphi? If not, can anyone
> suggest things I can try to sort out this problem?
As a troubleshooting step, if you de-install Delphi on one of the problem
clients, then EM loads faster?
Steve

Enterprise Manager appears to slow my dial up connection

I am using a dial up connection to the Internet and to an online SQL
Server database.

I have a problem in that when I use Enterprise Manager to access even
my local server , or even, it appears, when it is just open with no
connection to any SQL Server, I experience very slow data transfer
over my normal dial up connection.

I am reasonably sure it is Enterprise Manager causing the problem as
when I close it and redial I have an acceptable rate of data transfer
using my dial up connection.

Can anyone:

a. support my conclusion that Enterprise Manager is the probable
cause of the slow connection.

b. suggest a solution

Best wishes, John MorganJohn Morgan (jfm@.XXwoodlander.co.uk) writes:
> I am using a dial up connection to the Internet and to an online SQL
> Server database.
> I have a problem in that when I use Enterprise Manager to access even
> my local server , or even, it appears, when it is just open with no
> connection to any SQL Server, I experience very slow data transfer
> over my normal dial up connection.
> I am reasonably sure it is Enterprise Manager causing the problem as
> when I close it and redial I have an acceptable rate of data transfer
> using my dial up connection.
> Can anyone:
> a. support my conclusion that Enterprise Manager is the probable
> cause of the slow connection.
> b. suggest a solution

I don't have a configuration that I easily can test from, but I have a
guess that you could try: for all your remote servers, right-click and
pick Edit SQL Server Registration Properties and uncheck "Display server
state in console".

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Enterprise Manager - slow updates when editing tables

Hi,

I have a table with about 70k rows. When I use open table --> return
all rows and then edit a field Enterprise Manager then says it has to
return the entire result set for the update to take place.

This can take a long time and makes editing data a slow process. Is
there any way to get Enterprise Manager just to update the one row??

Thanks Tomtom.richards@.rocketmail.com (Thomas Richards) wrote in message news:<f118866.0408270102.5e0340e4@.posting.google.com>...
> Hi,
> I have a table with about 70k rows. When I use open table --> return
> all rows and then edit a field Enterprise Manager then says it has to
> return the entire result set for the update to take place.
> This can take a long time and makes editing data a slow process. Is
> there any way to get Enterprise Manager just to update the one row??
> Thanks Tom

The best solution is simply not to use Enterprise Manager - it's a
useful tool, but it's more useful as an administration tool than for
programming or data manipulation. In Query Analyzer, just use a simple
UPDATE:

update dbo.MyTable
set MyColumn = 'foo'
where PrimaryKey = 1

It's probably a good idea to use QA for this kind of thing, as EM has
a number of limitations, and QA allows you to control what's happening
more precisely.

Simon