January 23, 2012 at 12:55 pm
I'm having trouble understanding what would happen under a specific scenario.
I've got 2 SSD drives (plenty of size) for the TEMPDB to exist on.
Which scenario and what repercussions would i have under the following 2 scenarios?
a) 2 SSD drives mirrored, using 4 tempdb files
b) 2 SSD drives, not mirrored but holding the 4 tempdb files
seems: (are these assumptions correct?)
a) is safer for data corruption issues related to failure
b) would be faster for tempdb usage.
failure of drive repercussions? (if this isn't accurate, please fill in...)
a) exchange drive, reboot computer. no issue...
b) reboot computer (since tempdb is recreated upon boot, and would ignore first drive failure and use the available tempdb? or would it fail and require some extra work?
Thanks!
January 23, 2012 at 12:59 pm
I would probably go with option 2. If one fails, exchange the drive - reboot.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 23, 2012 at 1:02 pm
If scenario B is a striped volume of the 2 SSD drives, SQL would fail to recover. The volume(both drives) containing tempdb is no longer valid. You'd have to start SQL without recovering the databases and move tempdb to a valid location and restart again.
January 23, 2012 at 1:55 pm
would either be stand alone or mirrored. Yes, I had considered that for speed as well, but decided against for that very reason. Thanks!
January 23, 2012 at 2:10 pm
I'd choose #1, but I'm conservative, and tempdb gets written to a lot, so I'd be worried about failure.
With the four files, one filegroup? Depending on your workload, you might not see a performance increase. Also, depending on your controller/IO channel, you might not get much of a boost.
January 23, 2012 at 2:36 pm
Steve Jones - SSC Editor (1/23/2012)
I'd choose #1, but I'm conservative, and tempdb gets written to a lot, so I'd be worried about failure.With the four files, one filegroup? Depending on your workload, you might not see a performance increase. Also, depending on your controller/IO channel, you might not get much of a boost.
oh baby, you had me at conservative.
it's more about isolation to it's OWN area, than about serious performance increase.
i really want the TempDB in it's own area, and not competing for disc resources, so that in itself will be a performance enhancer.
My Network Engineer, Server Master, has seen a minor surplus in budget and asked me if i was interested.... :w00t: uhhh... OF COURSE, i said. 😀
the 4 files only see real action when the morning daily loads happen, and the index building that has to occur on the truncate, then load of the data files. during that short period of time (set to before the datasets are generally used, is really the main stress on the system)
Thanks, I'll be raid mirroring them.
January 23, 2012 at 2:58 pm
Thanks for the note, and I'd love to know how it works out. If you can grab some metrics now, and then some later, I've love to see a writeup (as would others) on what this did for you.
I'd get % of reads/writes, the IOPS now, maybe load times, and then get them after as well.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply