SQL Server Performance

SQL Server 2005 - Building a development Server

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by rlcoach, Mar 10, 2007.

  1. rlcoach New Member


    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
  2. joechang New Member

    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
  3. rlcoach New Member

    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

    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?
  4. joechang New Member

    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
  5. rlcoach New Member

    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

Share This Page