| |
|
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
|
|
 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.
|
How
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. |
| |
|
Determine which NTFS volume on a cluster-shared physical disk will host
the folder that will serve as a mount point for the RAM disk, and the
folder's name. |
| |
|
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 8.0.3 or greater) on each cluster
node. To install successfully, the cluster service must be running on
the 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 a folder on a shared cluster disk to serve as the mount point
for the local RAM disk. |
| |
5. |
Create a RAM Disk cluster resource.
| a. |
On the first page of the "New Resource"
wizard, enter the name of the new RAM Disk resource and select the
cluster group to which it will belong. |
| b. |
Navigate through the wizard, setting the appropriate dependencies,
etc. Be sure to set the RAM Disk resource as dependent on the physical
disk resource which contains the folder to be used as a mount point. |
| c. |
On the last wizard page "RAM Disk Cluster Resource Parameters",
enter the fully-qualified path to the folder previously created
in step 4. The path must include a trailing backslash (\). |
|
| |
6. |
Test the RAM Disk cluster resource. (If necessary, create a test group
and place the physical disk resource and RAM Disk resource in the group.
Once complete, place the resources in their original group.)
| a. |
Bring the RAM Disk cluster resource online. |
| b. |
Move the group containing the RAM Disk cluster resource to another
node. |
| c. |
If both these actions are successful, continue with installation.
Otherwise, troubleshoot the problem. |
|
| |
7. |
Configure SQL Server to work with the RAM Disk cluster resource.
| a. |
Configure the SQL Server cluster application to be
dependent on the RAM Disk cluster resource. |
| b. |
Configure SQL Server to place its temporary data in the folder
which serves as the RAM disk's mount point. |
| c. |
If necessary, reduce the SQL Server's system memory allocation
limit.. |
|
| |
8. |
Start SQL Server. |
| |
9. |
Test SQL Server with its RAM Disk cluster resource.
| a. |
Move the SQL group, while SQL is running, to another
node. |
| b. |
Restart a node that is available to host SQL. After restarting,
move SQL, while running, to that node. |
| c. |
If both these actions are successful, the installation is complete.
Otherwise, troubleshoot the problem. |
|
How
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. |
How
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. |
|
| |
|
See
Also:
Thin-Client Server Solutions
Messaging
and Collaboration Server Solutions
Business
Intelligence Server Solutions
Web Server Solutions
Server
Cluster Solutions |
U.S. Patents Nos. 5577226, 5606681, 5918244,
6370615, 6629201, 6651136, 7017013, 7039767, 7111129 and other worldwide patents
pending.
Copyright © 1996-2008 SuperSpeed Software,
Inc. All rights reserved.
