SQL Server Application Design Performance Tuning Tips

If your application makes use of COM objects to access SQL Server data, consider designing the application to take advantage of Object Pooling using Microsoft Transaction Server (MTS). MTS allows objects to be pooled, greatly increasing the overall performance and scalability of your application. [2000, 2005] Updated 6-27-2006

*****

Both MTS and ODBC database connection pooling can help boost a SQL Server application's scalability and performance. But if you have the choice to pick which method to use, choose MTS database connection pooling. It can be as much as two to four times faster than ODBC database connection pooling. [6.5, 7.0, 2000, 2005] Updated 6-27-2006

*****

While using MTS offers many scalability and performance benefits for SQL Server-based applications, this does not mean that MTS is always the best solution for all of your applications. Keep in mind that objects instantiated using MTS take longer to instantiate than objects not instantiated under MTS. When deciding whether or not place an object under MTS, consider how the object benefits from being in MTS. For example, transaction-oriented objects can take advantage of MTS's transaction-oriented features. But if an object is not taking any benefit of MTS's special abilities, then consider placing it outside of MTS for maximum performance. [6.5, 7.0, 2000, 2005] Updated 6-27-2006

*****

Depending on the needs of your users and the nature of your application, you might want to consider client or application server data caching to help reduce the load on your SQL Server.

In many cases, corporate desktop computers are underutilized. If this is the case, you can design your application (fat client) to retrieve data from the SQL Server database as it is needed, and then cache it on the client so that it can be used over and over. This way, whenever the client needs to access the data, they will use up local desktop resources, not SQL Server resources.

The ability to do this of course depends on many factors, such as whether the data being cached changes often, or if the client needs to perform updates to the data that need to be synchronized in the SQL Server database. The locally cached data could be stored in a Microsoft Access database or the MSDE or Express versions of SQL Server.

Instead of caching data on clients, data can also be cached on an application server that is shared by multiple clients. This option is most beneficial when one or more clients need to access the same data over and over.

Obviously, employing data caching in your application is a decision that needs to be determined very early in the design process. [6.5, 7.0, 2000, 2005] Updated 6-27-2006

*****

If you are the DBA of a SQL Server-based application and suspect its poor performance might be related to poor application design, one of the best tools to use to help verify this hypothesis is to use the Profiler. The Profiler has the ability to capture all of the events between the application and SQL Server. One of the ways to help identify performance problems due to poor application design is to watch how long it takes for each event to occur.

For example, each event captured by the Profiler can display a Duration time (in milliseconds) it takes for the event to occur. If an event takes an inordinate amount of time, this is a good indication that this particular event is indicative of poor application design. Another example is if you see the same events repeated over and over, such as cursor fetches. Well-designed applications shouldn't repeat the same events too often. The Profiler is a powerful tool for finding out how your applications interact with SQL Server. [7.0, 2000, 2005] Updated 8-21-2006

*****

As part of the early design process, consider how you intend to archive data in your database that is never or rarely used. Most databases grow quickly, often containing data you no longer need. As you know, the larger a database is, the longer it takes most tasks to perform. So it is important to ensure that your database does not have any unnecessary data. And the best way to ensure this is to figure out early in the design process how to determine what data is not needed in the database so that it can be archived or deleted.

Some archived data may never be needed again, while other data may be needed from time to time, but not so often that it needs to be in the production database. There are many different ways to archive data (archive database, OLAP database, text files, etc.) How you archive your data is not important to performance, but the fact that you archive seldom used data is a key factor in the performance of many databases. [6.5, 7.0, 2000, 2005] Updated 8-21-2006

*****

SQL Server allows you to link servers, in effect giving you the ability to call a stored procedure on a remote SQL Server from a local SQL Server. While linked servers are very flexible, they aren't the most efficient way to design your application. Not only do linked servers experience a performance penalty, they also "hardwire" two separate and distinct databases together, which never is a good thing.

One way to avoid linked servers, and their drawbacks, is to model your database access code based on one object per database connection, not one object per database entity. This, in effect, removes the need for linked servers. Each database connection object will be responsible for managing its connection to a specific server, including the calls to the stored procedures on that specific server. [7.0, 2000, 2005] Updated 8-21-2006

*****

There are many third-party tools that you can use to automatically write Transact-SQL code. While these tools are useful for prototyping, learning, and for experimenting with, they are often not great tools for writing production Transact-SQL code, unless you have the ability to take the code the software generates and tune it appropriately. The code produced by many of these tools don’t always produce optimized code. In addition, some of these tools produce code that does not permit query cancellation, handle query time-outs, or permits transactional control. All of these are critical to high performing SQL Server applications.

Another drawback of these tools is that they make you lazy, hurting your ability to understand what the code is doing. Only by truly understanding your Transact-SQL code will you be able to optimize it for the best performance.

As a general rule of thumb, you should avoid using Transact-SQL code application development tools for production applications. [6.5, 7.0, 2000, 2005] Updated 8-21-2006