Tips for Performance Tuning SQL Server OLTP Databases

Moving transactional data from a production OLTP database to another database for reporting purposes is a common way to prevent OLAP-type transactions from interfering with your production database.

Some of the more common ways to accomplish this goal is to use replication, a periodic batch process, DTS, SSIS, log shipping, or even backup and restore. Each of these have their pros and cons.

In many cases, reports don't have to be up-to-date to the most current transaction, and periodic refreshes, like those listed above, from the production database to a reporting database are all that is needed. But sometimes, reporting needs dictate that the data must be up-to-the-second. If this is the case, then periodic refreshes may not work.

One option would be to run time-critical reports directly from the production database, but non-time-critical reports from a reporting database. Assuming the reports won't interfere with production, then this may be an acceptable option. But if running the time-critical reports that interfere with production, then your options are limited.

One option you may want to consider is to use triggers to automatically move the required transactional data from the production database to a reporting database. This would ensure that the reporting database has up-to-the-second data for time-critical reports. Of course adding triggers adds overhead to the production database, but it may be less that running the time-critical reports on the production database. If you face this situation, you will probably want to perform much testing to find out which option works best for you. [6.5, 7.0, 2000, 2005] Updated 10-4-2005

*****

While replication can be used to offload some work from the production server, it also puts some extra load on it. The log reader agent will be at work on the server, identifying those transactions that need to be replicated. Because of this, you must balance the performance degradation caused by adding replication, by the performance boost provided by not having to do reporting on a production OLTP SQL Server.

As much as possible, you will want to minimize the amount of overhead that replication incurs. One of the biggest things you can do in this case is to locate the distribution server on a dedicated server, not on the OLTP server. [6.5, 7.0, 2000, 2005] Updated 10-4-2005