| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2004-11-08 : 09:44:51
|
| We have a client with a 4CPU Xeon Dell box (I don't know the exact Spec. off hand). This has a large array (6 or perhaps even 8) disks all on a RAID 5.The box is running IIS and SQL Server (its also running MySQL, Sybase NN and some other web server - but all that is due to be removed shortly).IIS is using about 8% CPU and SQL Server is hovering around 50~60%.Clearly optimising SQL and the application is a good way forward, however I have some general questions.If we rebuild the disks to provide some RAID 1 (2 disks I presume) and RAID 10 (the rest I presume) and IIUC put the Logs on RAID 1 and data on RAID 10 what sort of improvement could we expect - are we talk the odd percentage point, or something significant?Also, if we got a "posh" [but not totally silly money] SAN, sensibly configured, would that provide significant performance improvements, or are we talking single percentage points?I'm going to post a PerfMon picture when I've got a moment to ask for some more specific suggestions and advice, but I'd like to know a ballpark for improvement with a properly configured disk subsystem over "plain RAID 5"ThanksKristen |
|
|
MuadDBA
628 Posts |
Posted - 2004-11-08 : 13:15:05
|
| If you're not going to increase the amount of disks, then making a raid 10 out of 6 disks might see worse performance than making a raid 5 out of them, because you would only be striping across 3 disks instead of 6. It would depend on if your system is read heavy or write heavy.Stepping up to a SAN enclosure will probably show a marked improvement, because I can hardly see the point in buing a SAN with only 8 disks in it. You'd get more disks and take the read-write load off of the CPU of the server and put it on the SAN. Our SAN is like night and day. Granted we made a MAJOR jump, but tablescans that used to take hours are now returning in minutes. |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-11-08 : 13:22:43
|
| Well, personally I think you have way too many things running on that box. Seeing the CPU at 50-60% might not be the total picture. A few questions:1. What do your Average Disk Queue Lengths look like for the drives in question? If they are above 2.0 for any length of time, you probably need to add disks. 2. Is your database read heavy, write heavy, or about 50/50?For read heavy, I'd go with a two-disk RAID 1 for SQL TXLogs, and then whatever you have left as a RAID 5 for your SQLData. If it's write heavy, make that RAID 5 and RAID 1/0 and add some disks to the RAID 1/0 array.3. What type of disks do you have now? What type of SCSI, RPM's etc.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-08 : 15:27:07
|
| "I think you have way too many things running on that box."I agree ... but!! ...The existing "other internet stuff / mysql / sybase" is legacy. All bar one rarely-used web site have been moved to IIS/MSSQL - so apart from the memory footprint, the occasionaly CPU actiuvity, AND the instability :-( I think we are mostly seeing the real world.1. I'll post a PerfMon image shortly2. Do you know I don't know. I wouold assume Read heavy, but I honestly don't know. Is there a simple way to evaluate that?3. I'll checkN. <g> Please assume that for revised infrastructure, or SAN, that we can add additional drives (until we have "enough"!) or throw them away and start again.We can throw the box away too, but I'm interested to know whether restructuring the disks will be a nig improvement.crazyjoe: Pleased to hear it was a significant improvment. When you say "we made a MAJOR jump" what sort of jump? The sort that makes it impossible to pinpoint how much contirbution each bit made?!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-08 : 15:31:41
|
| IIS and SQL Server should not be running on the same box. This is a major no no for performance reasons.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-08 : 15:37:32
|
| Here's what I can see in Control Panel ec. The rest I'll have to quiz tomorrowXeon CPU 1.40 GHz x86 Family 15 Model 1 Stepping 1 (4 x CPUs)3,669,488 KB RAMDell PowerEdge 6650Drive Model PERC LD 0 PERCRAID SCSI Disk Device - 131GB(Looks like there are 5 drives currently)SCSI : Adapted AIC 7892 - Ultra160 SCSIKristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-08 : 15:39:38
|
| "IIS and SQL Server should not be running on the same box. This is a major no no for performance reasons"I'm with the program on that, but we're seeing only about 8% CPU for IIS, and 50~60% for SQL. Under those circumstances is IIS such a bad bed-fellow do you think?(The client will spend money on improving this setup, but I want to establish some "feel" for what sort of improvements, relative to bang-for-buck, the client can expect)Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-08 : 15:42:35
|
| I would have to see a bunch of counters in PerfMon in order to say IIS and SQL Server are fine running on the same box. If this is a mom and pop shop, then I'm sure they can get away with them being on the same box.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-08 : 16:16:19
|
| Nope, its aint no Mom&Pop (by my metrics at least!)100,000 pages per day, 1.5million SProcs per day (Those that are logged by our systems, we don't log the very-high-usages SProcs). System has pretty much an even loading from 08:00 to 23:00. (Say $50,000 - $100,000 sales per day).Perhaps easiest way to look at this is that the Web site is doing more busines than any of the client's individual Bricks&Mortar stores, so the Client will spend money on the infrastructure. I'd like to be able to give "This is worth X" and "That is worth Y" type comparisons to the client.Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-08 : 16:23:52
|
| Here's a picture:http://www.fbureau.co.uk/kbm/PerfMon_041103.gifThe red line is CPU (scrolled off the top of the Legend), the black line is "Batch Requests/sec"I'm not-much-cop at this, so if the counters are rubbish let me know and I can re-run them.Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-08 : 16:32:38
|
| Can't get much information from the image. Could you run this as a Counter Log instead of from the System Monitor GUI? Save the results to a blg file, then send it to me/us. This allows me/us to pull the PerfMon information into my PerfMon and do CTRL+H and stuff. Here's some of the counters I'd be interested in:Memory\Available MBytesMemory\Page Faults/secMemory\Pages/secPhysicalDisk(_Total)\% Disk Read TimePhysicalDisk(_Total)\% Disk Write TimePhysicalDisk(_Total)\Avg. Disk Queue LengthPhysicalDisk(<each>)\% Disk Read TimePhysicalDisk(<each>)\% Disk Write TimePhysicalDisk(<each>)\Avg. Disk Queue LengthProcess(sqlservr)\% Processor TimeProcess(sqlservr)\Page Faults/secProcess(<whateverTheIISprocessIs>)\% Processor TimeProcess(<whateverTheIISprocessIs>)\Page Faults/secProcessor(_Total)\% Privileged TimeProcessor(_Total)\% Processor TimeSQLServer:Access Methods\Page Splits/secSQLServer:Buffer Manager\Buffer cache hit ratioSQLServer:Databases(_Total)\Transactions/secSQLServer:General Statistics\User ConnectionsSQLServer:Locks(_Total)\Number of Deadlocks/secSQLServer:Memory Manager\Total Server Memory (KB)SQLServer:Memory Manager\Target Server Memory (KB)System\Processor Queue Length...anything else you'd like to addI'd run it during peak times for a few hours. If you can't, then maybe write out what the min, max, and avg values for each of the counters that you monitored.Tara |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-11-08 : 16:33:55
|
| The most important stat is the average disk queue length on that RAID 5 set. Don't get the one that says "total" get the one jsut for the RAID 5 set.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-08 : 16:45:45
|
| OK, will-do. Thanks.I may be gone some time ... !Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-09 : 08:08:54
|
| I'm using Terminal Services to grab some PerfMon data. I can't find a way to get past the firewall to get a connection from here ... so ... do I need to keep T.S. running, or will PerfMon keep collecting data? (TS is set to log me off etc. when I disconnect)I set PerfMon to collect data for 2 hours - so I'm fairly confident it will actually stop!Kristen |
|
|
|
Kristen
Test
22859 Posts |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-11-09 : 12:49:02
|
| Well, I took a look at the file.I didn't see anything that really stood out besides the fairly high CPU usage. The Disk Queue didn't look too bad. Do you have Full Text running? Analysis Services?I know right now you are looking at improving the disk setup for SQL server, and that's usually the best place to start. I think in this case, the best place to start is to isolate SQL server from everything else. Move everything except SQL server off that box and get a new webserver for IIS. Just from a security point of view, this is a good move. You don't want your SQL server to have access to the outside world, and vice versa.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-11-09 : 12:50:35
|
| Kristen, the jump we made does make it pretty hard to calculate where all the improvement came from.We used to have an eiStream workflow domain with one "Domain Controler" and 9 "Child Servers." Each server had services for eiStream Wofkflow AND SQL Server running on it, competing for memory and disk space. We also had one LOCATOR server which was simply a database server. Each DC/Child server had about 200GB of RAID5 allocated to it, most of which was used by the workflow software to store images before they were archived. The DBs didn't get above 5GB.The LOCATOR server was a different story. It also had about 200GB of raid5, but used about 40 of it for the database. I think it had 3 different raid5 sets, but as they have retired it, I can't be sure. Each raid5 set was 5 36GB drives, and I had the data and indexes on seperate filegroups and seperate raid channels.We moved to a SAN storage solution, at the same time retiring all the servers in the domain. We condensed the 9 child servers into 4, and put all of the databases on one clustered server. Everybody points to the SAN, and we each get a slice of space which is spread across all the disks in the enclosure (currently about 2.5TB). This is not how I wanted to set it up, but it's how the guys who set up the SAN insisted it be done, and who am I to argue :). Anyhow, the performance increase has ben phenomenal, but god help me if I could pinpoint one place of improvement. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-09 : 13:05:34
|
| I agree with Michael.Looking at the PerfMon blg file, the only thing that stands out is the high CPU. Average is 70. You don't have much more room left on that box. It's not currently a bottleneck, but it's getting really, really close to being one. BTW, it looks like you've got some other process that is using about 10 percent of the CPU. SQL Server is only using 57 percent and IIS is negligible. If SQL Server were on this server by itself, then things would look much better for the CPU stuff. Memory looks great. Disk performance looks good.Tara |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-11-09 : 13:11:27
|
| Also, if you move SQL server to a new server, some newer Hyper Threaded CPU's might be a good idea. Dual 3Ghz CPU's are cheaper to license than Quad Xeons due to the per processor licenses. You can usually save money by moving from Quad's to Dual's assuming you've not gotten all of your per processor licenses. If you have all four, then there's no savings by going to dual, unless you can re-use those per processor licenses elsewhere.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-10 : 02:10:46
|
| Folk, thanks very much."Do you have Full Text running? Analysis Services?"Nope"Move everything except SQL server off that box and get a new webserver for IIS"I'm actually thinking it the other way about. We're at high CPU already, IIS is using a negliable amount of umph ..."BTW, it looks like you've got some other process that is using about 10 percent of the CPU"... they'll be the MySQL and other Internet service - shortly to be retired ...So I could get a new box, just for SQL, with faster [more?] CPUs to guarantee more horsepowerI presume the database is small enough that we are caching all that we reasonably can, so we are CPU bound - is that about right?So I could make some inroads on optimising some SProcs.Is there a way I can find out what SProcs are called (I'm meaning the ones that are called from other Sprocs, rather than from the application). I'd be interested to find out about the ones which we have none of our own logging in - because we know they are getting hammered a lot!Kristen |
|
|
|
Trebz
Starting Member
7 Posts |
Posted - 2004-11-10 : 09:04:35
|
| How much cache do the Xeon's have, I would imaging for data intensive tasks such as SQL Server, CPU cache would be quite important. 4 x Xeon's with 2MB Cache each would very likley be quicker than those with 1MB each.From http://www.sql-server-performance.com/hardware_tuning.asp"When selecting your CPU for your server, select one with a large L2 cache. This is especially important if you have multiple-processor servers. Select at least a 1MB L2 cache if you have one or two CPUs. If you have 4 or more CPUs, get at a least 2MB L2 cache in each CPU. The greater the size of the L2 cache, the greater the server's CPU performance because it reduces the amount of wait time experienced by the CPU when reading and writing data to main memory."Wouldn't have thought more CPU's are required, guess it depends on how much of the work it's choosing to run in parallel.CPUID will tell you processor information.Oh, and on the subject of disk array's - "If your budget is tight, and you have a choice to devote your scarce dollars to either more RAM or to a faster disk subsystem, buy the extra RAM. Dollar per dollar, RAM gives you more performance than the same number of dollars spent on a faster disk subsystem" |
|
|
|
Next Page
|
Hardware Advice