INF: Optimizing Microsoft SQL Server Performance

• Microsoft SQL Server, version 4.2 for OS/2
• Microsoft SQL Server, versions 4.2, 6.0, and 6.5

SUMMARY

To most effectively optimize Microsoft SQL Server performance, you must identify the areas that will yield the largest performance increases over the widest variety of situations, and focus analysis on these areas. Otherwise, you may expend significant time and effort on topics that may not yield sizable improvements.

For the most part, the following information does not address the performance issues stemming from multi-user concurrency. This is a separate, complex topic that is covered in the document 'Maximizing Database Consistency and Concurrency,' which can be found in the SQL Server version 4.2x "Programmer's Reference for C," Appendix E, and also in other Knowledge Base articles. It is not in the version 6.0 documentation, but can be found on the MSDN (Microsoft Developer Network) CD-ROM under that title.

Rather than a theoretical discussion, this article focuses primarily on areas that years of experience by the Microsoft SQL Server Support team has shown to be of practical value in real world situations.

Experience shows that the greatest benefit in SQL Server performance can be gained from the general areas of logical database design, index design, query design, and application design. Conversely, the biggest performance problems are often caused by deficiencies in these same areas. If you are concerned with performance, you should concentrate on these areas first, because very large performance improvements can often be achieved with a relatively small time investment.

While other system-level performance issues, such as memory, cache buffers, hardware, and so forth, are certainly candidates for study, experience shows that the performance gain from these areas is often incremental. SQL Server manages available hardware resources for the most part automatically, reducing the need (and thus, the benefit) of extensive system-level hand tuning.

Microsoft SQL Server 6.0 opens up new opportunities for platform-layer performance improvements, with large amounts of memory, symmetrical multiprocessing, parallel data scan, optimizer enhancements, and disk striping. However, as large as these improvements are, they are finite in scope. The fastest machine can be bogged down with inefficient queries or a poorly designed application. Thus, even with the additional performance headroom SQL Server 6.0 allows, it is of paramount importance to optimize the database, index, query, and application design.

Most performance problems cannot be successfully resolved solely with a server-side focus. The server is essentially a "puppet" of the client, which controls what queries are sent, and thereby what locks are obtained and released. Although some tuning is possible on the server side, successful resolution of performance problems will usually depend on acknowledging the dominant role the client plays in the problem and analyzing client application behavior.

MORE INFORMATION

The following are some suggestions that, based on experience, have yielded significant performance gains:

Normalize Logical Database Design

Reasonable normalization of the logical database design yields best performance. A greater number of narrow tables is characteristic of a normalized database. A lesser number of wide tables is characteristic of a denormalized database. A highly normalized database is routinely associated with complex relational joins, which can hurt performance. However, the SQL Server optimizer is very efficient at selecting rapid, efficient joins, as long as effective indexes are available.

The benefits of normalization include:

• Accelerates sorting and index creation, since tables are narrower.
• Allows more clustered indexes, since there are more tables.
• Indexes tend to be narrower and more compact.
• Fewer indexes per table, helping UPDATE performance.
• Fewer NULLs and less redundant data, increasing database compactness.
• Reduces concurrency impact of DBCC diagnostics, since the necessary table locks will affect less data.

If the logical database design is already fixed and total redesign is not feasible, it may be possible to selectively normalize a large table if analysis shows a bottleneck on this table. If access to the database is conducted through stored procedures, this schema change could take place without impacting applications. If not, it may be possible to hide the change by creating a view that presents the illusion of a single table.

Use Efficient Index Design

Unlike many non-relational systems, relational indexes are not considered part of the logical database design. Indexes can be dropped, added, and changed without impacting the database schema or application design in any way other than performance. Efficient index design is paramount in achieving good SQL Server performance. For these reasons, you should not hesitate to experiment with different indexes.

The optimizer reliably chooses the most effective index in the majority of cases. The overall index design strategy should be to provide a good selection of indexes to the optimizer, and trust it to make the right decision. This reduces analysis time and gives good performance over a wide variety of situations.

The following are index design recommendations:

• Examine the WHERE clause of your SQL queries, because this is the primary focus of the optimizer.

  Each column listed in the WHERE clause is a possible candidate for an index. If you have too many queries to examine, pick a representative set, or just the slow ones. If your development tool transparently generates SQL code, this is more difficult. Many of these tools allow the logging of the generated SQL syntax to a file or screen for debugging purposes. You may want to find out from the tool's vendor if such a feature is available.

