Slow Response in SQL Enterprise Manager

To all SQL expertise,

I'm a novice in SQL Server. Today, my technical manger ask me to solve a problem:

We are using single Intel Xeon 2.4GHz CPU and 1GB RAM for our SQL Server. The SQL Server is serve for ASP database only and run for one and half years already. (of cause we will restart our SQL Server in some intervals) My technical manger said, originally, the response of SQL Enterprise Manager was quite fast. However, these few months come with a very slow response for any click inside the Enterprise Manager. e.g. right click on one database for change some parameters, and we have to wait 2 or more minutes for the popup menu. I try to backup all the database and reinstall the SQL Server, but the performance keep slow.

The "Physical Memory (K)" in "Performance" tab of "Windows Task Manager" shown:
Total: 1,048,084
Available: 128,420
System Cache: 131,024

The "Kernel Memory (K)" in "Performace" tab of "Windows Task Manager" shown:
Total: 63,800
Paged: 43,772
Nonpaged: 20,028

The SQL processes in "Processes" tab of "Windows Task Manger" shown:
sqlserver.exe: PID1908, CPU02, CPU Time3:11:07, Mem Usage720,300 ("Dynamically configure SQL Server Memory" in "Memory" tab of "SQL Server Properties (Configure) - (Local)" had be set as Min=0MB & Max=718MB, means that all the assigned memory had be used by sqlserver.exe already.)

Is there any cause to our SQL Server results as slow response? What can I try to solve this issue?

Thanks for all of the response.

Best rgds
Novice

My 2 cents, from a beginner DBA.

From first glance, I would recommend adding at least 1GB more of memory. I have a dual 2.8 Ghz and 2.5 GB of memory for a 15 - 20 GB DB and I could use more memory.

How big is your DB?

128K available memory just seems very low, and it also seems like you manually set SQL to max at 718K. Increasing the memory and letting SQL use as much as it needs up to the 1.5 GB limit might be a good starting point, let's see what others think as well.

Rodney

Hi ya,

The figures you've given don't really say anything... SQL Server will acquire as much memory as it can when it needs it up to either the limit set or until the OS has about 10 or so MB available (any less and the OS will start to page stuff out to disk, which SQL will try to avoid by releasing memory)

Are you able to collect some performance statistics using performance monitor?

some useful counters might be:
processor % utilised
memory pages/sec
network interface bytes in and bytes out
physical disk (each one) %disktime
logical disk (each one) %disktime

also run SQL Profiler looking for RPC: Complete and TSQL Batch Complete with a duration of > 100ms

See if this shows up anything

I'm presuming the EM is slow even on the server itself? (i.e. it is not the workstation that has an issue?)

Cheers
Twan

quote:My technical manger said, originally, the response of SQL Enterprise Manager was quite fast. However, these few months come with a very slow response for any click inside the Enterprise Manager. e.g. right click on one database for change some parameters, and we have to wait 2 or more minutes for the popup menu.

Is EM the only client that was slowed down recently? Have users of the application experienced slow response recently? Are you talking about instance of EM runing on db server or client workstation?

Thank you, all you four gentlemen,

Rodney,
Our DB is almost 730MB, we have no budget to increase the memory this year.

Twan,
Sorry to make you mash. I'm not familier to SQL, just due with this system for few days. Hope you understand.
The "CPU Usage" in "Performance" tab of "Windows Task Manager" shown: 3-6%
In fact, a staff reported to our technical manager that every time when she access the SQL Server this month, her computer get a very slow response. Thus, our technical manager try to look at the DB size within EM. At that moment, he found the EM response very slow and ask me to solve it.
I have no idea about his term "slow", because I never due with this SQL Server before. I just got this issue from our technical manager and he told me it was quite fast originally.

HTH,
The SQL Server was setup by the hardware provider, and no one optimize it since her first day of born. I'm going to learn it as much as I can.

mmarovic,
Both EM and user application have experienced slow response. We run the EM on SQL Server.

Best rgds
Novice

quote:Both EM and user application have experienced slow response. We run the EM on SQL Server.

Is this server deducated to run MSSQL Server or you have something else running there?
Do you use remote access to run EM on that server?

Right click (in EM) on icon reperesenting your msssql server, choose properties, then "Processor" tab. Is "Boost SQL Server priority on Windows" checked?

I recommend installing SQL Server tools on a client PC and checking the performance of EM from there. There can be a big difference.

