Server Performance - Improvements needed

[joebickley]

Hi I have a small server running sql server 2000 with NT4. The server also runs IIS4. The server is used as a web application server and as a backend data server for some end user apps. I am experianceing some really slow periods on the server and was wondering if anyone has any tips.

The spec is PIII 800 (single) with 256RAM. I have been using the performance monitor and it seems that the machine is have spuradic patches of high page file usage. If i shove another load of RAM in i think it will solve that but im not to sure. Also will this generally help the speed.

Thanks

Joe Bickley

 

[RickCole]

Performance on SQL Server is quite a big issue but if you have a browse on the following link it might point you in the right direction.

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

As a general rule SQL Server loves RAM and the more data it can hold in there the better the performance. If you have some large tables say over 250mb which are accessed frequently then RAM (especially at today’s prices) is a good starting point.

Another area to look at which cost's nothing (apart from time) is getting the correct indexes on your tables and any long running queries you have are fully optimised. The index tuning wizard could help you here.

Rick.

 

[Dilyias]

By slow periods do you mean in sql?

Go to performance moniter and moniter these things:

Buffer Cache Hit Ratio (SQLServer:Buffer Manager)
Normally this should stay around 99%. If it is dropping or low then you should consider getting more ram.

Also check Batch Requests/sec (SQLServer:Sql Statistics) and see if it coinsides with the page usage or cpu spikes (it usually does with the cpu) , etc..

Use sql profiler wizard to look for long queries. Your server also may be autoshrinking or computing statistics a lot, depending on how active it is with modifications.. How much memory is usually free?

You can find more info about this at the site Rick mentioned above (one of my fav sites)

Or do you mean slow regarding the IIS website? It would be good to move IIS to another server if possible.