• Use narrow indexes.

  Narrow indexes are often more effective than multi-column, compound indexes. Narrow indexes have more rows per page, and fewer index levels, boosting performance.

  The optimizer can rapidly and effectively analyze hundreds, or even thousands, of index and join possibilities. Having a greater number of narrow indexes provides the optimizer with more possibilities to choose from, which usually helps performance. Having a lesser number of wide, multi-column indexes provides the optimizer with fewer possibilities to choose from, which may hurt performance.

  It is often best not to adopt a strategy of emphasizing a fully covered query. It is true that if all columns in your SELECT clause are covered by a non-clustered index, the optimizer can recognize this and provide very good performance. However, this often results in excessively wide indexes and stakes too much on the hope that the optimizer will use this strategy. Usually, you should use more numerous narrow indexes which often provide better performance over a wider range of queries.

  You should not have more indexes than are necessary to achieve adequate read performance because of the overhead involved in updating those indexes. However, even most update-oriented operations require far more reading than writing. Therefore, do not hesitate to try a new index if you think it will help; you can always drop it later.

• Use clustered indexes.

  Appropriate use of clustered indexes can tremendously increase performance. Even UPDATE and DELETE operations are often accelerated by clustered indexes, since these operations require much reading. You can only have a single clustered index per table, so use this index wisely. Queries that return numerous rows, or queries involving a range of values are good candidates for acceleration by a clustered index.

  Examples:

        SELECT * FROM PHONEBOOK
        WHERE LASTNAME='SMITH'
  

  -or-

        SELECT * FROM MEMBERTABLE
        WHERE  MEMBER_NO > 5000
         AND MEMBER_NO < 6000
  

  By contrast, the LASTNAME or MEMBER_NO columns mentioned above are probably not good candidates for a non-clustered index if this type of query were common. Try to use non-clustered indexes on columns where few rows are returned.

• Examine column uniqueness.

  This helps you decide what column is a candidate for a clustered index, non-clustered index, or no index.

  The following is an example query to examine column uniqueness:

        SELECT COUNT (DISTINCT COLNAME)
        FROM TABLENAME
  

  This returns the number of unique values in the column. Compare this to the total number of rows in the table. On a 10,000-row table, 5000 unique values would make the column a good candidate for a non-clustered index. On the same table, 20 unique values would better suit a clustered index. Three unique values should not be indexed at all. These are only examples, not hard-and-fast rules. Remember to place the indexes on the individual columns listed in the WHERE clauses of the queries.

• Examine data distribution in indexed columns.

  Often a long-running query is caused by indexing a column with few unique values, or performing a JOIN on such a column. This is a fundamental problem with the data and query itself, and cannot usually be resolved without identifying this situation. For example a physical telephone directory sorted alphabetically on last name will not expedite looking up a person if all people in the city are named just SMITH or JONES. In addition to the above query which gives a single figure for column uniqueness, you can use a GROUP BY query to see the data distribution of the indexed key values. This provides a higher resolution picture of the data, and a better perspective for how the optimizer views the data.

  The following is an example query to examine data distribution of indexed key values, assuming a two-column key on COL1, COL2:

        SELECT COL1, COL2, COUNT(*)
        FROM TABLENAME
        GROUP BY COL1, COL2
  

  This will return one row for each key value, with a count of the instances of each value. To reduce the number of rows returned, it may be helpful to exclude some with a HAVING clause. For example, HAVING COUNT(*) > 1 will exclude all rows which have a unique key.

  The number of rows returned in a query is also an important factor in index selection. The optimizer considers a non-clustered index to cost at least one page I/O per returned row. At this rate, it quickly becomes more efficient to scan the entire table. This is another reason to restrict the size of the result set or to locate the large result with a clustered index.

Use Efficient Query Design

Some types of queries are inherently resource intensive. This is related to fundamental database and index issues common to most RDBMSs, not to SQL Server in particular. They are not inefficient, because the optimizer will implement the queries in the most efficient fashion possible. However, they are resource intensive, and the set-oriented nature of SQL may make them appear inefficient. No degree of optimizer intelligence can eliminate the inherent resource cost of these constructs. They are intrinsically costly when compared to a more simple query. Although SQL Server will use the most optimal access plan, this is limited by what is fundamentally possible.

For example:

• Large result sets
• IN, NOT IN, and OR queries
• Highly non-unique WHERE clauses
• != (not equal) comparison operators
• Certain column functions, such as SUM
• Expressions or data conversions in WHERE clause
• Local variables in WHERE clause
• Complex views with GROUP BY or ORDER BY

Resource-intensive:

   SELECT SUM(SALARY) FROM TABLE

   SELECT SUM(SALARY) FROM TABLE WHERE
   ZIP='98052'

   SELECT * FROM TABLE WHERE
   LNAME=@VAR

   SELECT * FROM TABLE
   WHERE  LNAME=@VAR AND ZIP='98052'

