SQL Server Application Design Performance Tuning Tips

As is mentioned in other parts of this website, it is important to design applications that keep transactions as short as possible. This reduces locking and increases application concurrently, which helps to boost performance.

One of the biggest mistakes a developer can make is to start a transaction when a data modification screen is first opened, then only close the transaction when the user has entered all of the data and clicks a "Save" button. This means that one or more records may be locked for the entire time that the user has the screen open, which can significantly degrade an application's overall performance.

A better way to design a data entry screen is to perform any necessary transactions before or after the user has entered all the necessary data. This way, records are only locked for a very small fraction of time while the transaction completes.

For example, let's say a user need to update an invoice. To do this, data from three different tables is required. The first step would be to retrieve the necessary data from the three tables, then display it to the user. Once the data is SELECTED from the three tables, the locks are released. Now the user can view the data and change it as desired. Once the user is done making any changes, this data is then updated to the three tables as a separate transaction. This way, no records are locked while the user is actually updating the data. This example is oversimplified, but I think you should get the idea that you should not allow user direct activity within any transaction. [6.5, 7.0, 2000, 2005] Updated 8-21-2006

*****

In order to reduce network traffic between the client or middle-tier and SQL Server, and to boost your SQL Server-based application's performance, only the data needed by the client or middle-tier should be returned by SQL Server. In other words, don't return more data (both rows and columns) from SQL Server than you need to the client or middle-tier, and then further reduce the data to the data you really need at the client or middle-tier. This wastes SQL Server resources and network bandwidth.

In addition, when designing screens that allow users to select criteria and then return results from SQL Server to the client or middle-tier, don't allow users to return huge amounts of data that will never be used by the user. Require the user to narrow down their search criteria so that only the required records are returned, not unnecessary records.

For example, I have seen third-party applications that allow users to return all rows of a table, including tables with over 20 million rows. Obviously, users don't need to see 20 million rows. Design your application to prevent users from making "less than wise" choices. [6.5, 7.0, 2000, 2005] Updated 12-20-2004

*****

Design your client application to allow your users to cancel running queries. Some queries, especially those that are created by end-users, can be poorly written and run on and on and on. By allowing users to cancel a query, valuable SQL Server resources are preserved.

In your application, when you write code to cancel a query, be sure that not only do you cancel the query, but you also roll back the transaction. If you don't, any locks acquired by the query don't automatically go away, unless you roll back the transaction.

If you are using an ODBC connection, you can use the SQLCANCEL function to cancel a query. Other APIs will have their own way to cancel a running query. [6.5, 7.0, 2000, 2005] Updated 12-20-2004