Tom Pullen
DBA, Oxfam GB

Novice,

In addition, are you directly on the server "head" when you are accessing? I use PC Anywhere and Terminal Services to tunnel in from my PC. I don't see any performance degredation from TS like I have read. On one of my older servers, the PC Anywhere connection is slow, every click takes a bunch of seconds to respond, but nothing like what you are facing. When it gets real bad, I use TS and it is usually much better.

How and where are you connecting to the server?

Rodney

Make sure that your client machines have the latest SQL Service pack, even if they only have the client tools installed. There is a known bug you may be experiencing.

http://support.microsoft.com/defaul...port/kb/articles/Q282/4/16.ASP&NoWebContent=1

Mindy Curnutt
Unisys Corporation

Note: you can tell if the client machine has the update dll by looking for the version of this file on the client machine:

Date Time Version Size File name Platform
--------------------------------------------------------------
12-Dec-2000 12:13 8.00.233 4,194,368 Sqldmo.dll x86

Dear all,

mmarovic
Thanks a lot your steps to check for "Boost SQL Server priority on Windows"! I think you shoot down part of our problem, it'd "NOT" been checked. When we checked this option and reboot the SQL Server, there is no complaint from the user again. However, the slow response of EM seem to be still there.
Can you tell me what is the major function of this option? What is the different between "checked" and "unchecked"?
By the way, this is a Server dedicated to run SQL only. Normally, I use Terminal Services Client to access that Server for running EM.

Satya
Thanks for your suggestion, I will look at the articles one by one.

Tom
Would you mind to tell me where can I find that "SQL Server tools"? And what is the official name of that tool?

Rodney
I tried to use EM both directly on Server and Remotely access on my notebook with Terminal Services Client, the responses are the same - slow.
When I use TS remote access to the SQL Server, my notebook is direct connected (Notebook-RJ45-PatchPanel-Hub-RJ45-Server) to the same internal network with that SQL Server. Within this network we have only two Servers, SQL and DHCP (also storing the user doc), plus 20 client PCs and network printers.

Mindy
Sorry to ask this question, how can I check our client PCs have the latest SQL SP? Only check with this "sqldmo.dll" file? Is it means when this file existing on the client PC, this PC has a latest SP?

Thanks again for all of your helps.

Best rgds
Novice

Look up the sqldmo.dll on the client box, right click it and look to see the version is 8.00.233.

It may be a memory issue also. To determine this, go into Administrtive Tools, Performance and add the SQL Buffer Manager, Page Life Expectancy counter.

Watch to see if the Page Life Expectancy is anything other than flat. If it drops off and then comes back up, SQL does not have enough memory. If Page Life Expectancy is anything lower than 500 (3 minutes) you may also have a memory issue and SQL needs more memory. If that counter is flat (and when I mean flat, I mean the min, avg and max are all close together) then you don't have memory pressure.

I usually change the scale on that counter to .01, the default scale is not useful.

Mindy

Hi ya,

if the cpu usage on the server is 3-6% then the option "Boost SQL Server priority on Windows" should not have any significant impact. This option is only useful when there are lots of competing processes and you want SQL to get first dibs at the cpu.

In my opinion, this option should NOT be turned on on production systems. It can cause SQL to starve the OS itself from CPU time making the box totally unresponsive and in some cases unable to be managed by sys admins...

If you turn the option back off I'm guessing that the server still performs ok...? You need to run performance monitor on the server to collect some data, and possibly also SQLProfiler to look for any long running sql.

Cheers
Twan

quote:Originally posted by Novice


Tom
Would you mind to tell me where can I find that "SQL Server tools"? And what is the official name of that tool?

SQL Server Client Tools means Enterprise Manager, Query Analyzer, Profiler, and so on. When you install SQL Server you are given 3 options - SQL Server and Client Tools, Connectivity Only, or Client Tools only. Install client tools only on your laptop or whatever then you can connect to your server in Enterprise Manager and administer the server direct from there.

A few other things - I'd avoid "Priority Boost" for the same reasons Twan gave.

