tempdb on raid1 isolated or raid10 shared with high write db

Question

---

PROPOSAL 2

8x600gb raid 10 mdf array

4x600gb raid 10 ldf array

PROPOSAL 3

8x600gb raid 10 mdf array

2x600gb raid 1 ldf array

2x600gb raid 1 tempdb mdf/ldf array

READ/WRITE STATS ON CURRENT SAN

db.2

reads= 36m

writes= 341m

db.s

reads= 315m

writes= 343k (as in 343,000)

TEMPDB

reads= 69m

writes= 39m

---

RAID 1 as opposed to current RAID10 is a question mark as our current SAN uses RAID10 for everything (mdf raid 10 , ldf raid 10).

As can be seen above: DB.2 is very write intensive in both its log file and mdf I would presume. DB.s is very read intensive but this maybe left on current san.

I have been looking studies that benchmark using the same test RAID1 and RAID10.

From what i understand based on a score of 1 to 5 (http://www.pcguide.com/ref/hdd/perf/raid/levels/comp-c.html) :

RAID1 reads = 2 out of 5

RAID1 writes = 3 out of 5

RAID10 reads = 4.5 out of 5

RAID10 writes = 3.5 out of 5

If this is to be believed and performance monitor shows the ldf array with no reads but consistent writes then putting the LDF on a RAID1 array should have little performance impact making RAID1 for log files acceptable. Fine, great.

Looking at a RAID1 array for TEMPDB however we can tell by the read write stats (above) that the read write values are heavy on both counts.

Therefore moving tempDB MDF from raid10 array1 and the ldf away from raid10 array2 to merge on a raid 1 single array on a new san then becomes a question of the how well tempDB READS perform on RAID1 (as writes would not be a problem as literature suggest raid1 and raid10 writes speeds are similar).

In short under proposal 3 my TEMPDB reads may suffer but would it be better or worse than contended reads when sharing disk space with a write intensive database like db.2 ???

This is the final question in need to answer.

Now bearing in mind on the current san db.s currently hosts 315m reads which is hosted on the same drives (mdf/ldf array) as db.2 and its relative 341m writes.

In this context relative stats from tempdb 69m reads and 39m writes should not have nearly as big an impact as db.s reads could possibly have. And it is perfectly possible that the drop from raid10 to raid 1 will have an impact on the write performance of tempdb.

So again i refer to this article regarding the isolation of tempdb. http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx

Q: How can i gauge of the drop from RAID10 to RAID1 but isolating tempDB on its own array is better than sharing a RAID10 array with a database that has 315m writes but little reads ? (assuming db.s remains on current san)

SIMPLY:

tempdb 69m reads on raid1 isolated

versus

tempdb 69m reads + db.2 36m reads on a shared raid 10 array.

Thank you.

Answer 1

I found these articles helpful:

• Why RAID10 (0+1) superiority is a myth (specifically versus RAID1).
• And this is an additional guide on raid level comparison.

Further reading suggests that the benefits of using RAID1 for log files (assuming isolated from data files) is lost when more than 1 log file exists on that RAID1 array. This is due to the sequential nature of the transaction log writes to disk. The sequential write benefit is lost when multiple log files are accesses on the same RAID1 array due to the random nature of access on a spinning disk. This would suggest that RAID10 is the better choice in a multiple DB environment unless you have the disks to isolate each log file.

These stats below sold me on proposal 3, isolating tempDB on RAID1 by stealing 2 disks from LOG array moving log array from RAID10 to RAID1. Basing much of this on RAID1s ability to maintain good WRITE speed.

TEMPDB is clearly under more stress than I have realised.

These table rankings ring true for snapshot values during normal operation (not just the accumulated totals) as we do have intensive out of hour routines.

TOTAL IO:

db.tempdb.mdf = 144,747,290,352

db.2.mdf =         100,482,243,080

db.2.ldf =           2,571,065,773

db.s.mdf =         1,702,508,040

db.s.ldf =           223,032,162

TOTAL READS:

DB.2.mdf =         84,851,614,280.00

db.tempdb.mdf = 72,271,813,552.00

db.s.mdf =         1,691,504,864.00

db.2.LDF=          93,822,304.00

TOTAL WRITES:

db.tempdb.mdf = 72,475,476,800

db.2.mdf =         15,630,628,800

db.2.ldf =           2,477,243,469

db.tempdb.ldf =   222,946,079

One possible concern maybe the additional of the tempdb ldf and mdf on the same raid1 array but if this is a problem tempdb.ldf can be moved to the log array.

These are helpful links that explain TEMPDB usage, what it does and how it may effect my apps:

• http://bradmcgehee.com/wp-content/uploads/presentations/Optimizing%20tempdb%20Performance_SQLCONN.pdf
• http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx