0

First - while I'd totally be down for advice; I'm also interested if there are any good ways of measuring/collecting data in advance that I could use to determine which 'rules of thumb' to follow and which to skip.

I can't change the schema significantly for the application, and while I've got some suspicions that the developers went index-happy; I don't want to take out their indexes and replace them just yet.

I've got a decent-sized database for a telephony application, about 60GiB or so. The data breaks up into, mainly:

  • TRAFFIC table (constant sequential writes, occasional long scans along any number of indices for reporting, grows rapidly. Associated Index is huge as well.)
  • BILLING table (constant sequential writes, often scans along account_info for user information)
  • Everything else (constantly random access reads and writes, more frequent than the other two tables.)

I'm on a single-core machine right now with 3Gb devoted to SQL Server and disk cache in SQL Server 2005. The msdb database and OS are on a 15k hard drive, RAID 1. The log files and backup drive are on a 15k hard drive, RAID 1. The telephony DB is all on 2 x 15k hard drives, RAID 10. (That's eight drives total.)

I'm upgrading hardware in a big way. Going to have SQL Server 2008 R2, 12 cores, 32Gb of RAM on Server 2008 R2 x64. It'll have four internal HDs, probably for OS and backup, but the biggest change is the attached storage - 12 x 15k drives on an external SAS interface.

We're a bit CPU/I/O bound on the current server, but not enough to bother much - this new servers going to heal a lot of sins - but I'd still rather do it 'right' this time.

I could do a massive RAID 10 of 6 disks and throw the entire DB onto it, but I've been considering breaking up TRAFFIC and it's index files onto separate partitions - and possible BILLING as well. In addition, the Everything Else might do well in it's own File.

Then, of course, there's always RAID 10 vs RAID 5/6, vs RAID 50/60 to consider.

I'd love some guidance on how to approach this decision and how to determine whether it's worth breaking up the drives to put TRAFFIC/BILLING seperate - and whether I'd be fine with two filegroups (PRIMARY and USERDEFINED) or if I'd need more.

By the way, sizeof(TRAFFIC) = sizeof(BILLING) * 2 >> sizeof(EVERYTHING ELSE)

Thanks,

Improve this question

3 Answers 3

Reset to default
1

Lets start from the top.

I've got a large database for a telephony application, about 60GiB or so

Rephrase that: I have a pretty small database. Seriously, the time where 60 giga where large is about 10 years ago. Compare that to: I have a finaincial data database that has 800gb and growing, with 95% of the data in one table ;)

It'll have four internal HDs, probably for OS and backup, but the biggest change is the attached storage - 12 x 15k drives on an external SAS interface.

Here is what I would do:

  • Mirror two discs for boot. Put off a 64gb partition down, the rest up you use for TEMP. You do not want to see a lot of IO there.

  • Mirror the next 2 discs for logfiles. If those run hifh on IO - relace them with SSD. Given the small amount of changes you have.... small 80gb SSD should be enough.

  • The rest (12 discs), put up a hugh RAID 10.

More important is that you reconfigure our server to use:

  • Minimum 12 data and log files for tempdb. Do NOT autogrow those. Fix them.
  • Minimum 12 log files. No joke. Do not autogrow here, either.
  • Minimum 12 database files. Did I say - no autogrow?

Then, of course, there's always RAID 10 vs RAID 5/6, vs RAID 50/60 to consider.

What please is to consider there, given the HUGH performance differences between Raid 10 vs the others - which all blow the water out of Raid 5/6/50/60 for anyhing requiring high IO. RAID 5 / 6 make onl ysense if you put in SSD drives - then the significnat IO loss will be totally eaten up. Actually given your trivial database size, it may be financially idiotic to even go with 2x15 SAS discs. Get 2 x200gb REALSSD drives and you will have about 100 times the IO performance if a RAID 10 over your 30 drives. Given the significant cost of the infrastructure you may save a LOT of money on the way.

Actually the smartest thing would be to not ordet the whole SAS thingy - you have 4 drive slots, put the OS on two drives, use 200gb SSD in a Mirror on the other one. Finished. And a LOT faster than your SAS stuff, too ;) THe joy of having a trivial datbase size. Check http://www.fastestssd.com for the current state. A modern SSD will reah 200mb sustained random rates in that setup, even if not top of the line. THis will seriously wipe the floow with the mediocre IO you get from your SAS setup.

Or: 30 SAS discs are maybe 4800 IOPS. RealSSD gets up to 50.000 - on one disc, with "weak times" of around 36.000 IOPS. That means that ONE SDD is about 7,5 times as fast - in slow moments - as your 12 disc setup. Around 10 times as fast in good times. Ouch.

Be carefull to properly align the parittions and properly format the file systme (hint: do not use the standard 4kb node size - stupid for SQL Server).

I could do a massive RAID 10 of 6 disks and throw the entire DB onto it, but I've been considering breaking up TRAFFIC and it's index files onto separate partitions - and possible BILLING as well. In addition, the Everything Else might do well in it's own File.

That would be stupid abuse of SQL Server. GIven that it does load balancing between files and wants/ asks for multiple files per group (one per logical processor) it would not gain anything - au contraire. Separating files and indices achieves NOTHING if they end up on the same discs anyway. In your case you are better off with one filegroup, 12 files. If you want later scalability, you may want to go for 48 data files to start with - gives you room up to processor 48 cores.

You may want to use two filegroups to splt off the billing data from the less volatile -/ requested one, but not for direct speed, but for the priviledge of posibly moving them totally later off without reorganization - that is what I did with my financial database.

Last words: whoever purchased the server made a bad decision hardware wise. There is no reason to have an external SAS tray for something that small. My database server is from SuperMicro and has 24 disc slots in 2 rack units height. That is without external cage. I dont really want to compare the numbers here- but I bet it was a lot of wasted money.

Improve this answer
7
  • I'm purchasing Dell Servers - (and I specced the HW.) The external SAS device is to be shared with another virtualization server - it's 24 discs, so the other 12 will go to my VMWare box when it's needed. Hadn't occured to me to put it onboard, but I'll look into it. It'll have 2 physical processors, both licensed - doesn't that imply that breaking up the filegroups would be worthwhile? Isn't it better to have 4-6 spindles handle the historical data and have the rest handle the realtime data? Why put the logs on internal storage? Thanks so much!
    – Rizwan Kassim
    Jul 12, 2010 at 23:42
  • The fileGROUP is not a problem - the FILES are. Trick is that when a file allocates extents, the whole file is blocked. So, for heavy operations having as many FILES as you have LOGICAL processors (not physical) is needed. Including logs and mostly tempdb. Logical processor is a CORE - and with Hyper-Threading enabled every CORE is 2 logical processors. Just look in task manager how many cores you see.
    – TomTom
    Jul 13, 2010 at 5:03
  • are you sure the SAS tray can be split as you want? First time I hear about that. If it woud be a SAN device - ok, but that is not a SAS DAS setup. Every SAS DAS box I ever saw was not splittable.
    – TomTom
    Jul 13, 2010 at 5:05
  • Dell PowerVault 1220 has Split mode that can be split up once (only). So in this case, couldn't/shouldn't we have up to 12 actual files?
    – Rizwan Kassim
    Jul 13, 2010 at 18:31
  • You should. The file number is NOT COUPLED TO THE IOPS SIDE. Basically WITHOUT multiple files, you can only have one insert (sometimes) at one time.
    – TomTom
    Jul 13, 2010 at 18:43
1

I'm going to link to a whitepaper that covers how to answer this exact question. While all of us can give general recommendations, really it comes down to how much to actual I/O utilization to answer the question. The following whitepaper talks about how to measure that and how to plan the disk subsystem accordingly (and test the new hardware to ensure it's up to snuff). In addition, at the end it has a lot of additional links to really digging into this area:

Predeployment I/O Best Practices

Improve this answer
1

this should help also google dmv all stars!

http://technet.microsoft.com/en-us/library/cc966540.aspx

also

Link

Link

the windchill entries cover almost all areas of perf tunning for sql servers

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .