SQL Server Performance Forum – Threads Archive
SQL Server 2005 – Building a development Server
Hi, I would like advice regarding what hardware to use for a SQL Server 2005 for use in a development environment. I am the only developer and user of the database. I develop at home, and currently use one PC, to develop the software. My PC is Athlon XP 4000, 2 Gig RAM, 1 x 160gb HD . I am developing an asp.net website along with a variety of data interface applications in C# – all on top of SQL Server 2005. This environment works/worked fine until I started dealing with a lot more data. I now have 2 databases, each about 30 gig in size (with most of the data in a a couple of tables in each database). When I am importing data or running the website or doing intensive queries or doing sql updates that change millions of rows, I am understandably hitting performance problems. I have had to throttle SQL Server 2005 to only use 400mb, as I have so many other development applications that are open…… Everything is slowing to a halt, and I dont think SQL Server is very happy. So, I am looking to build or buy a cheap machine to run SQL Server 2005, and take the load off my current PC. However I only have a budget of about £300/$560 – maybe a bit more at a push (I could possibly upgrade in the future). I could do something like below, if I am going to buy a machine Acer T180
Sempron 3200
512mb (i would upgrade to 2GB)
80gb hard disk Or I could build my own? If I do, what should I skimp on and where should I invest more. I.e. should I get the cheapest processor possible, and get a decent harddrive or 2? Should I raid 0 a couple of small drives? Or have one drive for data and one for the transaction logs? Or should I have one drive, and spend more on RAM? Any help would be greatly appreciated…. Andrew
normally, i would get a minimum of
1) a dual core cpu
2) 3-4 disk drives 1) is so that i can see parallel execution plans that would occur in production servers
2) is also to replicate the characteristics of production servers since you are more concerned with the problems that you described let me propose:
1. go ahead a limit SQL Server memory to no more than what is available in your system with all the other app open and running
its not that sql needs alot of memory in a development system
it is that it will use all that is available because that’s what data caching is about the problem occurs when other apps want more memory, causing SQL to page, which kills performance. 2. start by buying 2-3 SATA hard drives for SQL files only,
try various file placements,
standard production placements strategies don’t apply
maybe logs on one disk
30GB DB1 on a second, and 30GB DB2 on the third 3. only if the above is still not satisfactory,
buy the cheapest Core 2 Duo you can,
bring the HDs from 2 over, so you didn’t waste any money
quote:Originally posted by joechang
normally, i would get a minimum of
1) a dual core cpu
2) 3-4 disk drives 1) is so that i can see parallel execution plans that would occur in production servers
2) is also to replicate the characteristics of production servers since you are more concerned with the problems that you described let me propose:
1. go ahead a limit SQL Server memory to no more than what is available in your system with all the other app open and running
its not that sql needs alot of memory in a development system
it is that it will use all that is available because that’s what data caching is about the problem occurs when other apps want more memory, causing SQL to page, which kills performance. 2. start by buying 2-3 SATA hard drives for SQL files only,
try various file placements,
standard production placements strategies don’t apply
maybe logs on one disk
30GB DB1 on a second, and 30GB DB2 on the third 3. only if the above is still not satisfactory,
buy the cheapest Core 2 Duo you can,
bring the HDs from 2 over, so you didn’t waste any money
1) a dual core cpu
2) 3-4 disk drives 1) is so that i can see parallel execution plans that would occur in production servers
2) is also to replicate the characteristics of production servers since you are more concerned with the problems that you described let me propose:
1. go ahead a limit SQL Server memory to no more than what is available in your system with all the other app open and running
its not that sql needs alot of memory in a development system
it is that it will use all that is available because that’s what data caching is about the problem occurs when other apps want more memory, causing SQL to page, which kills performance. 2. start by buying 2-3 SATA hard drives for SQL files only,
try various file placements,
standard production placements strategies don’t apply
maybe logs on one disk
30GB DB1 on a second, and 30GB DB2 on the third 3. only if the above is still not satisfactory,
buy the cheapest Core 2 Duo you can,
bring the HDs from 2 over, so you didn’t waste any money
So you are suggesting that I first try upgading my current PC with 2-3 extra hard drives. I hadn’t thought of that. Do you think limiting my sql server to 400mb is okay, or shall I put another gig or so of RAM in my current PC? What sort of hard drives should I look at?
sql does not require a specific amount of memory,
the idea is to use memory to cache data to reduce disk io
however, if there are other apps using memory,
and sql did not leave enough for the other apps,
then sql or the other apps will page, which is really bad pay attention to when your system is slow
is cpu high?, if no then adding cpu does not directly address your problem
it does add memory, but what you are trying to avoid is memory contention next in line in managing a 30GB db is to be able move the data
disk ops is comprised of multiple operations,
one of which may be a sequential read
however with just 1 disk, the sequential read and other ops combine to have non-sequential characteristics
so having multiple disks, atleast 1 for each load, helps you get the sequential disk access for the one big load if not, then you did n’t lose any money because you can use the disks on your budget, there is not much choice, probably 250-320GB is good,
cheap enough to let you get 2-3,
and still have good capacity/$ (pound in your case) ratio
Thanks for your help, I have ordered 2 Sata 160gb hard disks and an extra gig of RAM. I will put the data files on one disk and the log files on the other, then give sql server about a gig of RAM and see how I get on. Andrew
]]>