SQL Server Performance

Server Hardware Opinions Please (db and iis)

Discussion in 'Performance Tuning for Hardware Configurations' started by kenp2600, Dec 16, 2005.

  1. kenp2600 New Member

    Please help me decide what to do about my current hardware configuration.

    I have an ASP.NET app that uses SQL Server for the database. Currently both IIS and SQL are running on the same machine (see machine 1 below). I want to separate it so that IIS and SQL each have their own machine but I have a very limited hardware budget right now. I am trying to decide if it would be worth moving either IIS or SQL to another machine that we have, or if I would actually lose performance by doing so considering the extra machine I have is a bit outdated (see machine 2 below).

    Should I leave well-enough alone or try to split it to these 2 machines I have. (buying new machines aren't an option right now although that's what I'd like to do). I could probably afford a memory upgrade on one or both computers if necessary. Which computer should do which task and is it worth adding RAM to either or both?

    Machine 1
    Dual XEON 1.8 Ghz w/ 1G RAM

    Machine 2
    P3 1.13 Ghz w/ 512K RAM

    Thanks
  2. brimba New Member

    Most prabably you want to put the web application on machine 2 and leave the DB on the machine 1.

    But I dont know how processor intensive your web application is and I dont know how stressed yor database server is. But running them on same machine with such little ram is probably worse than having machine 2 running your web application.
  3. kenp2600 New Member

    I guess that's is my dilemma. Since machine 2 isn't exactly a powerhouse, I was wondering if I would be better off just bumping machine 1 up to 4 gig of ram (or more) instead of moving the web server to it's own machine.

    I definitely would like to see a performance increase across the board over what I have now (both in IIS and SQL).

    I fear that with many people hitting my ASP.NET application that the P3 might not be enough hardware to run IIS comfortably even with a RAM upgrade. I'm not familiar enough with IIS requirements to make an educated guess there.

    I figured the more opinions on this the better...
  4. brimba New Member

    There are so many questsions...<br /><br />How big is your database?<br />How heavy loaded is your machine running booth sql-server and iis?<br />How many hdds and what type do you have in the machines?<br />What type of application do you run?<br /><br />The more you can tell about your applications the better answer we can give you <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  5. ghemant Moderator

    Hi,<br />these are some common guidelines , but again it *depends* upon your sql server's and iis load ,raid controller etc. :<br /><br /<a target="_blank" href=http://www.oucs.ox.ac.uk/windows/winnt/security/index.xml?style=text>http://www.oucs.ox.ac.uk/windows/winnt/security/index.xml?style=text</a><br /><br /<a target="_blank" href=http://msdn2.microsoft.com/en-us/library/ms178375.aspx>http://msdn2.microsoft.com/en-us/library/ms178375.aspx</a><br /><br /<a target="_blank" href=http://forums.asp.net/869436/ShowPost.aspx>http://forums.asp.net/869436/ShowPost.aspx</a><br /><br /><br /><img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami<br />
  6. kenp2600 New Member

    Here are some specs on what I have.

    We have around 25 small databases on this server (each with duplicate structure but different data. They total about 6 gigabytes in size and grow a little every day. We have activity by approximately 50 remote users through our web application, also we have 3 "power" administrative users that are constantly running scripts, doing data cleanup, conversions, imports, etc.

    The web application is ASP.NET and basically includes a "view/edit" screen for most tables in each database, it also runs crystal reports and queries the data to display a lot of overview information and graphs. Also there are nightly "number crunching" routines that run that insert about 100k records into each database and probably do about 75k queries on each database. They run over a period of about 8 hours to cover all databases.

    The machine currently also runs Terminal Services, TS Licensing, Certificate Server (I know TS is a bad idea on a SQL or Web Server too.)

    As for hard disks, I think (not 100% sure) there are 3 32 gig SCSI drives in a RAID 5 configuration with a 6 gig system partition (OS + Executables) and a 60 gig data partition (all databases including tempdb)

    I can't give exact numbers on load, but suffice it to say there is noticable slowdown of the application and SQL queries when anything significant is happening (ie, the nightly routines or a data import).

    Thanks for all the other info too.
  7. SQLDBcontrol New Member

    I'd agree with the first poster to reply to you. If you can put the web server on the slower machine then this would be your best solution. There are a few caveats though.<br /><br />This assumes that it is the SQL Server that is doing most of the hard work, and the web server isn't doing much other than posting and retrieving data to/from the SQL Server. If you are handling a lot of the business logic on the web server or if it is processor intensive then it might be a bad idea putting the web server on that machine because it really is pretty bad. I've got a two year old laptop that has better specs - and no, you can't have it[<img src='/community/emoticons/emotion-5.gif' alt=';)' />].<br /><br />The one thing I would seriously consider, whichever option you decide to take is to upgrade your Xeon machine. <br /><br />As you suggested, 4 GB is a good start and RAM is relatively cheap.<br /><br />The other area where you can really increase performance is your disk configuration. The speed of the transaction logs (the disks namely) contribute the most to SQL Server database performance.<br /><br />As a minimum you should consider splitting off the log files for your databases and putting them on a RAID 1 array (which is faster at dealing with writes than RAID 5 and that's almost all you ever get on log files).<br /><br />Getting hold of two high-performant (15k RPM) disks for your RAID 1 array is pretty cheap - and a seperate disk controller wouldn't go amiss either <img src='/community/emoticons/emotion-1.gif' alt=':)' />.<br /><br />Making this change in itself should help considerably. Of course you can go ever further by putting your database data files on a seperate array (away from the OS system drive) but I'm assuming you've got a limited budget.<br /><br /><br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by kenp2600</i><br /><br />Here are some specs on what I have.<br /><br />We have around 25 small databases on this server (each with duplicate structure but different data. They total about 6 gigabytes in size and grow a little every day. We have activity by approximately 50 remote users through our web application, also we have 3 "power" administrative users that are constantly running scripts, doing data cleanup, conversions, imports, etc.<br /><br />The web application is ASP.NET and basically includes a "view/edit" screen for most tables in each database, it also runs crystal reports and queries the data to display a lot of overview information and graphs. Also there are nightly "number crunching" routines that run that insert about 100k records into each database and probably do about 75k queries on each database. They run over a period of about 8 hours to cover all databases.<br /><br />The machine currently also runs Terminal Services, TS Licensing, Certificate Server (I know TS is a bad idea on a SQL or Web Server too.)<br /><br />As for hard disks, I think (not 100% sure) there are 3 32 gig SCSI drives in a RAID 5 configuration with a 6 gig system partition (OS + Executables) and a 60 gig data partition (all databases including tempdb)<br /><br />I can't give exact numbers on load, but suffice it to say there is noticable slowdown of the application and SQL queries when anything significant is happening (ie, the nightly routines or a data import).<br /><br />Thanks for all the other info too.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com
  8. kenp2600 New Member

    Awesome information. I really appreciate the help. One thing I'd like to get some more info on though...

    What RAID should I use for the OS array? RAID 1 for it as well?

    So for best performance, I should have 1 RAID 1 for OS, 1 RAID 1 for logs, and 1 RAID 5 for data? I guess that means a minimum of 6 or 7 physical drives. Should I have 3 physical SCSI controllers too (1 per RAID) or will multiple channels on 1 or 2 controllers suffice?

    Finally, will putting the OS on it's own drive require a complete reinstall of the OS or is it possible to remove one of my existing drives from the current RAID 5 and convert that to RAID 1 without setting everything up from scratch?

    Thanks again for all the information. This forum is a great resource.
  9. Luis Martin Moderator

    OS and logs with Raid 1 is Ok.
    Now deppending on what kind of application do you have (more reads than writes or viceverza?) is important to decide if Raid 5 or Raid 10.
    Anyway more channels more performace.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  10. SQLDBcontrol New Member

    Hi,

    glad this helped.

    Your data files can go on RAID 1 or RAID 5 and that depends much on what type of system you have. Generally the recommendation is that if you have a system where 90%+ of the work involves reads then RAID 5 should be considered. However, if less than 90% of the work involves reads then you should consder RAID 10. RAID 10 is twice as fast as RAID 1 so I would read between the lines with regards to the above percentages. Bottom line is that RAID 1 is better for write performance so you have to weigh up what is more important to you.

    The more channels or controllers you use the better the performance but that is something you can add to as time goes by.

    I must admit that I haven't had to convert a RAID 5 to a RAID 1 array so I'm not sure how that would go. I suspect that you might have to rebuild but it's worth investigating that. If you do have to rebuild it's worth biting the bullet and going for it. At least, by starting from scratch, you're starting from a strong foundation that allows you to build upon.

    Good luck.



    quote:Originally posted by kenp2600

    Awesome information. I really appreciate the help. One thing I'd like to get some more info on though...

    What RAID should I use for the OS array? RAID 1 for it as well?

    So for best performance, I should have 1 RAID 1 for OS, 1 RAID 1 for logs, and 1 RAID 5 for data? I guess that means a minimum of 6 or 7 physical drives. Should I have 3 physical SCSI controllers too (1 per RAID) or will multiple channels on 1 or 2 controllers suffice?

    Finally, will putting the OS on it's own drive require a complete reinstall of the OS or is it possible to remove one of my existing drives from the current RAID 5 and convert that to RAID 1 without setting everything up from scratch?

    Thanks again for all the information. This forum is a great resource.

    Karl Grambow

    www.sqldbcontrol.com
  11. Twan New Member

    Hi ya,

    I think that before you consider splitting the two machines you need more information...

    you should collect things like
    - processor utilisation
    - memory pages/sec
    - disk utilisation
    - sqlservr.exe process processor utilisation

    also you need an understanding of how well the .net app is written with distribution in mind. around 90% of all performance problems come down to application issues, and one of the common ones is splitting an application which was never designed to do so...

    armed with the above info you should be able to decide whether there is any benefit in adding additional memory, changing disk configs, etc. In a new server we all know some of the best practice methods for build one, but you really know what the bottlenecks actually are before attempting to try to resolve a performance issue with existing kit

    Cheers
    Twan

    just a query about RAID 10 being twice as fast as RAID1, the speed of a single read/write will be the same, but the throughput is higher since RAID 10 be definition must have at least twice the number of spindles

    in terms of changing a raid set from 5 to raid 1, yes you'd need a full backup, rearrange the disk, rebuild the server os and then restore the backup
  12. dtipton New Member

    Are you running SQL Standard Edition or Enterprise Edition?

    If Standard be sure to take a look at the maximum amount of RAM SQL Server can use (2GB I believe).

  13. kenp2600 New Member

    I believe we're running Enterprise Edition.

    I'll try to get a bit more information on current utilization, however, I know we need to split these out sooner or later. We are bringing on new clients every week and each time that happens it means an additional database, additional web app users, additional nightly processing work, etc. and I'd like to have some headroom so our hardware doesn't limit our growth.

Share This Page