In the second example, the local variable is not resolved until run time. However, the optimizer cannot defer until run time the choice of access plan but must choose at compile time. Yet at compile time, when the access plan is built, the value of @VAR is not known and consequently cannot be used as input to index selection.

The illustrated technique for improvement involves restricting the result set with an AND clause. An optional technique would be to use a stored procedure, and pass as a parameter to the stored procedure the value for @VAR.

In some cases it's best to use a group of simple queries using temp tables to store intermediate results than to use a single very complex query.

Large result sets are costly on most RDBMS's. You should try not to return a large result set to the client for final data selection via browsing. It is much more efficient to restrict the size of the result set, allowing the database back end to perform the function for which it was intended. This also reduces network I/O, and makes the application more amenable to deployment across slow remote communication links. It also improves concurrency-related performance as the application scales upward to more users.

Use Efficient Application Design

The role application design plays in SQL Server performance cannot be overstated. Rather than picture the server in the dominant role, it's more accurate to picture the client as a controlling entity, and the server as a puppet of the client. SQL Server is totally under the command of the client regarding the type of queries, when they are submitted, and how results are processed. This in turn has a major effect on the type and duration of locks, amount of I/O and CPU load on the server, and hence whether performance is good or bad.

For this reason it's important to make the correct decisions during the application design phase. However even if you face a performance problem using a turn-key application where changes to the client application seem impossible, this doesn't change the fundamental factors which affect performance - namely the client plays a dominant role and many performance problems cannot be resolved without making client changes.

With a well-designed application, SQL Server is capable of supporting thousands of concurrent users. With a poorly-designed application, even the most powerful server platform can bog down with just a few users.

The following are suggestions for client application design that experience has shown provides good SQL Server performance:

• Use small result sets. Retrieving needlessly large result sets (say, thousands of rows) for browsing on the client adds CPU and network I/O load, makes the application less capable of remote use, and can limit multiuser scalability. It's better to design the application to prompt the user for sufficient input so that queries are submitted which generate modest result sets.

  Application design techniques which facilitate this include exercisin control over wildcards when building queries, mandating certain input fields, and not allowing ad-hoc queries.

• Use dbcancel() correctly in DB-Library applications. All applications should allow cancellation of a query in progress. No application should force the user to reboot the client machine to cancel a query. Not following this can lead to unresolvable performance problems. When dbcancel() is used, proper care should be exercised regarding transaction level. For more details, see KB article Q117143 "When and How to use dbcancel() or sqlcancel()." The same issues apply to ODBC applications, where the ODBC sqlcancel() call is used.
• Always process all results to completion. Do not design an application or use a turnkey application which stops processing result rows without cancelling the query. Doing so will usually lead to blocking and slow performance.
• Always implement a query timeout. Do not allow queries to run indefinitely. Make the appropriate DB-Library or ODBC calls to set a query timeout. With DB-Library, this is done with dbsettime(), and with ODBC via SQLSetStmtOption().
• Do not use an application development tool which does not allow explicit control over the SQL statements sent to the server. Do not use a tool that transparently generates SQL statements based on higher- level objects, if it does not provide crucial features such as query cancellation, query timeout, and complete transactional control. It is often not possible to maintain good performance or to resolve a performance problem if the application all by itself generates "transparent SQL," because this doesn't allow explicit control over transactional and locking issues which are critical to the performance picture.
• Do not intermix decision support and OLTP (on-line transaction processing) queries.
• Do not design an application or use a turnkey application which forces the user to reboot the client machine to cancel a query. This can cause a variety of difficult-to-resolve performance problems because of possible orphaned connections. For more information, see the following article in the Microsoft Knowledge Base:

        ARTICLE-ID: Q137983
        TITLE     : How to Troubleshoot Orphaned Connections in SQL Server
  
  

Techniques to Analyze Slow Performance

It may be tempting to address a performance problem solely by system-level server performance tuning. For example, how much memory, the type of file system, the number and type of processors, and so forth. The experience of Microsoft SQL Server Support has shown that most performance problems cannot be resolved this way. They must be addressed by analyzing the application, the queries the application is submitting to the database, and how these queries interact with the database schema.

First, isolate the query or queries that are slow. Often it appears that an entire application is slow, when only a few of the SQL queries are slow. It is usually not possible to resolve a performance problem without breaking the problem down and isolating the slow queries. If you have a development tool that transparently generates SQL, use any available diagnostic or debug mode of this tool to capture the generated SQL. In many cases trace features are available, but they may not be openly documented. Contact the technical support for your application to determine if a trace feature exists for monitoring the SQL statements emitted by the application.

