RAID config options for SQL Server 2008
I'm setting up a new database server.
It's running Win 2K8 Standard (SP2) with MS SQL 2008 (64-bit) Standard (SP1)
The E: drive (where the Database will reside), is a RAID5 array with 10 - 146 GB 15k rpm SAS drives.
My question is regarding the specific RAID settings. The RAID controller allows me to configure these options:
- Read Policy: No Read Ahead / Adaptive Read Ahead / Always Read Ahead
- Write Policy: Write Through / Always Write Back
- IO Policy: Direct IO / Cached IO
- Disk Cache Policy: Enabled / Disabled
Can someone comment on those options? When would it be beneficial to choose one setting as opposed to another?
Thanks!
It's running Win 2K8 Standard (SP2) with MS SQL 2008 (64-bit) Standard (SP1)
The E: drive (where the Database will reside), is a RAID5 array with 10 - 146 GB 15k rpm SAS drives.
My question is regarding the specific RAID settings. The RAID controller allows me to configure these options:
- Read Policy: No Read Ahead / Adaptive Read Ahead / Always Read Ahead
- Write Policy: Write Through / Always Write Back
- IO Policy: Direct IO / Cached IO
- Disk Cache Policy: Enabled / Disabled
Can someone comment on those options? When would it be beneficial to choose one setting as opposed to another?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're welcome! Thanks for the points. Cheers,
D
D
You're welcome! Thanks for the points. Cheers,
D
D
Follow up question if you're game dicconb:
1. on the read ahead option, is adaptive read ahead safe since it will only read ahead if recent data is sequential? or is it possible a small transaction db (OLTP) will still suffer?
2. we have battery backed raid controllers, but no UPS in our racks. the datacenter has generator backed power and tons of customers, so I'm guessing the IT guy before me figured no need to invest in UPS. right now there are no funds to do so. my question is this:
a. even if the raid controller has a battery backup, what keeps all the drives spinning in a power failure situation? we have a dell r510 with 12 sas 15k disks and 2 internal sas 10k drives.
b. what exactly happens that tells the server "power is gone or about to be gone. write all cache to disk and stop accepting more data to the cache."
3. what does enabling disk cache actually do? does it send some command to enable disk cache in windows because there's a disk cache setting for each drive in windows. or is that different?
1. on the read ahead option, is adaptive read ahead safe since it will only read ahead if recent data is sequential? or is it possible a small transaction db (OLTP) will still suffer?
2. we have battery backed raid controllers, but no UPS in our racks. the datacenter has generator backed power and tons of customers, so I'm guessing the IT guy before me figured no need to invest in UPS. right now there are no funds to do so. my question is this:
a. even if the raid controller has a battery backup, what keeps all the drives spinning in a power failure situation? we have a dell r510 with 12 sas 15k disks and 2 internal sas 10k drives.
b. what exactly happens that tells the server "power is gone or about to be gone. write all cache to disk and stop accepting more data to the cache."
3. what does enabling disk cache actually do? does it send some command to enable disk cache in windows because there's a disk cache setting for each drive in windows. or is that different?
Hi Mr Vault,
Since it's just a quick one!
1. The effectiveness of adaptive read ahead algorithms will vary between manufacturers, but it's safe to say that unless the objects in your database are considerably larger than the database page size (6Kb by default in SQL server) you won't get any benefit from read-ahead. Personally I would keep it switched off for this sort of workload.
2.a The answer is that the disks will all stop working if the server loses power, however the write cache on the RAID controller will be kept alive by the battery. When your generator kicks in and power returns to the server, the RAID controller processes the remaining writes in the cache. So although it won't keep your server running, the battery will prevent write operations being lost.
2.b The server has no way of knowing that it's about to lose power. The process of writing all the cache to disk doesn't happen until the server comes back online, so you're relying on the battery to keep the write cache alive until power is restored. If the server is without power for so long that the RAID controller battery runs out, the write cache dies and your data is potentially corrupted. I suggest checking with Dell to see how long your RAID controller battery is designed to last if you're worried there might be extended outages.
3. Disk cache is a small buffer of RAM built into each physical disk drive unit, but it never has battery backup. It performs the same task as the write cache on the RAID controller, so if you already have write cache enabled on the RAID controller you will see very little performance increase by enabling disk cache. As I said in 2a if the server loses power, all of your disks lose power too. If disk cache is enabled there may be writes cached on the individual disks - as the disks don't have their own backup batteries these writes will be lost, potentially causing data loss or corruption. "Disk" caching in Windows is where write operations are stored in your server's RAM before Windows submits them to the RAID controller. This happens in a very different place, but it's just as vulnerable to data corruption in a power failure as your server's RAM is also not protected by a battery.
Hope that explains everything - if you need any more help I suggest opening a fresh question so the whole community can get involved. Feel free to post a link to the new question here so I'll be notified, and I'll take a look at it for you.
Cheers,
D
Since it's just a quick one!
1. The effectiveness of adaptive read ahead algorithms will vary between manufacturers, but it's safe to say that unless the objects in your database are considerably larger than the database page size (6Kb by default in SQL server) you won't get any benefit from read-ahead. Personally I would keep it switched off for this sort of workload.
2.a The answer is that the disks will all stop working if the server loses power, however the write cache on the RAID controller will be kept alive by the battery. When your generator kicks in and power returns to the server, the RAID controller processes the remaining writes in the cache. So although it won't keep your server running, the battery will prevent write operations being lost.
2.b The server has no way of knowing that it's about to lose power. The process of writing all the cache to disk doesn't happen until the server comes back online, so you're relying on the battery to keep the write cache alive until power is restored. If the server is without power for so long that the RAID controller battery runs out, the write cache dies and your data is potentially corrupted. I suggest checking with Dell to see how long your RAID controller battery is designed to last if you're worried there might be extended outages.
3. Disk cache is a small buffer of RAM built into each physical disk drive unit, but it never has battery backup. It performs the same task as the write cache on the RAID controller, so if you already have write cache enabled on the RAID controller you will see very little performance increase by enabling disk cache. As I said in 2a if the server loses power, all of your disks lose power too. If disk cache is enabled there may be writes cached on the individual disks - as the disks don't have their own backup batteries these writes will be lost, potentially causing data loss or corruption. "Disk" caching in Windows is where write operations are stored in your server's RAM before Windows submits them to the RAID controller. This happens in a very different place, but it's just as vulnerable to data corruption in a power failure as your server's RAM is also not protected by a battery.
Hope that explains everything - if you need any more help I suggest opening a fresh question so the whole community can get involved. Feel free to post a link to the new question here so I'll be notified, and I'll take a look at it for you.
Cheers,
D
Thanks dicconb. That was a tremendous help. the only followup question I have is how to determine the database page size. I wouldn't put it past our last admin to have changed this.
ASKER