SQL Server Enterprise Manager

Discussion in 'Databases' started by Bruce, Sep 2, 2003.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. Bruce

    Bruce DiscountASP.NET Staff

    This is the short coming of the design of enterprise manager.

    We did a lot of research on internet on this topic but it seems like many people are experiencing the same problem and there's no fix for it.

    I recommend you use Query Analyser instead of EM.



    quote:Originally posted by KPayne

    I don't think that DASP can do anything about that. It is a problem (feature?) with Enterprise Manager.

    Keith Payne
    Technical Marketing Solutions
    </blockquote id="quote"></font id="quote">
     
    Bruce, Sep 2, 2003
    #1
  2. natolnet000

    natolnet000

    Is there any reason why when connecting to the sql server in Enterprise Manager, in order to get to my specific database, first I have to see a list of every other database. Unfortunatly when entering the Databases 'folder' of your sql server, enterprise manager spends a lot of time downloading many many MB's of data.
    Every other program can view lists of the database, or go straight to my database after I've put in my username/password - is there any way to get Enterprise Manager to get into my database without requiring so much data first - it's not fun having to wait about 30 minutes to get into my database!
    Thanks for all your help in the forums,</font id="Verdana">

    Pete Miller
    [email protected]
     
    natolnet000, Sep 2, 2003
    #2
  3. KPayne

    KPayne

    I don't think that DASP can do anything about that. It is a problem (feature?) with Enterprise Manager.

    Keith Payne
    Technical Marketing Solutions
     
    KPayne, Sep 2, 2003
    #3
  4. mooredynast

    mooredynast

    One could refrain from having 20,000 databases on the same server. That would help some, I expect. Back in the day when there were "only" 7,500 databases, performance wasn't too bad. Now it takes 15 minutes to open EM. That's an ISP issue, friends.

    Marc Moore
     
    mooredynast, Sep 5, 2003
    #4
  5. Bruce

    Bruce DiscountASP.NET Staff

    That's what we thought before we did the investigation.

    A summary

    1) We monitor the database CPU & disk I/O activity. We found both metric indicate minmal usage on the SQL server.

    2) It takes less than 2 seconds to open EM on the SQL server itself and from another SQL server on the same network

    3) We ran a sniffer trace from a remote site when we open the database tree. We see that there were activities over port 1433 all the times during when we try to open EM

    4) Many people have posted on the internet w/ the same problem.



    quote:Originally posted by mooredynast

    One could refrain from having 20,000 databases on the same server. That would help some, I expect. Back in the day when there were "only" 7,500 databases, performance wasn't too bad. Now it takes 15 minutes to open EM. That's an ISP issue, friends.

    Marc Moore
    </blockquote id="quote"></font id="quote">
     
    Bruce, Sep 5, 2003
    #5
  6. tourneymanagerpro

    tourneymanagerpro

    From SQL Server Magazine (online, didn't see it in print):
    Setting Autoclose to NO will reduce the amount of time it takes for clients to open Enterprise Manager and click on Databases. The reason being every time you try to access the database, the query will be slowed by the amount of load time it takes to mount the database. As for the question about your files opening and closing, yes, when you enumerate a database in Enterprise Manager, you access the database, which opens the files.

    From dbforums.com (S. Ashby):
    Dear SQL Server Experts,

    I'm running SQL Server 2000 on a development server. This "server" really is nothing more than a desktop system with 640MB of RAM, an 800mhz Pentium 3, and a slow, 40GB IDE disk drive. We increased the number of databases stored within the SQL server yesterday from 30 to 42; when we did this, we now see a dramatic increase in the amount of time it takes to open Enterprise Manager and expand the Database folder. It was taking about 2 1/2 minutes, but now, it takes upward of 8 minutes to expand. During this time, the CPU on the server is not heavily utilized (always less than 10%), but the disk drive is definitely getting hammered.... First of all, can anyone explain to me what is going on here (my guess is that there is some rights checking going on to determine what databases should be displayed in
    the pane), and secondly, is there anything I can do (a parameter
    configuration changes, etc.) to decrease this delay? Also, the response of the database in the end-user application requesting data from it is still excellent.

    I'm concerned by what I see because we are contemplating putting a production SQL server together and keeping upwards of 150+ databases on it (all relatively small - less than 25MB each). This system, of course, would be on order of a 2.2Ghz Pentium 4, would have a SCSI-based, hardware-based RAID 5 disk subsystem, and would have really fast SCSI drives on it... The transactional volume per database is relatively low (less than 1000 transactions/day, most of the time, I'd guess), so we're looking to get maximum mileage out of our SQL licensing fees. Anyone been there, done that? Is this a workable situation?

    Response
    You might try turning off the 'auto close' option on your databases so
    you don't incur the open/close overhead:

    EXEC sp_dboption 'MyDatabase', 'autoclose', False

    Posted by:
    Dan Guzman
    SQL Server MVP
     
    tourneymanagerpro, Sep 11, 2003
    #6
  7. tourneymanagerpro

    tourneymanagerpro

    Bruce,

    From the statements you made, it sounds like you are not witnessing this problem. Is that correct? Do you (or dasp) have Enterprise Manager at a remote location other than on the same network as the SQL Server machine? If so, login through the internet and try expanding the databases folder. It is agonizing!!

    I just ran the EXEC query to set autoclose to false through Query Analyzer. I can only do it for my database so it doesn't show any improvement in Enterprise Manager.

    Here is the code that generates the sql statements needed by someone at discountasp.net (I assume dasp or Bruce) to turn autoclose off:

    Select 'EXEC sp_dboption ''' + name + ''', ' + '''autoclose''' + ',' + 'false' from master.dbo.sysdatabases where filename > 'e' order by dbid

    The where filename > 'e' excludes the system tables.

    Anyone can run this select statement, but only a user with administrative rights can execute the generated sql statements.
     
    tourneymanagerpro, Sep 11, 2003
    #7
  8. Bruce

    Bruce DiscountASP.NET Staff

    Not true.

    We have tested this connecting w/ EM over the internet, it is slow.

    I'll forward your finding to the DBA and see if he already tried this.

    Thanks.


    quote:Originally posted by tourneymanagerpro

    Bruce,

    From the statements you made, it sounds like you are not witnessing this problem. Is that correct? Do you (or dasp) have Enterprise Manager at a remote location other than on the same network as the SQL Server machine? If so, login through the internet and try expanding the databases folder. It is agonizing!!

    I just ran the EXEC query to set autoclose to false through Query Analyzer. I can only do it for my database so it doesn't show any improvement in Enterprise Manager.

    Here is the code that generates the sql statements needed by someone at discountasp.net (I assume dasp or Bruce) to turn autoclose off:

    Select 'EXEC sp_dboption ''' + name + ''', ' + '''autoclose''' + ',' + 'false' from master.dbo.sysdatabases where filename > 'e' order by dbid

    The where filename > 'e' excludes the system tables.

    Anyone can run this select statement, but only a user with administrative rights can execute the generated sql statements.
    </blockquote id="quote"></font id="quote">
     
    Bruce, Sep 13, 2003
    #8
  9. Bruce

    Bruce DiscountASP.NET Staff

    Hello all.

    The EM latency issue has prompted us to create a support ticket with the Microsoft SQL enterprise support team.

    Summary of our ticket:

    1) MS Engineer said he has seen this many time.

    2) MS Engineer indicated that the latency is normal (I'd say this is a design flaw) since EM query the system database and return all the data.

    3) MS Engineer also indicated that there's no way to configure the server to not retrieve all data on every database.

    4) Turning on AutoClose will not have any effect.

    Hope this make things clear

    quote:Originally posted by tourneymanagerpro

    Bruce,

    From the statements you made, it sounds like you are not witnessing this problem. Is that correct? Do you (or dasp) have Enterprise Manager at a remote location other than on the same network as the SQL Server machine? If so, login through the internet and try expanding the databases folder. It is agonizing!!

    I just ran the EXEC query to set autoclose to false through Query Analyzer. I can only do it for my database so it doesn't show any improvement in Enterprise Manager.

    Here is the code that generates the sql statements needed by someone at discountasp.net (I assume dasp or Bruce) to turn autoclose off:

    Select 'EXEC sp_dboption ''' + name + ''', ' + '''autoclose''' + ',' + 'false' from master.dbo.sysdatabases where filename > 'e' order by dbid

    The where filename > 'e' excludes the system tables.

    Anyone can run this select statement, but only a user with administrative rights can execute the generated sql statements.
    </blockquote id="quote"></font id="quote">
     
    Bruce, Sep 15, 2003
    #9
  10. tandrew

    tandrew

    One solution to this I have seen used elsewhere, involves providing Terminal Server Logins for clients who use SQL Server.

    In this way a remote user only uses the network bandwidth required to "replicate" the screen refreshes, not the enormous amount of data that flows back and forth between Enterprise Manager (an SQL-DMO application), and the SQL Server it is connecting to.

    I actually use Terminal Server / Remote Desktop support over 33.6K modems, and although one is careful what one does, the performance is quite acceptable.

    Having said that, I recognise that providing Terminal Server logins to SQL Server users will have a lot of ramifications for discountasp (i.e. how to suitably restrict just what such users can do when logged onto their terminal services session). But it would certainly overcome the slowness issue with Enterprise Manager.[:)]

    Regards,
    Trevor Andrew
     
    tandrew, Oct 8, 2003
    #10
  11. Bruce

    Bruce DiscountASP.NET Staff

    Unfortunately, this cannot be done for security reason.

    quote:Originally posted by tandrew

    One solution to this I have seen used elsewhere, involves providing Terminal Server Logins for clients who use SQL Server.

    In this way a remote user only uses the network bandwidth required to "replicate" the screen refreshes, not the enormous amount of data that flows back and forth between Enterprise Manager (an SQL-DMO application), and the SQL Server it is connecting to.

    I actually use Terminal Server / Remote Desktop support over 33.6K modems, and although one is careful what one does, the performance is quite acceptable.

    Having said that, I recognise that providing Terminal Server logins to SQL Server users will have a lot of ramifications for discountasp (i.e. how to suitably restrict just what such users can do when logged onto their terminal services session). But it would certainly overcome the slowness issue with Enterprise Manager.[:)]

    Regards,
    Trevor Andrew
    </blockquote id="quote"></font id="quote">
     
    Bruce, Oct 8, 2003
    #11
  12. mebeza

    mebeza

    you can always setup an access data project to view your sql 2000 db, works like a charm

    kb
     
    mebeza, Oct 10, 2003
    #12
  13. capkix

    capkix

    [8D] Thanks SO much. I kinda (as in really) feel
    silly for not having thought of this before!

    Cheers,


    Nash

    quote:Originally posted by mebeza

    you can always setup an access data project to view your sql 2000 db, works like a charm

    kb
    </blockquote id="quote"></font id="quote">
     
    capkix, Oct 20, 2003
    #13
  14. vanbebar

    vanbebar

    Here is my .02 cents [:)]

    This is definitely a design flaw, within the Enterprise manager.

    This symptom, is not only witnessed in opening the node to view the databases, it is also the same if opening the 'tables' node.

    I am an SAP Technical Consultant & it is not unusual for an SAP MSSQL database to have 33,000+ tables (50,000+ indexes) & a huge mistake, is to accidentally open the 'tables' node :) (that is a massive waiting game)

    Fortunately, database size, does not seem to add to this symptom, since most SAP productive databases are at minimum 50GB & can quickly grow to 500GB...

    Sorry for the babbling, but this is a issue that is close to my heart [:D]


    Barry - [SAP Technical Consultant]
     
    vanbebar, Dec 23, 2003
    #14
  15. KPayne

    KPayne

    How do I get a job as an SAP Consultant? Do I have to speak German?

    We paid those guys $250.00/hour during a rollout at a place I used to work. That's my kind of job :)

    Keith Payne
    Technical Marketing Solutions
     
    KPayne, Dec 25, 2003
    #15
  16. vanbebar

    vanbebar

    That's funny :)

    I was fortunate. Back in 1998, the company that I worked for implemented SAP R/3 3.1. So, I became the SAP Technical Basis person for our company. After 3 years of that, my experience was enough, to land me a job actually working for SAP America, as a Technical/Basis Consultant. I had a blast at that job.

    But now my kids got to an age, where I didn't want to be away from home anymore... so, I am now the IT Director / SAP Technical guy, for a local company.

    So I still get to be a techy geek, but don't have to travel to do it [:)]

    Happy Holidays, everyone!!


    Barry - [SAP Technical Consultant]
     
    vanbebar, Dec 26, 2003
    #16
  17. TooMuch2AM

    TooMuch2AM

    It took me approximately 30 seconds of searching on google groups to find an answer to this.

    Simply install MSSQL SP3a or above on your EM machine. The database list now takes 30 seconds to open instead of 30 minutes.
     
    TooMuch2AM, Jan 21, 2004
    #17
  18. mlawrence

    mlawrence

    Execllent work TooMuch2AM! Thanks for saving me a lot of time!

    Martin
     
    mlawrence, Jan 22, 2004
    #18
  19. Bruce

    Bruce DiscountASP.NET Staff

    mlawrence,

    did you try TooMuch2AM's recommendation? did it work for you?



    quote:Originally posted by mlawrence

    Execllent work TooMuch2AM! Thanks for saving me a lot of time!

    Martin
    </blockquote id="quote"></font id="quote">
     
    Bruce, Jan 23, 2004
    #19
  20. tourneymanagerpro

    tourneymanagerpro

    Installing the Service Pack did the trick. Thanks TooMuch!
     
    tourneymanagerpro, Feb 20, 2004
    #20
  21. kayak-tica

    kayak-tica

    kayak-tica, Mar 17, 2004
    #21
  22. elian

    elian

    quote:A very cool app to help out with this issue as well!


    Very quick online access to enterprise manager type tool.

    http://sqladmin.webhost4life.com/</blockquote id="quote"></font id="quote">

    More info about myLittleAdmin (the application mentionned above) on http://www.myLittleTools.net/mla_sql

    Best regards
     
    elian, Mar 31, 2004
    #22
  23. mlawrence

    mlawrence

    You bet! Went from 30 mins down to 30 secs! I am happy once again. [8D]

    quote:Originally posted by bruce

    mlawrence,

    did you try TooMuch2AM's recommendation? did it work for you?



    quote:Originally posted by mlawrence

    Execllent work TooMuch2AM! Thanks for saving me a lot of time!

    Martin
    </blockquote id="quote"></font id="quote">
    </blockquote id="quote"></font id="quote">
     
    mlawrence, Apr 8, 2004
    #23
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page