The 90:10 Rule for SQL Server Performance

Tuning Microsoft SQL Server 2000 and SQL Server 7.0 to boost performance can be hard work, but in most cases, you can get major benefits from expending just a little effort. It's the old 90:10 rule: You can get 90 percent improvement with only 10 percent effort, but realizing that final 10 percent performance gain will take 90 percent of your tuning efforts.

This 90:10 rule doesn't apply to all database products or even to earlier versions of SQL Server. To achieve reasonable performance, some products require you to set dozens—or even hundreds—of server-level parameters and numerous other options in your SQL code. By contrast, Microsoft made SQL Server 2000 and SQL Server 7.0 self-tuning, and these products give you reasonable performance right out of the box.

But to attain a higher level of performance than what you get on average out of the box, you only need to give SQL Server a little attention. Applying the following 10 tips will help you achieve that initial 90 percent performance gain. If you find that you need more information as you put these tips into practice, check out the resources in the sidebar "Knowing Is 9/10 of the Battle."

Tip 1: Don't skimp on hardware
I don't usually recommend hardware improvements first on a list of tuning tips because I don't want to mislead you into thinking you can simply throw hardware at performance problems. However, in the case of SQL Server performance, consider hardware first.

If you already use a decent system, upgrading your hardware will rarely bring more than a 10 percent overall performance improvement. However, if you're running a SQL Server based application on a server to which several hundred users simultaneously connect, and the server has only one physical hard disk and the absolute minimum of 64MB of RAM, then simply increasing RAM to the recommended minimum of 128MB will bring a more drastic performance improvement.

Beyond 128MB of RAM, you ideally need another 10MB for each 10 simultaneous user connections, and you need enough additional RAM to store all user data, system data, and indexes. I recommend choosing a disk configuration with which you store user data (.mdf and .ndf) files and log (.ldf) files on separate physical disks that have separate controllers. Store the user data files on the best RAID system your budget allows. For processing power, purchase two of the fastest processors you can afford. These configurations are the very skimpiest you should consider.

Tip 2: Don't overconfigure
Microsoft designed both SQL Server 2000 and SQL Server 7.0 to self-tune. For example, the SQL Server engine can determine optimal values for memory utilization, number of locks to allow, and checkpoint frequency.

Only consider changing the out-of-the-box configuration options that don't affect performance. These nonperformance configurations include user options and the two-digit year cutoff option (the user options bitmap indicates which options you want to enable for each user connection; the two-digit year cutoff option controls how SQL Server interprets a two-digit year value).

Microsoft made the performance-related options available for configuration in the rare case in which you need to make an adjustment to them. However, your best bet is to let SQL Server's configuration options continue self-tuning.

If you use SQL Server 7.0, the max async I/O parameter might be an exception to this recommendation. You configure max async I/O for the level of sophistication and number of controllers in your I/O system. The max async I/O value determines the maximum number of outstanding asynchronous I/O requests that the server can issue to any file. If a database spans multiple files, the value applies to each file.

The max async I/O default setting of 32—only 32 reads and 32 writes can be outstanding per file—is an optimum value for many systems. Read SQL Server Books Online (BOL) to determine whether you need to change the default value for your system. SQL Server 2000 doesn't include the max asynch I/O parameter but can determine the optimum value internally.

Tip 3: Take time for design
In this age of rapid application development (RAD), the popular goal of quick initial project implementation might tempt you to sacrifice high-quality relational database design. If you yield, performance suffers. Poor design is a difficult problem to fix because fixing design frequently requires changing much of the written and tested code.

For a well-designed database, start by creating a normalized model in at least the third normal form. This practice minimizes redundancy and reduces overall data volumes. You can then systematically denormalize design, documenting each break from the normalized form.

A denormalized design is different from an unnormalized design. A denormalized design introduces redundancy for specific performance reasons. For example, if you consistently need to look up a customer's outstanding orders by the customer's name, and one table stores the customer ID number and the customer's name whereas another table stores the customer ID number and the order status, SQL Server needs to perform a join of these tables to retrieve names of customers with outstanding orders. In some cases, joins can be expensive. A denormalized model might add the customer name to the table that contains the order status and customer ID number and eliminate the need for a join.

Tip 4: Create useful indexes
Without the help of useful indexes, SQL Server must search every row in a table to find the data you need. If you're grouping or joining data from multiple tables, SQL Server might even need to make multiple passes through the tables to get your results. Because reading from disk is one of the most expensive actions SQL Server performs, reducing the number of required disk reads is one of the most effective tuning techniques you can employ.

If you create useful indexes, you can see performance improve by orders of magnitude, rather than by a few percentage points. For example, without an index, SQL Server might need to read all 10,000 pages in a table. An index would bring a 100,000 percent improvement in the number of pages SQL Server needs to read if the index reduces the number of necessary page-reads to 10.

A thorough knowledge of index architecture and the SQL Server query optimizer will help you create the best possible indexes, but until you reach that knowledge level, you can use SQL Server's Index Tuning Wizard to create useful indexes. Open this tool from SQL Server Enterprise Manager by clicking the Wizards button in the toolbar and looking under Management Wizards.

Before the wizard makes a set of index recommendations, it needs to know how you'll be accessing data. The best way to collect this information is with SQL Server Profiler. During a few hours of peak usage, capture the SQL command batches that your client applications send to your SQL server. You can use this information to tell the wizard how client applications access a table.

If your application isn't in production yet, you can provide the Index Tuning Wizard with a set of representative SQL statements that access the tables you want to tune. You can use Query Analyzer to create this set. Simply input the names of the stored procedures you'll be running, and make your best guesses about other ad hoc SQL statements that users will run.

Tip 5: Use SQL effectively
SQL is a set processing language, not a row-at-a-time processing language. T-SQL, Microsoft's dialect of the SQL language, can use server cursors to access one row at a time; however, most solutions that use server cursors will be orders of magnitude slower than solutions that use SELECT statements and UPDATE statements to perform the equivalent task. The more SQL programming experience you have, the more comfortable you'll be using the SQL language most effectively. Taking advantage of features such as subqueries, derived tables, and CASE expressions to manipulate sets of rows will speed your solutions and help you maximize SQL Server performance.

For example, suppose a table contains a row for each product in your inventory, and another table contains a row for the quantity of each sale of that product. You want to denormalize the database and store the sum of each product's sales in the product inventory table. To generate these sums, you could use a cursor and step through the product table one row at a time. For each row, you could then find all matching rows in the sales table, add up the quantity values, and use that sum to update the product inventory table. In this example, using server cursors to collect figures for total sales is possible but unbelievably inefficient.

   Previous  [1]  2  Next