GilaMonster (9/15/2014)
Gazareth (9/15/2014)
GilaMonster (9/14/2014)
Sometimes advice on a blog still scares me. Not often, but sometimes. Like todayConsider using a RAMDisk for Index filegroups.
Putting non-clustered indexes on a RAMDisk will greatly improve performance
Be absolutely sure that your RAMDisk either backs itself up to disk, or at least saves and loads itself from a real disk on shutdown and startup.
This can significantly increase shutdown/startup/reboot times.
I also recommend doing regular SQL backups of these filegroups.
If the system crashes, recovering the database becomes a lot harder. Which is why backups are very important.
Crikey. I haven't encountered RAM disks since Windows 3.11/Dos 6.
How does RAM measure up to SSD's in £/GB these days?
It's not the cost that is the concern. It's the durability. If the RAMDisk doesn't write back to durable storage before the server shuts down (ie, uncontrolled shutdown), the database, or at least the index filegroups, would have to be restored from backup before the DB would be usable again.
It's a bit wasteful on RAM, holding all your hot index pages twice, as well as any you're not using in RAM anyway. Puzzled as to how this approach will "greatly improve performance" - does this person work for a spectacularly unscrupulous RAM vendor by any chance?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.