Enterprise Manager Slowing to a Crawl

Enterprise Manager Slowing to a Crawl

Post by Michael R Griffi » Thu, 21 Dec 2000 05:15:29



    For several months I've been running SQL Server 7.0 under Windows 98 on
a Compaq Presario, 900MHz Celeron, 128M memory, 40GB hard drive, a
standalone home system. Nothing production or heavy duty, obviously, just
some design work and a little testing of small, simple databases. Everything
runs fine. But over the last couple of months, Enterprise Manager has gone
from crisp to sluggish to intolerable in its response times on local
databases no larger nor more complex than "pubs." In fact, I tried working
with pubs and Northwind and got exactly the same kinds of response. Some
typical times (manually measured from click on the object to the wait-icon
returning to normal):

    Open the local server node [COMPUTER (Windows 95/98)]: 13 seconds
    Open the database list (the 6 defaults plus 2 small of my own): 10
seconds
    Open the node for a database (doesn't matter which one): 9 seconds
    Open Tables node (13 app tables + 19 system tables): 37 seconds
    Open a table definition (by double-clicking): 22 seconds
    Open All Rows for a table from right-click menu: 52 seconds 1st time, 33
seconds thereafter.

    I think the latter case is particularly suggestive: it always takes over
50 seconds to Open All Rows for any of the tables the first time, then
30-something seconds for the same or any other table in that session.
Quitting and restarting gets me the one-time 50-second opener. Also, there's
now a 3-second gap between hitting the right mouse button and the drop down
menu popping into place. I suspect it's the application code itself hitting
some kind of thrash point (although there's no unusual disk or network
activity) rather than some complexity in the logical structures or the data
connections. The fact that it has gone over time through increasing degrees
of sluggishness would suggest something like a cache getting filled and/or
fragmented and not flushed, but I have no idea where to even look to follow
that line of reasoning. I assure you (not too stridently, I hope) that I've
done nothing (on purpose) to the default configuration of the system or the
individual databases, and these are simple example applications - not even
any blobs, no indexes clustered or unclustered, no constraints or conditions
beyond FKs and autonumbered primary keys. And besides, as I mentioned, pubs
and Northwind show the same behavior. And the SQL Analyzer by itself returns
a "select * from tablename" in a couple of seconds (maybe 4 rows of 2
columns) from the same table that "Open All Rows" takes 30-50 seconds to
display. So I really think it's somewhere in the application's code
management rather than in the data.

    So far, all EM functions seem to still be working, it just takes so long
that it's starting to affect my progress. Every other application I use
seems totally unaffected - internet and email tools, Visual Interdev, Visual
Basic, a little forte Java, Excel, PhotoShop and Premiere perform crisply as
always. And EM was the same not long ago. I started noticing the increasing
sluggishness while I was using the da Vinci tools for the first time to
display and design a new project - I assumed the problem was in those tools,
but I guess now they may be innocent bystanders.

    Sorry for the novella, but I wanted to put out enough information at the
start to give somebody something to work on. Of course I'll be happy to
follow up with any details or additional tests that anybody thinks would be
useful. I'm just hoping someone's seen something like this before - it must
be a systemic problem with a single point of origin to be dragging down
performance across the board within the application. The obvious next step
is to reinstall SQL Server, but I hate to do that without having some idea
what's going on. Even if it works for now, reinstalling might destroy
evidence that would solve the problem, and leave me to repeat the process in
a few months. Hope this case rouses someone's interest, or rings a bell from
past adventures.

    Thanks for your time,

   (Remove the junk from the email if you know the answer and it's so
humiliatingly obvious that you want to explain it to me privately.)

 
 
 

Enterprise Manager Slowing to a Crawl

Post by Jay Davi » Thu, 21 Dec 2000 13:46:09


I noticed the same occurrence on my machine after I installed SQL Server 7.0
Service Pack 3 Beta.  I uninstalled/reinstalled and it works fine again
without Service Pack 3.

Jay



>     For several months I've been running SQL Server 7.0 under Windows 98
on
> a Compaq Presario, 900MHz Celeron, 128M memory, 40GB hard drive, a
> standalone home system. Nothing production or heavy duty, obviously, just
> some design work and a little testing of small, simple databases.
Everything
> runs fine. But over the last couple of months, Enterprise Manager has gone
> from crisp to sluggish to intolerable in its response times on local
> databases no larger nor more complex than "pubs." In fact, I tried working
> with pubs and Northwind and got exactly the same kinds of response. Some
> typical times (manually measured from click on the object to the wait-icon
> returning to normal):

>     Open the local server node [COMPUTER (Windows 95/98)]: 13 seconds
>     Open the database list (the 6 defaults plus 2 small of my own): 10
> seconds
>     Open the node for a database (doesn't matter which one): 9 seconds
>     Open Tables node (13 app tables + 19 system tables): 37 seconds
>     Open a table definition (by double-clicking): 22 seconds
>     Open All Rows for a table from right-click menu: 52 seconds 1st time,
33
> seconds thereafter.

>     I think the latter case is particularly suggestive: it always takes
over
> 50 seconds to Open All Rows for any of the tables the first time, then
> 30-something seconds for the same or any other table in that session.
> Quitting and restarting gets me the one-time 50-second opener. Also,
there's
> now a 3-second gap between hitting the right mouse button and the drop
down
> menu popping into place. I suspect it's the application code itself
hitting
> some kind of thrash point (although there's no unusual disk or network
> activity) rather than some complexity in the logical structures or the
data
> connections. The fact that it has gone over time through increasing
degrees
> of sluggishness would suggest something like a cache getting filled and/or
> fragmented and not flushed, but I have no idea where to even look to
follow
> that line of reasoning. I assure you (not too stridently, I hope) that
I've
> done nothing (on purpose) to the default configuration of the system or
the
> individual databases, and these are simple example applications - not even
> any blobs, no indexes clustered or unclustered, no constraints or
conditions
> beyond FKs and autonumbered primary keys. And besides, as I mentioned,
pubs
> and Northwind show the same behavior. And the SQL Analyzer by itself
returns
> a "select * from tablename" in a couple of seconds (maybe 4 rows of 2
> columns) from the same table that "Open All Rows" takes 30-50 seconds to
> display. So I really think it's somewhere in the application's code
> management rather than in the data.

>     So far, all EM functions seem to still be working, it just takes so
long
> that it's starting to affect my progress. Every other application I use
> seems totally unaffected - internet and email tools, Visual Interdev,
Visual
> Basic, a little forte Java, Excel, PhotoShop and Premiere perform crisply
as
> always. And EM was the same not long ago. I started noticing the
increasing
> sluggishness while I was using the da Vinci tools for the first time to
> display and design a new project - I assumed the problem was in those
tools,
> but I guess now they may be innocent bystanders.

>     Sorry for the novella, but I wanted to put out enough information at
the
> start to give somebody something to work on. Of course I'll be happy to
> follow up with any details or additional tests that anybody thinks would
be
> useful. I'm just hoping someone's seen something like this before - it
must
> be a systemic problem with a single point of origin to be dragging down
> performance across the board within the application. The obvious next step
> is to reinstall SQL Server, but I hate to do that without having some idea
> what's going on. Even if it works for now, reinstalling might destroy
> evidence that would solve the problem, and leave me to repeat the process
in
> a few months. Hope this case rouses someone's interest, or rings a bell
from
> past adventures.

>     Thanks for your time,

>    (Remove the junk from the email if you know the answer and it's so
> humiliatingly obvious that you want to explain it to me privately.)


 
 
 

Enterprise Manager Slowing to a Crawl

Post by Michael R Griffi » Thu, 21 Dec 2000 14:35:41



Quote:

> I noticed the same occurrence on my machine after I installed SQL Server
7.0
> Service Pack 3 Beta.  I uninstalled/reinstalled and it works fine again
> without Service Pack 3.

Jay -

    Thanks for the reinforcement. That's a hopeful sign at least. I don't
have any SP installed - I hadn't even checked on service packs for it until
this incident came up. I've got a CD en route now, so maybe some combination
of reinstalling with or without might clear it up. But not explain it. Has
anybody else seen this phenomenon come or go?

Michael

 
 
 

Enterprise Manager Slowing to a Crawl

Post by Tibor Karasz » Thu, 21 Dec 2000 16:51:17


Michael,

Two things to check:

1. That you don't have ODBC logging turned on.

2. That you don't have the autoclose or autoshrink sp_dboption turned on.

--
Tibor Karaszi, SQL Server MVP
Neil's FAQ at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.

 
 
 

Enterprise Manager Slowing to a Crawl

Post by Michael R Griffi » Thu, 21 Dec 2000 18:19:37


Tibor -

    Bingo!
    I didn't even know there was an ODBC logging option, or where it was
located. But thanks to your tip, I looked first through SQL Server, then
through the ODBC control in the Control Panel before I ran into it. Turned
the option off and immediately performance picked back up! Thanks
enormously, you've saved me a lot of time and trouble.
    Jay or Tibor, should either of you ever find yourself bound for Austin,
Texas, please contact me. I'd be delighted to stand you to all the
enchiladas and Carta Blancas you can handle - or the cuisine and beverage of
your choice. I really appreciate your help.

Sincerely,
Griff



Quote:> Michael,

> Two things to check:

> 1. That you don't have ODBC logging turned on.

> 2. That you don't have the autoclose or autoshrink sp_dboption turned on.

> --
> Tibor Karaszi, SQL Server MVP
> Neil's FAQ at: http://www.sqlserverfaq.com
> Please reply to the newsgroup only, not by email.

 
 
 

Enterprise Manager Slowing to a Crawl

Post by Tibor Karasz » Fri, 22 Dec 2000 00:46:10


Griff,

Quote:>     Jay or Tibor, should either of you ever find yourself bound for Austin,
> Texas, please contact me. I'd be delighted to stand you to all the
> enchiladas and Carta Blancas you can handle - or the cuisine and beverage of
> your choice. I really appreciate your help.

It is a long trip from Sweden, but I'll see what I can do <g>.

I'm glad it helped!

--
Tibor Karaszi, SQL Server MVP
Neil's FAQ at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.

 
 
 

1. Enterprise Manager slow

I am running msde on my computer with windows98 and 128meg.  I previously
had mssql7 but recently upgraded to 2000.  Now, when I go to Enterprise
Manager and click on Databases - then refresh, it is much slower than is was
under mssql7  Another programmer in the office was having similar  problems
and installed a new version of DBNETLIB.DLL which fixed the problem for him.
I installed the same file but am still having the slow response.  Can anyone
tell me what the problem  might be?.

Thanks,
Fay

2. compiling a db2 sqlproc

3. Enterprise Manager is slow connecting to local server

4. dbms_sql problem

5. Enterprise manager slow on local

6. database problem, help!!!!!!!!!!!!!!!!

7. Slow Enterprise Manager

8. Calling nested stored procedures

9. Enormous log files/Slow Enterprise Manager

10. Slow Database Properties Window in Enterprise Manager

11. Enterprise Manager Very Slow

12. Enterprise Manager slow

13. Slow enterprise manager