| The Straight Story on Virtual Server Chat with Mike Otey |
| Chat live on May 25 |
Robert Schneider introduced 10 ways to optimize >SQL Server from the programming viewpoint in, "10 Easy Tips for Better SQL Server Performance," October 1996. This month, we look at SQL Server performance from the system-tuning side. How many of the 100,000 or more SQL installations out there are truly optimized, and how many are just running out-of-the-box configurations?
Memory
Robert Schneider touched on memory issues, and now here are some guidelines.
Plan on about 50KB per SQL user assigned to the operating system and another
40KB to 50KB per SQL Server user (100KB total), with a baseline of about 16MB if
you are running NT Server 3.51 or 24MB for NT Server 4.0. SMP systems require
more memory allocated to the OS.
Avoid paging activity (disk swapping for virtual memory), because more than a few swapped pages per second have an enormous effect on overall system performance. To prevent paging, balance the memory allocated to SQL Server with that left over for Windows NT until paging activity is minimized (use Perfmon to watch memory counters).
Disk Configuration
Choosing an appropriate layout (RAID level, number of drives, number of
controllers, etc.) can be confusing, but doing so is a huge factor in improving
server performance. Keep your data and logging devices on separate physical
drives. You don't want to mix sequential logging I/O with random data activity,
because the drive heads will always be thrashing around. Use RAID according to
your fault tolerance needs, performance requirements, and available money. (For
more information on RAID levels, see my sidebar, "RAID Performance and NT,"
November 1996).
Stripe sets are better performers than volume sets, and six seems to be the effective maximum number of drives for improving I/O performance. Spread server activity out as much as possible, with separate physical drives (or sets, but not partitions on the same drive) for TempDB data, logs, main databases, NT's pagefile (which you don't want to access anyway), and executables such as the OS, and SQL binaries. If your system has lots of memory, the pagefile and executables can reside on the same drive.
SQL Parameters
To find the options in Table A, you right-click on the registered server you
want to tune, select Configure..., and go to the Configuration tab. You can tune
many other SQL Server parameters (just go to the SQL Enterprise Manager and
bring up the configuration dialog for the server). The parameters listed in
Table A are the major ones that can significantly improve your overall system
performance. You will probably need to further tweak and tune the settings until
you find the best combination for your environment. Use Perfmon to watch system
and SQL counters during normal server operations, and analyze your system usage
to guide your tuning decisions.
| TABLE A: SQL Parameters | ||
| Parameter | Description | Settings |
| Memory | Maximum size, in 2KB units, of system memory available to SQL Server. | Your value depends on transaction mix and user count; a system with 256MB of RAM could assign as much as 180MB to SQL Server. |
| User Connections | Maximum number of simultaneous connections to SQL Server. Uses 37KB of memory per user. | Don't assign more than 10 to 15 more connections than you will use--remember that SQL Server uses about 40KB per user. |
| Max Worker Threads | The number of worker threads that are available for SQL Server processes. Default is 255. | You can bump up this number to at least 512 on a heavily loaded SMP system. |
| SMP Concurrency | Controls the number of threads SQL Server will release to NT for execution. | Default is 0, meaning that SQL holds back and does not use all available CPU resources. Leave it at 0 for a uniprocessor system, but set to -1 on an SMP system that is running only SQL Server. SQL Server will use all available power. |
| Hash Buckets | Number of buckets for hashing pages to buffers in memory. | The default is about 8000, but you can generally double this value. |
| Max Async IO | The number of outstanding asynchronous I/Os that can be issued. | Default can be as low as 8, but if you have several data drives (in a stripe set) or an accelerated hardware RAID controller, you can bump this up to at least 100. |
| Max Lazywrite IO | Tunes the priority of batched asynchronous I/Os that can be issued--comparable to Max Async IO, but specified to the lazywriter. | Just as with Max Async IO, this value can be about 100. |
| Free Buffers | Determines the threshold of free buffers available to the system. | This parameter relates to how much memory your system has. In the configuration I used, with 384MB of system RAM and 180MB assigned to SQL, you can easily give this parameter a value of 10,000. |
| Procedure Cache | Percentage of memory allocated to the procedure cache after SQL Server memory needs are met. | This parameter is more difficult to tune. Engage the Perfmon counters for SQL Server, procedure cache, etc., and watch how much free space is left while your users are running. Tune it down gradually until you find an efficient point (procedure cache does chew up SQL memory), but 5 percent or 10 percent is good on a low- to medium-load server. |
| TempDB in RAM | Size of the TempDB database in RAM. The value 0 causes TempDB to reside on a disk device; the default is master. | This value is related to available memory and dollars. If your system has enough RAM, you can dump all of TempDB into RAM to significantly reduce disk I/O activity and speed up frequently used SQL transactions (TempDB is basically a big cache). You will have to size it accordingly. |
