0

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?

5
  • You can't separate one instance into two instances without transferring the data from one instance1.database to the other instance2.database. While transferring the data from instance1.database to instance2.database you will have similar locking issues. Which benefits to you see of splitting your instance into two? – John K. N. Dec 14 '16 at 12:54
  • Most of our activity is during the day , i plan to transfer the data from instance1 to instance2 by night so the benefit will be better performance during the day. – alonk Dec 14 '16 at 13:02
  • 3
    If the transfer happens at night, when there is low activity on the server, using READ UNCOMMITTED just seems unnecessary. – Andriy M Dec 14 '16 at 13:49
  • Is the data being inserted into one table or into multiple tables with referential integrity being a possible limiting factor? Could you provide some anonymous details on the table definitions involved? – John K. N. Dec 14 '16 at 13:56
  • The data is being inserted into multiple tables such as : tblCalls , tblCallsType , tblDevice , tblEvents , tblEventTypes. all of this tables have PK and some of them FK's ( for example tblEvents has Foreign key to tblEventsTypes). – alonk Dec 14 '16 at 14:15
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)

1

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 !

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.