Disk Trashing Server

Question

Post reply

Disk Trashing Server

Gary Andrade

Ten Centuries

Points: 1028
More actions
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.

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

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

Johan Bijnens SSC Guru Points: 135364 More actions · Answer 1

aparently an I/O bottleneck

- What's your drive(s) config ?

Maybe this helps a bit : http://www.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

gbn SSCertifiable Points: 6429 More actions · Answer 2

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 3

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.

Vytas Newbie Points: 5 More actions · Answer 4

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.

Gary Andrade Ten Centuries Points: 1028 More actions · Answer 5

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.