Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
More Easy SQL Server Performance Tips
System-tuning performance tips for SQL Server.
Joel Sloss
December 31, 1996
4 Min Read
Robert Schneider introduced 10 ways to optimize >SQL Server from theprogramming viewpoint in, "10 Easy Tips for Better SQL Server Performance,"October 1996. This month, we look at SQL Server performance from thesystem-tuning side. How many of the 100,000 or more SQL installations out thereare truly optimized, and how many are just running out-of-the-boxconfigurations?
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 another40KB to 50KB per SQL Server user (100KB total), with a baseline of about 16MB ifyou are running NT Server 3.51 or 24MB for NT Server 4.0. SMP systems requiremore memory allocated to the OS.
Avoid paging activity (disk swapping for virtual memory), because more thana few swapped pages per second have an enormous effect on overall systemperformance. To prevent paging, balance the memory allocated to SQL Server withthat left over for Windows NT until paging activity is minimized (use Perfmon towatch memory counters).
Disk Configuration
Choosing an appropriate layout (RAID level, number of drives, number ofcontrollers, etc.) can be confusing, but doing so is a huge factor in improvingserver performance. Keep your data and logging devices on separate physicaldrives. 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 toyour fault tolerance needs, performance requirements, and available money. (Formore 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 theeffective maximum number of drives for improving I/O performance. Spread serveractivity out as much as possible, with separate physical drives (or sets, butnot partitions on the same drive) for TempDB data, logs, main databases, NT'spagefile (which you don't want to access anyway), and executables such as theOS, and SQL binaries. If your system has lots of memory, the pagefile andexecutables can reside on the same drive.
SQL Parameters
To find the options in Table A, you right-click on the registered server youwant to tune, select Configure..., and go to the Configuration tab. You can tunemany other SQL Server parameters (just go to the SQL Enterprise Manager andbring up the configuration dialog for the server). The parameters listed inTable A are the major ones that can significantly improve your overall systemperformance. You will probably need to further tweak and tune the settings untilyou find the best combination for your environment. Use Perfmon to watch systemand SQL counters during normal server operations, and analyze your system usageto guide your tuning decisions.
TABLE A: SQL Parameters
Parameter | Description | Settings |
|---|---|---|
Memory | Maximum size, in 2KB units, of system memoryavailable to SQL Server. | Your value depends on transaction mix and user count; a system with 256MBof RAM could assign as much as 180MB to SQL Server. |
User Connections | Maximum number of simultaneous connections to SQL Server. Uses 37KB ofmemory per user. | Don't assign more than 10 to 15 more connections than you willuse--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 forexecution. | Default is 0, meaning that SQL holds back and does not use all availableCPU resources. Leave it at 0 for a uniprocessor system, but set to -1 on anSMP system that is running only SQL Server. SQL Server will use allavailable 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 astripe set) or an accelerated hardware RAID controller, you can bump this up toat least 100. |
Max Lazywrite IO | Tunes the priority of batched asynchronous I/Os that can beissued--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 theconfiguration I used, with 384MB of system RAM and 180MB assigned to SQL, youcan easily give this parameter a value of 10,000. |
Procedure Cache | Percentage of memory allocated to the procedure cache after SQL Servermemory needs are met. | This parameter is more difficult to tune. Engage the Perfmon counters forSQL Server, procedure cache, etc., and watch how much free space is left whileyour users are running. Tune it down gradually until you find an efficientpoint (procedure cache does chew up SQL memory), but 5 percent or 10 percentis good on a low- to medium-load server. |
TempDB in RAM | Size of the TempDB database in RAM. The value 0 causes TempDB to resideon a disk device; the default is master. | This value is related to available memory and dollars. If your system hasenough RAM, you can dump all of TempDB into RAM to significantly reduce diskI/O activity and speed up frequently used SQL transactions (TempDB isbasically a big cache). You will have to size it accordingly. |
About the Author
You May Also Like
.jpg?width=700&auto=webp&quality=80&disable=upscale)
