Performance
|
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 that are sent its way, SQL Server will do its best to run them. But if the tasks SQL Server is being asked to perform aren'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] Added 4-25-2002 ***** 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] Updated 1-2-2004 ***** 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 with creating these objects and then
freeing 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-2-2004 ***** 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 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 website) 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 easier? [7.0, 2000] Updated 1-2-2004 ***** 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] Added 7-9-2004 ***** 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] [7.0, 2000] Updated 1-2-2004 ***** If your application runs queries against SQL Server, that by nature are long, 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] Updated 8-9-2004 More info from Microsoft ***** 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] More info from Microsoft Updated 8-9-2004 ***** 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] Updated 8-9-2004 ***** 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] Updated 8-9-2004 ***** 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 desktop version 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] Updated 8-9-2004 ***** If you are the DBA of a SQL Server-based application, and suspect that 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 indicates how much CPU 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] Added 1-2-2001 ***** 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 and can be archived. 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] Added 4-16-2001 ***** 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] Added 8-28-01 ***** 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 doesn't 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] Updated 4-25-2002 ***** 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 was SELECTED from the three tables, the locks would be 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] Added 12-27-2001 ***** 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] Added 4-25-2002 ***** 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 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] ***** 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] Added 7-2-2003 |