Enterprise manager performance

Hello,
When I use EM to examine a local db in SQLServer, it takes 1 - 3 minutes to
display the stored procedures (for example).
I just clicked on the "Tables" node in the treeview, and it took over a
minute to list the tables.
Is there any way to improve the performance? As it stands, it borders on
the unusable...
Thanks for any suggestions.
Check the properties of your database. Is autoclose turned on?
By the way, you can find out lots of information via Query Analyzer. It can
do almost everything that Enterprise Manager does -- often it can do it
faster.
you can execute stored procedures to find out information
exec sp_tables
exec sp_stored_procedures
or you can execute queries
SELECT * FROM INFORMATION_SCHEMA.TABLES /*WHERE ...*/
SELECT * FROM INFORMATION_SCHEMA.COLUMNS /*WHERE ...*/
use northwind
go
exec sp_tables
go
exec sp_columns 'Orders'
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Orders'
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Orders'
Keith
"Developer" <wanderer@.mapinfo.nope.com> wrote in message
news:uXmNY7bwEHA.3716@.TK2MSFTNGP15.phx.gbl...
> Hello,
> When I use EM to examine a local db in SQLServer, it takes 1 - 3 minutes
to
> display the stored procedures (for example).
> I just clicked on the "Tables" node in the treeview, and it took over a
> minute to list the tables.
> Is there any way to improve the performance? As it stands, it borders on
> the unusable...
> Thanks for any suggestions.
>
|||Thanks for the reply. I'll try Query Analyzer.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uwwAbcewEHA.1988@.TK2MSFTNGP12.phx.gbl...
> Check the properties of your database. Is autoclose turned on?
> By the way, you can find out lots of information via Query Analyzer. It
can[vbcol=seagreen]
> do almost everything that Enterprise Manager does -- often it can do it
> faster.
> you can execute stored procedures to find out information
> exec sp_tables
> exec sp_stored_procedures
> or you can execute queries
> SELECT * FROM INFORMATION_SCHEMA.TABLES /*WHERE ...*/
> SELECT * FROM INFORMATION_SCHEMA.COLUMNS /*WHERE ...*/
>
> use northwind
> go
> exec sp_tables
> go
> exec sp_columns 'Orders'
> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Orders'
> SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Orders'
> --
> Keith
>
> "Developer" <wanderer@.mapinfo.nope.com> wrote in message
> news:uXmNY7bwEHA.3716@.TK2MSFTNGP15.phx.gbl...
> to
on
>
|||Hi,
Have you found any valueable information in Query Analyzer? If you have
many tables, it might really be time-consuming.
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||QA will be sufficient.
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:XQ5gLYwwEHA.2916@.cpmsftngxa10.phx.gbl...
> Hi,
> Have you found any valueable information in Query Analyzer? If you have
> many tables, it might really be time-consuming.
> I wanted to post a quick note to see if you would like additional
> assistance or information regarding this particular issue. We appreciate
> your patience and look forward to hearing from you!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||This is ridiculous -- I just waited 4 minutes for EM to open the property
sheet for a local database.
P4, 2.8 gig, 1 gig o' ram and a big hard disk.
There must be some sort of tuning of SQLServer (or EM) available to make it
respond like a modern application.
"Developer" <wanderer@.mapinfo.nope.com> wrote in message
news:uXmNY7bwEHA.3716@.TK2MSFTNGP15.phx.gbl...
> Hello,
> When I use EM to examine a local db in SQLServer, it takes 1 - 3 minutes
to
> display the stored procedures (for example).
> I just clicked on the "Tables" node in the treeview, and it took over a
> minute to list the tables.
> Is there any way to improve the performance? As it stands, it borders on
> the unusable...
> Thanks for any suggestions.
>
|||Hi,
I understood that it must be frustrating!
Would you please use Profiler to trace the process when SQL Server
Enterprise Manager was opening? Send the Profiler Logs directly to me
v-mingqc@.online.microsoft.com (please remove 'online' as it's only for
SPAM). I would like to make further investigate.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi,
Thanks for sending me the Profiler Trace!
1. I find the statement exec sp_dboption N'CareInsite', N'autoclose',
N'true' in the trace, so that please make sure AUTO_CLOSE option for the
database was set to false(Not Selected)
2. Check if ODBC Trace is turned off, using ODBCAD32.EXE and see Tracing tab
BTW, you are using SQL Server 2000 SP3?
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||1. I have unchecked "Auto close" on the Options tab in the property sheet
for my db.
2. It appears ODBC tracing was on; I clicked the "Stop Tracing Now" button
on the Tracing tab of "ODBC Data Source Administrator".
"About SQL Server Service Manager" says it is version 8.00.760. I believe
it is SQL Server 2000 SP3.
Thanks for following up...
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:ta%23zQ%23IyEHA.3388@.cpmsftngxa10.phx.gbl...
> Hi,
> Thanks for sending me the Profiler Trace!
> 1. I find the statement exec sp_dboption N'CareInsite', N'autoclose',
> N'true' in the trace, so that please make sure AUTO_CLOSE option for the
> database was set to false(Not Selected)
> 2. Check if ODBC Trace is turned off, using ODBCAD32.EXE and see Tracing
tab
> BTW, you are using SQL Server 2000 SP3?
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||I suspect that turning off ODBC tracing made the difference; EM and Query
Analyzer are responsive and useful.
Thanks.
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:ta%23zQ%23IyEHA.3388@.cpmsftngxa10.phx.gbl...
> Hi,
> Thanks for sending me the Profiler Trace!
> 1. I find the statement exec sp_dboption N'CareInsite', N'autoclose',
> N'true' in the trace, so that please make sure AUTO_CLOSE option for the
> database was set to false(Not Selected)
> 2. Check if ODBC Trace is turned off, using ODBCAD32.EXE and see Tracing
tab
> BTW, you are using SQL Server 2000 SP3?
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>