Ramdisk Plus SQL-Performance

Microsoft SQL Server performance can be limited by network, processor or memory resources, but the most common bottleneck is disk input/output.

Although SQL Server contains a built-in cache, processor and memory resources are often underutilized, meaning that the system can yield still more performance.

Even after optimizing database, index, query, and application design, SQL Server may under-perform.

Disk I/O bound servers with significantly less than 99% processor utilization may improve their performance through the use of a block-level cache and/or RAM disk. A RAM disk should be used where access to temporary data is constraining system performance, such as frequently occurs with the tempdb file.

One or more block-level caches should be used in the paths of files limiting system performance which are larger than available physical memory. The system pagefile and the table files are ideal candidates for block-level caches.

Accelerate tempdb access
Where tempdb access limits server performance, a RAM disk provides the highest-performing storage medium available. Because the tempdb file's contents are truly of a temporary nature, it is an ideal candidate for a RAM disk. If the file is not present when SQL Server starts, SQL Server creates the file. It is not necessary to save its contents at system shutdown.

RamDisk and RamDisk Plus provide up to 63 GB of virtual disk storage on 32-bit platforms, and up to 2 TB on 64-bit platforms.

The amount of system memory dedicated to the RAM disk depends on the size of the tempdb files, and may vary between 15 and 80% of physical memory resources.

By placing tempdb files on a RAM disk, production servers have attained accelerations in SQL Server throughput from 15 to over 600%.

RAM disk cluster resource
RamDisk and RamDisk Plus support RAM disks for both non-cluster and cluster applications. Cluster support is provided by integrating the local RAM disk into the cluster environment as a cluster resource. Access to the RAM disk cluster resource is realized by mounting the RAM disk on a specified folder in an NTFS volume present on a shared disk.

RAM disks created by RamDisk and RamDisk Plus are available very early in the system boot cycle, eliminating synchronization issues with auto-start services or applications.

Accelerate pagefile and table file access
Access to SQL table files as well as to the system pagefile can also limit server performance. When there are sufficient memory and processor resources available, a block-level cache can greatly mitigate this problem.

Because the pagefile contents are temporary in nature, write-caching can be enabled without putting data at risk.

In the case of table files, to eliminate added risk of data loss write-caching should be disabled. If greater write performance is desired, and some risk of data loss is acceptable, write-caching can be enabled.