Microsoft SQL Server Performance Solutions

On this page:

The tempdb Bottleneck
The Pagefile Bottleneck
The Table File Bottleneck
How to Implement the tempdb Solution on a Machine that Is Not Part of a Cluster
How to Implement the tempdb Solution on a Machine that Is Part of a Cluster
How to Implement the Pagefile Solution (non-cluster machines only)
How to Implement the Table File Solution (for non-cluster machines only)

   

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

Tempdb Solution Whitepaper

 

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

 

Go to topRamDisk 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.


Go to topHow to Implement the tempdb Solution on a Machine that Is Not Part of a Cluster
  Preliminary considerations:
  Examine the 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:
  1. Install the latest version of RamDisk or RamDisk Plus.
  2. Stop SQL Server.
  3. Add a RAM disk to the system.
a. Select a size large enough to contain the tempdb file.
b. Select an appropriate drive letter.
c. Select "NTFS" file system, without compression.
d. Enable '\temp' folder creation.
With RamDisk Plus:
e. Use the default image location.
f. Do not enable "Save at system shutdown" or "Reserve space for full disk".
  4. 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.)
  5. Start SQL Server.

Go to topHow to Implement the tempdb Solution on a Machine that Is Part of a Cluster
  Preliminary considerations:
  Examine the 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.
  Determine which unassigned drive letter to reserve for the RAM disk. The drive letter must be available on all cluster nodes.
  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 configure cluster dependencies, change the tempdb path, and, possibly, to reduce its memory allocation limit.
  Instructions:
  1. Install RamDisk or RamDisk Plus (version 11.1 or greater) on each cluster node. To install cluster support, the cluster service must be running on at least one node.
  2. Stop SQL Server.
  3. Add a local RAM disk to each cluster node. (Use the same parameters on each node.)
a. Select a size large enough to contain the tempdb file.
b. Select an appropriate drive letter.
c. Select "NTFS" file system, without compression.
With RamDisk Plus:
d. Use the default image location.
e. Do not enable "Save at system shutdown" or "Reserve space for full disk".
  4. Create and test a RAM Disk cluster resource. (See How to Implement a RAM Disk in a Cluster - 4  and 5.)
  5. Configure SQL Server to work with the RAM Disk cluster resource. If necessary, reduce the SQL Server's system memory allocation limit. (See How to Relocate Microsoft SQL Server's tempdb files.)
  6. Start SQL Server.
  7. Test SQL Server with its RAM Disk cluster resource. (See How to Implement a RAM Disk in a Cluster - 8.)

Go to topHow to Implement the Pagefile Solution (non-cluster machines only)
  Preliminary considerations:
  If not already implemented, the pagefile should be hosted by its own storage volume (or storage partition). If possible, the volume should be located on a dedicated physical storage device. Using disk partitioning software, create a new storage volume on the current storage device, or, add a hard disk (or array), to host the pagefile.
  Consider the impact of reallocating system memory to the cache. 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. It may be necessary to stop SQL to reallocate memory resources.
  Instructions:
  1. Install the latest version of SuperCache II. Restart the machine.
  2. If necessary, stop SQL Server to free memory resources and to reduce the memory allocation limit. Then, restart SQL.
  3. Enable caching on the storage volume hosting the pagefile.
a. Select 32 KB cache page size.
b. Select a cache size according to available memory and the pagefile size. The cache size can be increased dynamically at any time.
c. Select 1 read-ahead page.
d. Select 15 seconds lazywrite latency.
e. Do not select MFU filtering.
  4. Monitor SQL performance. Increasing the cache size may improve performance. Increases may continue until processes become memory starved, at which point the cache size should be reset to the value of peak performance.

Go to topHow to Implement the Table File Solution (non-cluster machines only)
  Preliminary considerations:
  If not already implemented, table files should be hosted on their own storage volume (or storage partition). If possible, the volume should be located on a dedicated physical storage device. Using disk partitioning software, create a new storage volume on the current storage device, or, add a hard disk (or array), to host the table files.
  Consider the impact of reallocating system memory to the cache. 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. It may be necessary to stop SQL to reallocate memory resources.
  Instructions:
  1. Install the latest version of SuperCache II. Restart the machine.
  2. If necessary, stop SQL Server to free memory resources and to reduce the memory allocation limit. Then, restart SQL.
  3. Enable caching on the storage volume hosting the table files.
a. Select 32 KB cache page size.
b. Select a cache size according to available memory and the table files' aggregate size. The cache size can be increased dynamically at any time.
c. Select 1 read-ahead page.
d. Select 0 seconds lazywrite latency for no-risk write-through mode. If some risk of data loss is acceptable, set lazywrite latency to a non-zero value. Write performance and risk of data loss increase with this value.
e. Do not select MFU filtering.
  4. Monitor SQL performance. Increasing the cache size may improve performance. Increases may continue until processes become memory starved, at which point the cache size should be reset to the value of peak performance.

    Go to topSee Also:
How to Relocate Microsoft SQL Server's tempdb Files

Thin-Client Server Solutions
Messaging and Collaboration Server Solutions
Business Intelligence Server Solutions
Web Server Solutions
Server Cluster Solutions


Citrix   HP   IBM

  Home - Site Map - Contact Us - Legal Notices - Privacy Policy  

SuperSpeed LLC is the owner of the following U.S. Patents 5577226, 5606681, 5918244, 6370615, 6629201, 6651136, 7017013, 7039767, 7111129, 7475186, 7594068, 7631139, 7886099 and other patents are pending.

Copyright © 1996-2014 SuperSpeed LLC. All rights reserved.