Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2004-12-01 : 00:41:32
|
| My client has an offer for rent of a box for a month to get him over the Christmas rush.Dell PowerEdge 6600 4x3.0GHz CPUs with 4MB cacheWill this do the trick, and how do we need it configuring?In particular how should the drives be spec'd? And will we need to spread indexes etc. over different drives?He's currently on a 4 Xeon box (around 1GHz I think) with 3GB RAM and all disk space on RAID 5. Its maxing out on CPU at peak times.Part of the plan is to use this as an experiment to see what a "decently configured" box could deliver, and then to take a decision on keeping this box, or going for something bigger (possibly including SAN, fail-over server etc.)New box needs to cover at least 4x the current workload.Any thoughts would be appreciatedKristen |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-01 : 14:07:08
|
| <bump>Shout if you need some more info (DB size/SProcs per hour/Visitors etc.) to help me create a good solution pleaseKristen |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-12-01 : 16:41:51
|
| Is it SQL Server Standard edition? How many drives are on it? What is the RAID adapter compared to the other server? Where are you experiencing your bottlenecks? Is the disk IO maxing out causing things to backup and the CPU to spike? Is the memory utilization maxing out causing it? Is it just the CPU maxing out? How many concurrent users do you now have? Will the user connections be increasing, or just the volume of users using those pooled connections? What size are the databases? You should probably give us a 4 or 5 hour average of the perf mon counters if you can get your hands on them and a complete spec of the both servers including RAID, drives (size, speed, etc), and OS layout.Also, are you tracing the box to see where the hotspots are? Specifically, are you looking at long running, heavy reads, heavy writes, and heavy CPU??? It's possible you could combine a little performance tuning with the server upgrade and get a huge cushion.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-01 : 18:49:20
|
| Is it SQL Server Standard edition?Currently yes; New box not decided yet. I'd like to try Enterprise to see if >2GB RAM makes a significant difference (i.e. set the Max RAM to 2GB for one day, and then max available next day, and see what the difference is)How many drives are on it?What is the RAID adapter compared to the other server?For the new box we can choose these. Current server just has single RAID 5.If I understand correctly the new box should have a pair of drives on RAID 1 for logs, and as many drives as possible on RAID 10 for the data. Do we need additional, separate, RAID 1 for operating system?Where are you experiencing your bottlenecks?We are mostly CPU bound at present. BLG file available at http://www.fbureau.co.uk/kbm/PerfMon041109.zipIs the disk IO maxing out causing things to backup and the CPU to spike?We get that sometimes - what we call a "disk storm". However, we haven't seen that for a few days. We've increased free disk space from almost nothing to around 40GB, so maybe the Disk Storm was Windows trying to scrounge some swap space / elbow-room etc.Is the memory utilization maxing out causing it?Doesn't look like itIs it just the CPU maxing out? How many concurrent users do you now have?When it gets busy we are getting up to 200 users or so. This is just a single IIS application using the same [single] login to the database. Today we've set a limit at 100 user connections or @@CPU_BUSY vector > 1,000 - vector is calculated as:(change in @@CPU_BUSY) * 10,000 / time_interval_in_millisecondsthe time_interval is one minute-ish - above that and we start denying new sessions on the web site - so the existing sessions can get on, and new visitors will have to come back later. Will the user connections be increasing, or just the volume of users using those pooled connections?We expect more visitors to the site, but if they are serviced more quickly I don't think the number of pooled connections will increaseWhat size are the databases?Single main database. Around 7GB. (There are other databases - for example a set of backoffice-data which is updated from the back-office once an hour, and then "staged changes" are applied to the main database; these other databases have very little activity. This "transfer database" is around 5GB. The others are even less used and <1GBYou should probably give us a 4 or 5 hour average of the perf mon counters if you can get your hands on them and a complete spec of the both servers including RAID, drives (size, speed, etc), and OS layout.See above for .BLG file. Its a few hours durationCurrent box is:Dell PowerEdge 66504xXeon 1.40 GHz x86 Family 15 Model 1 Stepping 13GB RAMPERC LD 0 PERCRAID SCSI Disk Device - 131GBI think there are 5 drives currentlySCSI : Adapted AIC 7892 - Ultra160 SCSIAlso, are you tracing the box to see where the hotspots are? Specifically, are you looking at long running, heavy reads, heavy writes, and heavy CPU??? It's possible you could combine a little performance tuning with the server upgrade and get a huge cushionWe've had a go at the worst performing SProcs. Also done a major exercise of IndexDefrag / Rebuild and UpdateStats. That alone gave us a 20% overall average SProc speedup.Thanks for your help,Kristen |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-12-01 : 18:53:09
|
quote:
Do we need additional, separate, RAID 1 for operating system?
YesI'd get as much RAM as you can afford. At very least 4GB total, ideally 8GB, and dedicate most of that to SQL Server.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-01 : 19:01:02
|
| "I'd get as much RAM as you can afford"We need Enterprise Edition for that, right?We (our shop, rather than the client) have EE licenses, which we could use as an experiment. However, its a big purchase for the client - and I'd like to prove the benefit before advising the client.(I think we are talking 10,000 GBP per CPU for EE licence.)Kristen |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-12-01 : 19:15:28
|
| Doh, I forgot about that!I so used to running EE that I forgot about the 2GB limit.OK, if that's the case, spend money on Drives then. Maybe a 6-8 disk RAID 1/0 set for your MDF's? Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-12-01 : 21:10:25
|
| If it's down to drives, I would try RAID 1 for the OS, RAID 1 for the tempdb, RAID 10 for the logs, and RAID 5 for the data. If you have the money to afford that, it will offer the best performance. Of course, you're looking at about 13 drives there, so you'd need a U320 enclosure for it. The 2gb of RAM might be enough if you have a good cache hit, buffer cache hit, and the target and total memory are matching up good now. Otherwise, you'll be looking at more RAM. Find out how many drives you have to put in there. We can then better advise. You also might want to look at dual channel RAID adapters.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-12-02 : 15:33:11
|
| Keep yer OS and SQL stuff separate.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-03 : 02:18:57
|
| "Keep yer OS and SQL stuff separate"In the absence of masses of channels is it better to have OS in one channel and Logs and Data sharing the second? Or is three the kind of "absolute minimum" number of channels?Or did you mean create two drives on a single RAID 1 channel - C: is OS, D: is LOGs and then E: (on a second channel)?Sorry, I'm probably being dense, but I could do with a rule of thumb. Suggestions on the following would be appreciated:Starter server--------------One RAID 5 channelC: OSD: Everything elseor C: OSD: LOGsE: DataMedium server--------------2 Channels. [1]=RAID 10, [2]=RAID 5C: OS [1]D: Logs & tempdb [1]E: Data [2]Decent server--------------??Best(?) server--------------4 Channels [1]=RAID 1, [2]=RAID 1, [3]=RAID 10, [4]=RAID 5C: OS [1]D: tempdb [2]E: Logs [3]F: Data [4]Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-04 : 13:41:47
|
| Update:Well the switch over went really well ... :-(Switched over in 14 minutes (would have been half that but I didn't have a "throw users off" script handy when the new server's restored database was in ReadOnly mode and I wanted to make it ReadWrite and somewhere something had a link to is - nothing in Current Tasks ... Anyway, I thought that wasn't too bad for 14GB of databases. And none of the web users lost their sessions ...... and 10 minutes later the new server failed and we had to roll back to the old server - which took less than 5 minutes!So the CPU_BUSY routine can earn a bit more kudos as the 4 hour response from Dell will presumably take until Monday or Wednesday the week after next to solve the problem ...Maybe I'll get paid for two rollouts, maybe for neither of them (or maybe that will become getting paid for None of them) :-(Kristen |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2004-12-05 : 16:09:21
|
I had a similar problem with a new Dell server... Make them open the box and audit for parts my machine was missing one of the CPU's and it took a couple of weeks to get them to come out, mean while back on the farm I'm bashing my head in trying everything possible while watching my clients confidence in me go down the toilet!!! Get tough with them! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-06 : 01:47:42
|
| They came out yesterday, replaced the CPUs, and checked everything over.We ran all sorts of stuff yesterday to check the machine was OK.I got up at 5AM this mornign to make the cut over, transfered the backup from old server to new and Bang! down she went again.Monday night is always very busy for the client, so today should be the client's busiest day ever ... :-(Kristen |
|
|
|
ronstone
Starting Member
32 Posts |
Posted - 2004-12-07 : 10:11:00
|
| Following this thread closely, keep us updated Kristen! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-07 : 12:58:38
|
| Hehehe ... they came out against yesterday, and replaced the WHOLE box this time. We cut over to the new box this morning ... so far so good.Kristen |
|
|
|
|
|
|
|
|
What Hardware for SQL Box