Application design plays a pivotal role in determining the performance of a system using Microsoft® SQL Server™. Consider the client the controlling entity rather than the database server. SQL Server is completely under the command (a puppet) of the client regarding the type of queries, when they are submitted, and how the results are processed. This in turn has a major effect on the type and duration of locks, amount of I/O, and processing (CPU) load on the server, and hence on whether performance is generally good or bad.
For this reason, it is important to make the correct decisions during the application design phase. However, even if a performance problem occurs using a turn-key application, where changes to the client application seem impossible, this does not change the fundamental factors that affect performance: the client plays a dominant role and many performance problems cannot be resolved without making client changes. A well-designed application allows SQL Server to support thousands of concurrent users. Conversely, a poorly designed application prevents even the most powerful server platform from handling more than a few users.
Guidelines for client application design include:
Network roundtrips between the client and SQL Server are usually the number one reason for poor performance in a database application, an even greater factor than the amount of data transferred between server and client. Network roundtrips describe the conversational traffic sent between the client application and SQL Server for every batch and result set. By making use of stored procedures, you can minimize network roundtrips. For example, if your application takes different actions based on data values received from SQL Server, make those decisions directly in the stored procedure whenever possible, thus eliminating network traffic.
If a stored procedure has multiple statements, then by default SQL Server sends a message to the client application at the completion of each statement and details the number of rows affected for each statement. Most applications do not need these messages. If you are confident that your applications do not need them, you can disable these messages, which can improve performance on a slow network. Use the SET NOCOUNT session setting to disable these messages for the application. For more information, see SET NOCOUNT.
Retrieving needlessly large result sets (for example, thousands of rows) for browsing on the client adds CPU and network I/O load, makes the application less capable of remote use, and limits multiuser scalability. It is better to design the application to prompt the user for sufficient input so queries are submitted that generate modest result sets. For more information, see Optimizing Application Performance Using Efficient Data Retrieval.
Application design techniques that facilitate this include exercising control over wildcards when building queries, mandating certain input fields, not allowing ad hoc queries, and using the TOP, PERCENT, or SET ROWCOUNT Transact-SQL statements to limit the number of rows returned by a query. For more information, see Limiting Result Sets Using TOP and PERCENT and SET ROWCOUNT.
An application never should force the user to reboot the client machine to cancel a query. Ignoring this can lead to unresolvable performance problems. When a query is canceled by an application, for example, using the open database connectivity (ODBC) sqlcancel function, proper care should be exercised regarding transaction level. For example, canceling a query does not commit or roll back a user-defined transaction. All locks acquired within the transaction are retained after the query is canceled. Therefore, after canceling a query, always either commit or roll back the transaction. The same issues apply to DB-Library and other application programming interfaces (APIs) that can be used to cancel queries.
Do not allow queries to run indefinitely. Make the appropriate API call to set a query time-out. For example, use the ODBC SQLSetStmtOption function.
For more information about setting a query time-out, see your ODBC API documentation.
For more information about setting a lock time-out, see Customizing the Lock Time-out.
Do not use a tool that transparently generates Transact-SQL statements based on higher-level objects if it does not provide crucial features such as query cancellation, query time-out, and complete transactional control. It is often not possible to maintain good performance or to resolve a performance problem if the application generates transparent SQL statements, because this does not allow explicit control over transactional and locking issues, which are critical to the performance picture.
Cursors are a useful tool in relational databases; however, it is almost always more expensive to use a cursor than to use a set-oriented SQL statement to accomplish a task.
In set-oriented SQL statements, the client application tells the server to update the set of records that meet specified criteria. The server figures out how to accomplish the update as a single unit of work. When updating through a cursor, the client application requires the server to maintain row locks or version information for every row, just in case the client asks to update the row after it has been fetched.
Also, using a cursor implies that the server is maintaining client state information, such as the user’s current rowset at the server, usually in temporary storage. Maintaining this state for a large number of clients is an expensive use of server resources. A better strategy with a relational database is for the client application to get in and out quickly, maintaining no client state at the server between calls. Set-oriented SQL statements support this strategy.
However, if the query uses cursors, determine if the cursor query could be written more efficiently either by using a more-efficient cursor type, such as fast forward-only, or a single query. For more information, see Optimizing Application Performance Using Efficient Data Retrieval.
Do not design an application or use an application that stops processing result rows without canceling the query. Doing so will usually lead to blocking and slow performance. For more information, see Understanding and Avoiding Blocking.
| Deadlocking | Locking |
| Dynamic Locking | Transactions |