Another Overlooked Windows Setting for the DBA

March 20, 2009 · Posted in Uncategorized 

*Warning* Only use if your array controller has a battery backed cache. *Warning*

The settings are “Enable write caching on the disk” and “Enable advanced performance”. You can access these through device manager on the properties of the disk. These settings mostly apply to direct attached storage and are unavailable for most enterprise SAN lun’s that I have seen.

While we are at it, if your RAID controller cache has a readwrite ratio, it is a good idea to set it to 0% read100% write as long as you do not have a memory bottleneck. SQL uses RAM as its read buffer.

So is this a silver bullet for performance? Definitely not especially if you are not hitting a disk write bottleneck. However, every little bit helps and if it knocks 5-10% off of your 3 hour long full backup to disk, that is a win!

Happy Friday!

This content is published under the Attribution-Share Alike 3.0 Unported license.

Tags: , ,

Comments

  • Do you know of a way to script this?
  • Not presently but I was planning on writing a script that will generate a report of which servers this needs to fixed on. As far as changing, it is probably a reg key but once for each disk but it doesn't need to be turned on for all disks.

    It is on my list but several things just got bumped up in priority ahead of it...
  • Matt
    Note: "Enable Enhanced Performance" doesn't do anything but re-introduce an old bug:
    http://207.46.16.252/en-us/magazine/2007.04.win...

    It's likely that SQL Server 2000 and up do not rely on this bug at all.
  • It seems based on this and another thread I found http://forums.storagereview.net/index.php?showt... that we should not enable the Advanced Performance as it will remove the flush cache option to improve speed, but during power failure override the option you really want on a database server? Does anyone have a test box that they can power off to do a test?
  • Chuck - That is why I have the bold warning, do not do this until you have a battery backed cache. Most enterprise level servers(HP for sure) will send traps if the battery juice is running out or the battery backing is unavailable for some reason.

    Also, if you do not have a write bottleneck then you may still want to leave it off.
  • Understood, but if you have the cache and want to use it, it seems you have to leave the enable adv performance UNchecked and enable write caching checked based on references sited by Matt and myself.
blog comments powered by Disqus