tempdb file placement

Question

tempdb file placement

2Tall

SSChampion

Points: 14644
More actions
December 12, 2011 at 2:29 am

#391099

Hi. I have a SAN with virtual arrays striped across all spindles. I have presented 2 * RAID 1 LUN's to SQL for tempdb placement. I have placed the log file on RAID 1a and the data file on RAID 1b.
Question: Would you place the data file on RAID 5?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

per BOL:

If data must be quickly recoverable, consider mirroring the transaction log and putting the database on a RAID 5 disk. RAID 5 provides redundancy of all data on the array. This allows for a single disk to fail and be replaced most of the time without system down time. RAID 5 offers lower performance, compared to RAID 0 or RAID 1, but greater reliability and faster recovery.

Divine Flame SSCoach Points: 15943 More actions · Answer 2

2Tall (12/12/2011)
Hi. I have a SAN with virtual arrays striped across all spindles. I have presented 2 * RAID 1 LUN's to SQL for tempdb placement. I have placed the log file on RAID 1a and the data file on RAID 1b.
Question: Would you place the data file on RAID 5?
Thanks,
Phil.

If you are specifically looking for TempDB files placement, RAID 1+0 is recommonded for them as it provides excellent reading/writing speed. RAID 1 is also good (which you have used).

Sujeet Singh

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

Dev (12/12/2011)
per BOL:
If data must be quickly recoverable, consider mirroring the transaction log and putting the database on a RAID 5 disk. RAID 5 provides redundancy of all data on the array. This allows for a single disk to fail and be replaced most of the time without system down time. RAID 5 offers lower performance, compared to RAID 0 or RAID 1, but greater reliability and faster recovery.

Rumor has it that RAID 10 is much better than RAID 5 for performance and offers similar features.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

Jeff Moden (12/12/2011)
Dev (12/12/2011)
per BOL:
If data must be quickly recoverable, consider mirroring the transaction log and putting the database on a RAID 5 disk. RAID 5 provides redundancy of all data on the array. This allows for a single disk to fail and be replaced most of the time without system down time. RAID 5 offers lower performance, compared to RAID 0 or RAID 1, but greater reliability and faster recovery.
Rumor has it that RAID 10 is much better than RAID 5 for performance and offers similar features.

No Doubt on it but RAID10 is costly compared to RAID5.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

2Tall (12/12/2011)
Hi. I have a SAN with virtual arrays striped across all spindles. I have presented 2 * RAID 1 LUN's to SQL for tempdb placement. I have placed the log file on RAID 1a and the data file on RAID 1b.
Question: Would you place the data file on RAID 5?

Given a choice, no. It's not about disk redundancy or data recoverability (being TempDB and seeing that both RAID 1 and 5 allow for single disk failures), it's completely about performance. RAID 5 has about the worst write performance of the standard RAID levels and TempDB gets hammered on most normal implementations.

I'd strongly prefer RAID 10 or RAID 1 if given the choice. RAID 5 would be my last option.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Dev (12/12/2011)
Jeff Moden (12/12/2011)
Dev (12/12/2011)
per BOL:
If data must be quickly recoverable, consider mirroring the transaction log and putting the database on a RAID 5 disk. RAID 5 provides redundancy of all data on the array. This allows for a single disk to fail and be replaced most of the time without system down time. RAID 5 offers lower performance, compared to RAID 0 or RAID 1, but greater reliability and faster recovery.
Rumor has it that RAID 10 is much better than RAID 5 for performance and offers similar features.
No Doubt on it but RAID10 is costly compared to RAID5.

So's RAID 1, but for TempDB RAID 5 is about the worst possible choice (other than RAID 0). For user databases it can be fine, for TempDB, not so much.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 7

GilaMonster (12/12/2011)
Dev (12/12/2011)
Jeff Moden (12/12/2011)
Dev (12/12/2011)
per BOL:
If data must be quickly recoverable, consider mirroring the transaction log and putting the database on a RAID 5 disk. RAID 5 provides redundancy of all data on the array. This allows for a single disk to fail and be replaced most of the time without system down time. RAID 5 offers lower performance, compared to RAID 0 or RAID 1, but greater reliability and faster recovery.
Rumor has it that RAID 10 is much better than RAID 5 for performance and offers similar features.
No Doubt on it but RAID10 is costly compared to RAID5.
So's RAID 1, but for TempDB RAID 5 is about the worst possible choice (other than RAID 0). For user databases it can be fine, for TempDB, not so much.

I didn't recommend RAID5 for TempDB. If you see my first post I highlighted its performance bottlenecks.

2Tall SSChampion Points: 14644 More actions · Answer 8

Thanks to all who have taken time to post. RAID 1 it is then.

Kind Regards,

phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

chetanr.jain Hall of Fame Points: 3938 More actions · Answer 9

Hello Phil,

If you do not have budget constraint, then go for Raid 10

otherwise, you already have other option

2Tall SSChampion Points: 14644 More actions · Answer 10

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper