Faster SQL queries with RAM Disk

Faster SQL queries with RAM Disk

We have been having performance issue with our SQL Server database. Specifically, certain kinds of frequently executed stored procedures were causing high IO throughput on SQL Server tempdb files. The server utilizes multiple 15K RPM drives in RAID 10 configuration. Even with this setup, we were seeing near constant 100% utilization of the disk IO system. In previous version of SQL Server it was possible to force tempdb into RAM. Microsoft has since disabled this functionality. By using RAMDisk Plus we were able to essentially move tempdb into RAM. This improved response time on the particular stored procedure at least ten fold. Additionally, this lowered overall IO utilization on the server from around 100% to under 5%, which further improved response times

Preliminary considerations:

    Examine the current size of the tempdb file and the amount of available free memory. If the tempdb file is 100 MB or less, a RAM disk is unlikely to improve performance. If the tempdb file is greater than available physical memory, add more RAM to the system.
    Consider the impact of reallocating system memory to the RAM disk. Sufficient memory resources must be available for SQL and other processes.
    On a production machine, determine a convenient time when SQL Server can be stopped. SQL must be stopped to change the tempdb path, and, possibly, to reduce its memory allocation limit.

Instructions:

    Stop SQL Server
    Add a RAM disk to the system.( You can download from URL: https://sourceforge.net/projects/imdisk-toolkit/ or this link: http://www.ltr-data.se/opencode.html/)
        Select a size large enough to contain the tempdb file.
        Select an appropriate drive letter.
        Select “NTFS” file system, without compression.
        Enable ‘\temp’ folder creation.
        Use the default image location.
        Do not enable “Save at system shutdown” or “Reserve space for full disk
    Configure SQL Server to place the tempdb file on the RAM disk. If necessary, reduce the SQL Server’s system memory allocation limit. (See How to Relocate Microsoft SQL Server’s tempdb files below.)
    Start SQL Server.