The role application design plays in SQL Server performance cannot be overstated. Rather than picturing 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 query, when the query will be submitted, and how the results will be 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 performance.
For this reason, it's important to make the correct decisions during the application design phase. Even if you're using a turnkey application where changes to the client application seem impossible, this doesn't change the fundamental factors that affect performance—namely, that the client plays a dominant role and that 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 suggestions for client application design are known to improve SQL Server performance:
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 only queries that generate modest result sets are submitted. Application design techniques that help ensure a small result set include exercising control over wildcards when building queries, mandating certain input fields, and disallowing ad hoc queries.
All applications should allow cancellation of a query in progress. No application should force the user to reboot the client computer to cancel a query because this can lead to unresolvable performance problems.
When dbcancel() is used, proper care should be exercised regarding transaction level. The same issues apply to ODBC applications, where the ODBC sqlcancel() call is used. For more information, see "Causes of the Transaction Log Filling Up" in SQL Server Books Online.
Do not design an application or use a turnkey application that stops processing result set rows without canceling the query. Doing so will usually lead to blocking and slow performance.
Do not allow queries to run indefinitely. Make the appropriate DB-Library or ODBC calls to set a query time-out.
With DB-Library, this is done with dbsettime(); with ODBC use SQLSetStmtOption().
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 time-out, and complete transactional control.
It is often not possible to maintain good performance or to resolve a performance problem if the application independently generates "transparent SQL" because this doesn't allow explicit control over transactional and locking issues that are critical to the performance picture.