SQL Server Application Design Performance Tuning Tips
When designing applications using SQL Server as the backend, consider designing the application to take advantage of the n-tier application model. By doing so, you will design applications that not only maximize performance, but help to boost scalability. Application design can be divided into two areas: logical design and physical design
SQL Server logical application design includes these three services:
- Presentation Services: Normally used to produce the user interface and handle user input and output.
- Business Services: Includes the logic to handle business rules and logic.
- Data Services: Used to store and manipulate data, including maintaining database integrity.
While SQL Server-based applications should ideally be designed around the above three services, the actual physical implementation is different, and includes these options:
- Physical Two-Tier Implementation With Fat Clients: Generally, the presentation and business services are on the client, and the data services reside on the SQL Server.
- Physical Two-Tier Implementation With a Fat Server: Generally, the presentation services reside on the client and the business and data services reside on the SQL Server.
- Physical Three-Tier Implementation: The presentation services reside on the client, the business services reside on a MTS (Microsoft Transaction Server) server. The data services reside on a SQL Server.
- Internet Implementation: The web browser, on the client, is where the presentation of the application is made, but the presentation services themselves generally reside on a web server, running as ASP code or COM objects. Business services generally run on an MTS server. And the data services generally run on the SQL Server.
Each of the above services on physical design implementations is not a pure model, but varies depending on the exact nature of your application. For best SQL Server performance, the appropriate model needs to be matched against the needs of the users and how the application is actually used in the real world. Keep in mind that application design is probably the most important influence on performance and scalability of SQL Server-based applications. [6.5, 7.0, 2000, 2005] Updated 1-6-2006
*****
When designing the business services tier in an n-tier application, don’t make any of these common design mistakes:
- Don’t create complex or deep object hierarchies. The creation and use of complex classes, or a large number of objects used to model complex business rules, can be resource intensive and reduce the performance and scalability of your application. This is because the memory allocation associated with creating these objects, and then freeing the memory, is costly.
- Don’t include data-centered tasks in the business services tier, instead, keep them in the data services tier where they belong.
- Don’t try to maintain state in the business services tier. MTS is not designed to maintain state, and if you break this rule, performance and scalability will suffer.
[6.5, 7.0, 2000] Updated 1-6-2006
*****
When creating n-tier applications, some developers avoid stored procedures and instead pass ANSI-compliant SQL (embedded SQL) to the database. The theory behind this is that any database, besides SQL Server, could then be used as the back-end database for the application. SQL Server stored procedures, as you probably know, are often SQL Server specific.
The problem with this approach is that it can negatively affect performance. As you probably know (if you have read much of this Web site) stored procedures can enhance the performance of your application as compared to sending embedded SQL to SQL Server.
So how do you resolve the issue of trying to code an n-tier application that is flexible enough to run with multiple database programs, and at the same time performs to its fullest? The way around this is to design your components to recognize which database you are using, and then to communicate to each supported database optimally. For example, in the case of SQL Server, stored procedures would be used instead of embedded SQL. Sure, this is more work for the developer, but is the purpose of the application to make the life of the developer easier, or to make the work of the users/customers easier? [7.0, 2000, 2006] Updated 1-6-2006
*****
Performance and scalability testing needs to be included as part of the overall design and implementation effort, not something left until the application is ready for production. By testing early, and identifying potential bottlenecks, they can be fixed earlier in the process, instead of after the application is in production. This can save a lot of time, money, and frustration. [6.5, 7.0, 2000, 2005] [7.0, 2000] Updated 1-6-2006
*****
One way to help reduce the overhead on SQL Server is to do as much data validation on the client as you can before the data is sent to SQL Server. This way, SQL Server doesn’t have to waste any time dealing with bad data. Instead, make your client application do this work, allowing the user to correct the data before it is sent to SQL Server. [6.5, 7.0, 2000] Updated 1-6-2006
*****
One of the most important, if not the most important, factor that affects SQL Server’s performance is the application that is calling it. If the application is well designed, SQL Server can support literally thousands of simultaneous connections. But if the application is poorly designed, it might only support a handful.
Keep in mind that SQL Server is a servant of the applications using it. Whatever queries or tasks are sent its way, SQL Server will do its best to run them. But if the code SQL Server is being asked to perform isn’t well designed, SQL Server’s performance will suffer, through no fault of its own.
For example, an application that sends 500 different requests to access 500 different rows in a SQL Server database is not very efficient. A single query requesting the 500 different rows is much more efficient. SQL Server will do whatever it is told to do, even if it is a bad idea.
Many SQL Server performance problems can be attributed to poor application design. Because of this, it is critical that any application being used to access SQL Server be well thought-out and designed to get the most out of SQL Server. [6.5, 7.0. 2000, 2005] Updated 6-27-2006
*****
Avoid including Transact-SQL in a COM+ transaction. Instead, put the code in a stored procedure, and call it from the COM+ object. From a performance and scalability perspective, it is more optimal to run Transact-SQL transactions from within a stored procedure instead of from with a COM+ object. This is due in large part because running Transact-SQL code from a COM+ object can lead to unnecessary locking and blocking during the length of the COM+ transaction. [7.0, 2000] Updated 6-27-2006
*****
If your application runs queries against SQL Server that are long by nature, try to design the application to be able to run queries asynchronously. This way, one query does not have to wait for the next before it can run. One way to build in this functionality into your n-tier application is to use the Microsoft Message Queue Server (MSMQ). [6.5, 7.0, 2000, 2005] Updated 6-27-2006