Assuming constant memory (32gb) and CPU (4), 2 x disk arrays, I have the following disks
- 2 x 150 (10k)
- 6 x 150 (15k)
They are all local disks.
- My DB is 350gb and set to default 10% growth
- My OS & SQL Server are Server 2k8R2 (C: drive OS + page + applications = 55Gb)
- Log requirements are about 70gb and set to default 10% growth and is routinely truncated
- My TempDb is about 12gb currently and set to default 10% growth
My problem is that I’m trying to understand where to best put the TempDB and OS and the Log. My experience is limited in optimal configuration of these two
This is not an online transactional system. It has heavy data write (new data + indexes rebuild/reorg) then heavy data read (I'm estimating at about 50/50) processing for about 13 hours, and then just quiet.
My understanding is that the TEMPDB is heavily used during normal processing compared to the log.
My idea is the following
- 2 x 150g (15k) Raid 1 = 150g for OS + TempDB
- 2 x 150g (10k) Raid 1 = 150g for LOG (note slower disks here)
- 4 x 150g (15k) Raid 5 = 150g for data
Does this sound like a good idea? I could then swap the Log + TempDB if needed.
Am I breaking a cardinal rules like never put TempDB on OS disk due to paging concerns, or perhaps never put log on slower disk than data?
We also have a SSAS on the system and the end users access only the Cube. The 50% read above is based on the time it takes to process SSAS database.