Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We have a SQL Server 2005 Standart server with two databases which are used by tons of applications. The server seems to slow down at certain times of day due to high traffic. We're certainly going to scale up but we also have a few Compaq Proliant servers lying around doing nothing and I thought maybe we should scale out instead. At first I thought of dividing the two databases into separate servers but they each have tables that the other depends on so we fear that it could be slow when we separate them. Could you please give me any suggestions about scaling out for having a faster running database system?

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

It is not easy to give advice based on such a tiny description, and usually scaling is to be thought through early, from design time.

However, you may want to look in some specific areas - that may or may not fit your systems topology and design. Sorry if some items are pretty obvious, but, again, it is hard to provide targeted and relevant advices from just a small description.

  • Indexes: developers sometimes create queries that DB admins are not aware of, and indexes are missing. Usually the cost is exponential, being sometimes barely noticeable for several months (depending on how your DBs grow).

  • DB server configuration tuning. A lot is to be found on the net, and extending memory for indexes, for this or that may sometimes change your server life. Adding memory? Is your server swapping?

  • Where is the bottleneck? Is it really the DB server itself, not the bandwidth? Don't you have other processes on the same server that eat resources?

Scaling

Regarding the scaling, it depends actually how your two databases are linked, and how much cost it would take for the developers to use the tables from two different servers. If, fortunately, you can give it a try, ensure the two DB servers can communicate efficiently, via a 1Gb link for instance.

share|improve this answer
add comment

Could you please give me any suggestions about scaling out for having a faster running database system?

No. Not possible. Simple like that. In general. Basically buy faster hardware or optimize your code.

share|improve this answer
    
"...or optimize your code...": SELECTs should have explicit column lists and valid SARGS (only get the data you need). SARG columns indexed (left most column most selective), index statistics kept up to date, use stored procedures where possible, parameterize frequently used ad-hoc queries, etc. –  jl. Dec 9 '10 at 14:36
    
Without analysis of where potential bottlenecks are, you can't even really say what to optimize. CPU? Memory? Network? Disk I/O? (the overlooked performance killer IMO.) Analysis needs to be done before you can even recommend what code to look at or what hardware to buy. –  MattB Dec 9 '10 at 15:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.