Tips for Performance Tuning SQL Server OLTP Databases

By : Brad McGehee Jan 02, 2007

Printer friendly

Try to avoid performing both OLTP and OLAP transactions within the same database. If you do, OLTP transactions will slow down OLAP transactions, and OLAP transactions will slow down OLTP transactions. OLTP and OLAP transactions should be considered mutually exclusive transactions, and ideally, should be relegated to their own databases.

For example, OLTP transactions should occur in a database specifically designed and tuned for OLTP. As appropriate, data from the OLTP database should be moved to an OLAP database or datamart so that OLAP queries can be performed against it. This way, both OLTP and OLAP activity won't interfere with each other and hurt your application's performance. [6.5, 7.0, 2000, 2005] Updated 10-4-2005

*****

Keep transactions as short as possible. The shorter transactions are, the shorter that locks have to be held, which results in both greater performance and scalability. Here are some ways to help keep transactions as short as possible:

·         It is especially important that transactions aren't kept open while waiting for user input. User input should be collected as a whole, and then submitted at one time. In other words, don't collect user input in the middle of a transaction.

·         Use stored procedures for your code.

·         Only return the exact data needed, no extra columns or rows should be returned.

·         Try to reduce the number of round trips between the client and SQL Server.

·         In large units of work, consider breaking it down into smaller units, so that there are many, very quick transactions rather than a single, large transaction. A large transaction hold locks for longer periods, contributing to performance problems.

·         From another perspective, if there are many, many transactions going on, contributing to excess round trips and network traffic, consider batching them together so that there are fewer round trips between the client and SQL Server.

·         Consider using the ODBC SQLParamOptions Function which allows multiple parameter sets for a single Transact-SQL statement to be sent from the client to the server in a batch, which helps to reduce roundtrips.

·         Do not use a transaction to view and browse data.

·         Consider using a lower transaction level, if appropriate.

·         Always manage your transaction by beginning and ending your transaction explicitly. Do not use implicit transactions.

[6.5, 7.0, 2000, 2005] Updated 10-4-2005

*****

Avoid INSERTing, UPDATEing, or DELETEing large numbers of records in a single transaction. If you do, all the records affected by your action will be locked until the transaction is done. If you find that you need to perform mass data changes, it is better to batch them into smaller, shorter transactions to prevent unnecessary locking, which can block other users. [6.5, 7.0, 2000, 2005] Updated 5-3-2005

*****

As transactional data accumulates in database tables, consider archiving older data to separate tables in the same database, or in other databases. This can considerably reduce query time on large tables. Another option is to move this older data to a OLAP database. [6.5, 7.0, 2000, 2005] Updated 5-3-2005

*****

Design SQL Server-based applications to make the fewest network trips between the client and SQL Server. One way to do this is to try to execute a single stored procedure for an entire transaction. Of course, keep this transaction as short as possible to prevent potential blocking. [6.5, 7.0, 2000, 2005] Updated 5-3-2005

*****

If an OLTP transaction affects many tables, and if you can control the order in which these tables are accessed within the transaction, try to locate any references to the most used tables toward the end of the transaction. This helps to reduce the amount of time locks have to be held during the transaction, helping to boost concurrency. [6.5, 7.0, 2000, 2005] Updated 5-3-2005

*****

As an application is being designed, try to identify those transactions that are time critical, such as looking up a customer or placing an order, and focus on designing the application to meet these critical needs. If the application has already been written, you can also take a similar path by identifying the time critical transactions and then focusing on how to improve them, one-by-one. [6.5, 7.0, 2000, 2005] Updated 5-3-2005

*****

Avoid using implicit transaction. Use explicit transactions instead. This is because it is not always easy or possible to determine when an implicit transaction starts. It is possible for an implicit transaction to start without your knowledge, potentially reducing concurrency and causing other problems. 

For example, if a particular application uses implicit transactions, but the developer forgets to specifically COMMIT or ROLLBACK the transaction, the transaction is still open, potentially still holding open locks, preventing other users from accessing the data they need. If this situation repeats itself, more and more open locks are created and at some point, the database becomes unusable.

To find out if there are any open transactions in a database, run the DBCC OPENTRAN command. [6.5, 7.0, 2000, 2005] Updated 5-3-2005