Six Easy Steps to Getting Its Best
Microsoft SQL Server version 6.0 has a more powerful database engine and supports more Windows users than its previous versions. However, figuring out how to tune it to get the best performance possible is a bit of a mystery. Here are some tips that will help you solve that puzzle.
- Spend some money on RAM.
- Set the data and procedure caches.
- Use stored procedures.
- Look at the application's requirements for SQL Server's temporary database.
- Select an appropriate data-protection scheme.
- Analyze the application to determine its primary type of processing.
You should keep in mind that no amount of tuning makes up for poor application and database design. The best way to avoid problems is to build a logical data model first and then create your applications. If you're careful, you can do these concurrently as long as the database-design team is actively involved in the early stages of application development. Once the application and logical data models are complete, you can generate the physical database from the logical data model. You can do it manually, but we recommend using one of the available tools that help automate this process. Make sure you pick a tool that provides strong support for physical database designs, such as Erwin from Logic Works.
1) Buy More RAM
Adding more RAM is one of the best ways to improve SQL Server performance. More memory enables SQL Server to cache more data and reduce the amount of physical disk I/O necessary. Disk access is the slowest part of your server and should be minimized. A minimal installation should have 64MB of RAM or more, although the DBMS can function quite well with as little as 32MB. Remember, the operating system requires that part of RAM be available to the server.
The system-overhead requirement for a Windows NT-based system can grow as you enlarge the SQL Server configuration. As the parameters are raised, NT needs more RAM for such things as additional threads, page tables, and so on. The table above contains a guideline to help you determine how much memory to allocate to SQL Server, depending on how much physical memory is on the system.
Due to the expense of additional memory, you should carefully analyze your system requirements and perform user- and data-volume simulations. The results of these tests will determine how much RAM you need for SQL Server to optimize both the application speed and the server hardware cost.
Hardware limitations may restrict your ability to expand memory. For example, consider a system with four memory-expansion slots, each of which allows, at most, a 16MB single integrated memory module (SIMM). This system can be expanded to a maximum of 64MB of RAM.
While such limitations are normally considered during system design before the hardware is purchased, it may sneak up on you if, for example, your customer base suddenly increases.
2) Data and Procedure Cache
There are two types of cache memory for use with SQL Server: data and procedure. The procedure cache holds the optimized query plans for the most recently executed stored procedures, including triggers and system-stored procedures. The data cache contains pages (2KB blocks of data) from the most recently accessed objects--mostly tables and indexes.
As the size of the data cache increases, the speed of data retrieval also increases, especially on subsequent retrievals. Cache is allocated to data and procedures depending on the application. Applications that use triggers need more procedure cache--triggers are treated as stored procedures for cache purposes. The default for SQL Server is 30% of cache for procedures and 70% for data. However, this may not be sufficient for your application.
To estimate the size of the procedure cache, you should run the system command dbcc memusage to determine the size of your largest plan. Multiply this size by the maximum number of concurrent users you have and then add 25% to determine the optimal size for your procedure cache. For example, if your largest plan uses 300KB and the application has a maximum of 30 concurrent users, then the procedure cache should be 11.25MB in size:
30 x 300KB x 125% = 11.25MB.
To illustrate the advantages of caching, let's look at an example. A mortgage-loan origination application can potentially retrieve several hundred rows of loan-pricing data using a complex SQL statement with multiple table joins. The initial retrieval takes 17 seconds. Sub-sequent retrievals for other customers take less than 3 seconds each since the application data is already in the cache. The key is that the loan-pricing data is basically fixed, even though multiple customers are being served.
The default installation size for SQL Server memory, including all cache plus kernel and server structures, is 8MB for a system with 32MB of RAM or less (16MB for systems with more than 32MB of RAM). If you have 64MB available, you should increase the cache to at least 40MB. Larger is better in this case because after the cache fills up, it is flushed on a FIFO basis. Therefore, you want to have enough cache to handle multiple SQL statements. If the cache is too small, it has to empty every time a new SQL statement executes, and the benefit is lost.
While the system is under a typical load, use the SQL Performance Monitor to check the SQL Server cache-hit ratio. If it's over 90%, adding more memory won't help. Additional memory is used mainly for SQL Server data cache to increase the hit ratio. In this case, the hit ratio is already high, and the maximum improvement possible is quite small. If the hit ratio is less than 90%, adding more memory may improve it and, thus, performance.
When you're searching for memory bottlenecks, you should also look at SQL Server I/O Lazy Writes per second. This counter measures how often the Lazy Writer process flushes dirty pages to disk to make room in the cache. The process ensures that the number of free data buffers stays higher than a predefined threshold. More memory may improve performance by reducing unnecessary stress on your I/O subsystem.
The kernel and server structures consist of configurable options: user connections (37KB per user connection), open databases (1KB per open database), open objects (70 bytes per object), and locks (32 bytes per lock). As these options increase in number, the caches suffer. If 10 user connections are added at 37KB each, the memory available for the caches is reduced by 370KB.
Previous
