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:
- Getting the rows from the automatic devices.
- 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?
Best Answer
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)