Post reply

Disk Trashing Server

  • February 16, 2006 at 5:24 pm

    #111690

    We have an Opteron box with 8 GB of RAM.  Windows 2003 Enterprise, SQL Server 2000 Enterprise SP4.   The problem is disk activity is through the roof and CPU hovering at %5 usage (almost nothing).  If I stop the SQL Server service it releases almost 2 GBs of memory.  If I start it back up it quickly rises (10 min) back up and SQL begins to crawl.  A high transactional database is the main player on this box.  Any idea what the nature of this problem might be and how to get started with the troubleshooting.  Any ideas would be helpful. 

  • February 17, 2006 at 12:27 am

    #621559

    aparently an I/O bottleneck

    - What's your drive(s) config ?

    Maybe this helps a bit : http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=214046

    (check the mentioned articles ! )

     

    - http://www.sql-server-performance.com/performance_monitoring_tutor_part4.asp

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 17, 2006 at 6:49 am

    #621633

    Have you enabled AWE in MSSQL without setting max memory etc?

    You could be generating a lot of swapping between RAM and page file...

  • February 17, 2006 at 6:51 am

    #621635

    The memory utilization is not all that big of a concern unless you have a lot of swapping out of memory (this means a lot of activity on the Hard Drive where the swap file exists.)  Using Task Manager pick View->Select Columns... and turn on Page Faults. Restart service then see if you also have an excessive amount of page faults during the first few minutes and when a large resultset is produced from your average query. If so then you may need to add additional memory.

    There are also some items you can look at in System Monitor such as disk queue lenght to help determine if it is slow responding drive subsystem.

    But my suggestion is before you assume hardware get a sampling of the queries run over several hours (use SQL Profiler) and verify that index structures don't need tweaking. Application design is always the best palce to start looking for poor performance issues.

  • February 17, 2006 at 8:39 am

    #621694

    Look at SQL Server properties, memory tab.

    Default settings are - "Dynamically ..." and all physical memory of server.

    If awe is not enabled, sql server starts using > 2gb and high swapping occures...

    I see 2 otpions:

    1. Set sql server memory to 2 GB. Then it will not swap.

    2. Assign 2 GB for sql server, or 3 GB with windows awe and sql server awe anabled.

    If problems remain, I would try to find tables w/o PK,

    queries that are not using indexes.

  • February 17, 2006 at 1:33 pm

    #621799

    Thanks for all suggestions.  Turns out it was a hardware related issue.  The HBA was having problems connecting to the SAN.  We tried to reboot the server and it would not come up until we disconnected from the SAN.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply