August 6, 2008 at 2:49 am
Hi Team,
I am facing a problem regarding replication. I have 2 clustered server, 1 for OLTP (12 CPU, 8 GB RAM), and 1 for Reporting (12 CPU, 8 GB RAM). We set up Transactional Replication from OLTP to Reporting. The distribution agent is scheduled to run within a span of 15 mins. A query executed on the OLTP database is taking around 1.36 mins, while the same query is taking around 30 mins to execute at the reporting server. The query plans are same, as I compared.
After I explicitly issued the command SET TRANSACTIONAL ISOLATION LEVEL REPEATBLE READ against the report server, This query is taking only 3.17 mins to execute.
Again I manually SET TRANSACTIONAL ISOLATION LEVEL READ COMMITTED against the same report server. This query is taking 3.17 mins now.
Can someone help me with the RCA (Root Cause Analysis) of this issue.
August 7, 2008 at 6:14 pm
What prompted you to change the isolation level to repeatable read? Do you usually run the query in serializable?
August 8, 2008 at 12:01 am
hi Matt,
As per my understanding, the effect of Isolation level on the database engine was not reflecting. As I manually once set the TRANSACTION ISOLATION LEVEL to READ COMMITTED (which is default), the database engine is now properly taking the effect of default ISOLATION LEVEL. Also there was an issue with the Hotfix, which I have cleared by installing the cumulative update 8.
Thanks & Regards,
Sumon
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply