SQL Server Performance

Slow Response in SQL Enterprise Manager

Discussion in 'Performance Tuning for DBAs' started by Novice, Nov 9, 2004.

  1. Novice New Member



    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
  2. rweinstein New Member

    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
  3. Twan New Member

    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
  4. satya Moderator

    KBAhttp://support.microsoft.com/kb/243589/EN-US/ about monitoring the slow running queries.

    See if SQL Server is doing a lot of I/O. Tools like SQL Profiler and Blocker Script output can tell you if the query(s) are resulting in these large I/Os, and tuning or optimizing these individual queries from a statistics and indexes point of view may help from the disk I/O perspective. An improper index strategy can add to a disk subsystem workload. For example, instead of doing an Index Seek, the SQL Server Optimizer may choose to do a Table/Index Scan, resulting in a lot of unnecessary I/O.

    Try to solve the problem, and not the symptoms. With proper optimization techniques, such as adding proper indexes, redesigning badly written queries, and so on, you can avoid almost all of these issues.

    Run SYSMON( PERFMON) for correct assessment of resource usage.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. mmarovic Active Member

    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?
  6. Novice New Member

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

    I would suggest not to take full compilation from the Task Manager, PERFMON (SYSMON) helps to assess the resource usage in detail.

    Then start searching this website for all the articles that are purely based on the performance fine-tuning only.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. mmarovic Active Member

    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?
  9. thomas New Member

    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
  10. rweinstein New Member

    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
  11. mcurnutt New Member

  12. mcurnutt New Member

    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
  13. Novice New Member

    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
  14. mcurnutt New Member

    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
  15. Twan New Member

    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
  16. thomas New Member

    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
  17. mmarovic Active Member

    <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.
  18. mcurnutt New Member

    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
  19. rweinstein New Member

  20. vaxman New Member

    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.
  21. satya Moderator

    True I will agree 100% about clustered indexes on system tables, not advised.
    For the accurate information you can get results from query analyzer rather than depending upon the Enterprise Manager GUI tool.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page