Does using lower Isolation Level improve OLTP performance?

Question

We have a SQL Server 2008 R2 instance which gets millions of rows of data per day from automatic devices such as CTI and AVR. On the same instance there are many heavy reports which analyze the data.

I'm looking into the option of separating these activities into two distinct processes:

1. Getting the rows from the automatic devices.
2. Analyzing the data and producing heavy reports.

I plan to try to set the isolation level for the first process to the minimum (read uncommitted) as data integrity is not such an issue.

Would I get significant performance improvement from both the separation and usage of the lowest isolation level?

Answer 1

SQL Server 2008 Solution

Seeing as you are thinking of duplicating the database into two instances, changing the isolation level from the default Read committed isolation level to Read uncommitted will not have much of an impact.

Reference: Isolation Levels in the Database Engine

The inserts occur on instance1.database which does not have any active (SELECT) queries.

Transferring the data from instance1.database to instance2.database (Reporting database) will have a small impact during the night. Otherwise the users are not impacted by the INSERTs occurring on instance1.database during normal office hours while accessing instance2.database.

No need to change the isolation level.

Alternate Solution : Availability Groups

You might be better off with a newer SQL Server version (2012 or greater) and Availability Groups. This way you could have your live data sent to the AG and you just configure the second replica to be READ-ONLY, which you then query for reporting purposes. This is documented in the MSDN article:

Configure Read-Only Access on an Availability Replica (SQL Server)

Here is an overview of the AlwaysOn Availability Groups (SQL Server)

Answer 2

There are 3 possible things to look for :

1. Read the data loading performance guide and adapt the loading method and some good techniques.

2. Make sure you have your autogrowth settings tuned with a sensible value to have less autogrowth events as possible during inserts.

   2.a Have your stats updated once the load finishes. This will help query optimizer generate better plans.

3. You are limited by the version of sql server you are using. SQL Server 2016 is designed for such analytic and heavy workload esp. using clustered and non clustered column store index and memory optimized tables. Think of a possible upgrade !