For application development tools that use embedded SQL, this is much easier - the SQL is openly visible.

If your development tool or end-user application does not provide a trace feature, there are several alternatives:

• Use the 4032 trace flag according to the instructions in the SQL Server 4.2x "Troubleshooting Guide," and the SQL Server 6.0 "Transact-SQL Reference." This will allow capture of the SQL statements sent to the server in the SQL errorlog.
• Monitor the queries via a network analyzer such as Microsoft Network Monitor, which is part of Systems Management Server (SMS).
• For ODBC applications, use the ODBC Administrator to select tracing of ODBC calls. See the ODBC documentation for more details.
• Use a third party client-side utility which intercepts the SQL at the DB-Library or ODBC layers. An example of this is SQL Inspector from Blue Lagoon Software.
• Use the SQLEye analysis tool provided as an example in the Microsoft TechNet CD-ROM. NOTE: SQLEye is not supported by Microsoft Technical Support.

• Run the suspected slow query in isolation, using a query tool such as ISQL, and verify that it is slow. It is often best to run the query on the server machine itself using ISQL and local pipes, and redirect the output to a file. This helps eliminate complicating factors, such as network and screen I/O, and application result buffering.
• Use SET STATISTICS IO ON to examine the I/O consumed by the query. Notice the count of logical page I/Os. The optimizer's goal is to minimize I/O count. Make a record of the logical I/O count. This forms a baseline against which to measure improvement. It is often more effective to focus exclusively on the STATISTICS IO output and experiment with different query and index types than to use SET SHOWPLAN ON. Interpreting and effectively applying the output of SHOWPLAN can require some study, and can consume time that can be more effectively spent on empirical tests. If your performance problem is not fixed by these simple recommendations, then you can use SHOWPLAN to more thoroughly investigate optimizer behavior.
• If the query involves a view or stored procedure, extract the query from the view or stored procedure and run it separately. This allows the access plan to change as you experiment with different indexes. It also helps localize the problem to the query itself, versus how the optimizer handles views or stored procedures. If the problem is not in the query itself but only when it is run as part of a view or stored procedure, running the query by itself will help determine this.
• Be aware of possible triggers on the involved tables that can transparently generate I/O as the trigger runs. You should remove any triggers involved in a slow query. This helps determine if the problem is in the query itself or the trigger/view, and therefore, helps direct your focus.
• Examine the indexes of the tables used by the slow query. Use the previously listed techniques to determine if these are good indexes, and change them if necessary. As a rough rule-of-thumb, at least try indexing each column in your WHERE clause. Often performance problems are caused by simply not having a column in the WHERE clause indexed, or by not having a useful index on such a column.
• Using the queries previously mentioned, examine the data uniqueness and distribution for each column mentioned in the WHERE clause, and especially for each indexed column. In many cases simple inspection of the query, table, indexes, and data will immediately show the problem cause. For example performance problems are often caused by having an index on a key with only three or four unique values, or performing a JOIN on such a column, or returning an excessive number of rows to the client.
• Based on this study, make any needed changes to the application, query, or indexes. Then re-run the query after making the change and observe any change in I/O count.
• After noting improvement, run the main application to see if overall performance is better.

Steps for Examining I/O vs. CPU-bound Query Behavior:

• Use Windows NT Performance Monitor to watch I/O vs. CPU activity. Watch all instances of the "% Disk Time" counter of the "LogicalDisk" object. Also watch the "% Total Processor Time" counter of the "System" object. To see valid disk performance information, you must have previously turned on the Windows NT DISKPERF setting by issuing "diskperf -Y" from a command prompt, and then rebooting the system. See the Windows NT documentation for more details.
• While running the query, if the CPU graph is consistently high (say, greater than 70%), and the % Disk Time is consistently low, this indicates a CPU-bound state.
• While running the query, if the CPU graph is consistently low (say, less than 50%), and the % Disk Time is consistently high, this indicates an I/O bound state.
• Compare the CPU graph with the STATISTICS IO information.

Conclusion

SQL Server is capable of very high performance on large databases. This is especially the case with SQL Server 6.0. To achieve this performance potential, you must use efficient database, index, query, and application design. These areas are the best candidates for obtaining significant performance improvement. Try to make each query as efficient as possible, so that when your application scales up to more users, the collective multi- user load is supportable. Study of the client application behavior, the queries submitted by the application, and experimentation with indexes using the guidelines in this document are especially suggested. A methodical approach in analyzing performance problems will often yield significant improvement for relatively little time investment.