Tom Pullen
DBA, Oxfam GB

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">Thanks a lot your steps to check for "Boost SQL Server priority on Windows"! I think you shoot down part of our problem, it'd "NOT" been checked. When we checked this option and reboot the SQL Server, there is no complaint from the user again. However, the slow response of EM seem to be still there.<br />Can you tell me what is the major function of this option? What is the different between "checked" and "unchecked"?<br />By the way, this is a Server dedicated to run SQL only. Normally, I use Terminal Services Client to access that Server for running EM.<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Actually I thought that option might already be on and this is the reason for EM slowness when running on server directly. I prefer to run EM on my (client) comp instead on server directly. From BOL:<br /><br /> <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">Use the priority boost option to specify whether Microsoft® SQL Server™ should run at a higher Microsoft Windows NT® 4.0 or Windows® 2000 scheduling priority than other processes on the same computer. If you set this option to 1, SQL Server runs at a priority base of 13 in the Windows NT 4.0 or Windows 2000 scheduler. The default is 0, which is a priority base of 7.<br /><br />priority boost should be used only on a computer dedicated to SQL Server, and with a symmetric multiprocessor (SMP) configuration.<br /><br /><br /><br />Caution Boosting the priority too high may drain resources from essential operating system and network functions, resulting in problems shutting down SQL Server or using other Windows NT 4.0 or Windows 2000 tasks on the server.<br /><br /><br />In some circumstances, setting priority boost to anything other than the default can cause the following communication error to be logged in the SQL Server error log:<br /><br />Error: 17824, Severity: 10, State: 0 Unable to write to ListenOn<br />connection '&lt;servername&gt;', loginname '&lt;login ID&gt;', hostname '&lt;hostname&gt;'<br />OS Error: 64, The specified network name is no longer available.<br /><br />Error 17824 indicates that SQL Server encountered connection problems while attempting to write to a client. These communication problems may be caused by network problems, if the client has stopped responding, or if the client has been restarted. However, error 17824 does not always indicate a network problem. Check priority boost and make sure that the option is set to the default. Deviating from the default may cause error 17824.<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Your increased performance could come from boosting sql server priority but maybe just reboot did the trick.<br /><br />You don't have screen saver set on this server, do you? <img src='/community/emoticons/emotion-1.gif' alt='' /> Please, don't be offended by the question I experienced even more unbelievable mistakes from inexperienced people and I don't know what I can expect from you. Especially because you changed this setting just based on my question without checking what it actually means and/or without asking for recommendation.<br /><br />I apologize for omitting explanations why I need questions to be answered. I just wanted to avoid "what if" discussion and to give you recommendations and explanations at the moment I have enough info.

quote:
Both EM and user application have experienced slow response. We run the EM on SQL Server.

I did not understand or read this part at first. So it's not that EM is slow, it's that the entire SQL Server is slow no matter how you access it?

You may want to check to see if you have maintenance plans. You said that the SQL Server had been installed by a vendor and nothing has been done since that day...

Are there reindexing jobs? Are you updating statistics? Has your database grown over time (a significant amount), if so, these two things could be crucial to the way your system performs.

If you have the maintenance window this weekend, I would reindex all tables and update all statistics. Update them with a FULL SCAN if you can get away with it resource wise. That way you can eliminate these two possibilities.

Mindy

Hi,

Look up this posing from another SQL Server message Board:

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-tools/1750/Enterprise-manager-performance

It seems like the exact same problem. See if you can do what the MSFT rep suggested and seemingly did the trick and let us know if this works.

I think it has to do with ODBC tracing needing to be turned off.

Thanks.

quote:
Michael Bourgon's noted here:

http://www.developersdex.com/sql/message.asp?p=2290&r=4703342

Here's something I found a few months ago, and it seems to come in
handy pretty often, so I figured I'd share.

Disclaimer: you are modifying a SYSTEM TABLE. This may not work in
the next version of SQL Server.

When you right click on a Database in the Enterprise Manager and
choose Properties, in order to show the last backup time, it has to go
through MSDB and find the last backup date for the database. Unless
you're carefully pruning that (via sp_delete_backuphistory), it can
get slow. Eventually, getting database properties can literally take
minutes, which locks up the Enterprise Manager for that period of
time.

After watching via the profiler, I added two indexes in MSDB:

in backupset: 1 index, on "media_set_id" and "backup_finish_date"
in restorefile: 1 index, on "restore_history_id"
(both are nonclustered, though you probably could cluster the
restorefile index)

Now, getting properties takes about 4 seconds.
Hope this helps.

--------------------------------

I would add it's probably NOT a good idea to create a CLUSTERED index on a system table. Be happy with the regular index improvement and don't risk breaking something by clustering it.