SQL Server Performance Forum – Threads Archive

Server Hardware Opinions Please (db and iis)

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

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.

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…

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

‘ />

Hi, these are some common guidelines , but again it *depends* upon your sql server’s and iis load ,raid controller etc. : http://www.oucs.ox.ac.uk/windows/winnt/security/index.xml?style=text</a> http://msdn2.microsoft.com/en-us/library/ms178375.aspx</a> http://forums.asp.net/869436/ShowPost.aspx</a> <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami

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.

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. 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=’

‘ />]. The one thing I would seriously consider, whichever option you decide to take is to upgrade your Xeon machine. As you suggested, 4 GB is a good start and RAM is relatively cheap. 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. 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). 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=’

‘ />. 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. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by kenp2600 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. <hr height="1" noshade id="quote"></blockquote id="quote"> Karl Grambow www.sqldbcontrol.com

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.

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.

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

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

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).

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.

]]>