August 4, 2008 at 3:44 am
I have a clustered production server (12 CPU, 28 GB AWE Memory), and a clustered reporting server(12 CPU, 6 GB AWE Memory). There is transactional replication setted up from production to reporting server in an interval of 15 mins.
Database Size is 7 GB approx.
I have a query, which executes at Production Server within a min, but at Reporting Server it took 28 mins (approx). If I make the syncronization interval 1 hour, then it is taking a time of around 2 mins.
I have tested the DB (Reporting Server) in my local PC which is having a HT Processor and 1 GB memory. It is taking around 11 mins.
Please suggest me, what is happening at Reporting Server end, how can I resolve the issue..... Thanks in anticipation.
August 4, 2008 at 8:43 am
Most likely, it's locking issues. How long does your synchronization take to finish?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 4, 2008 at 12:07 pm
have you checked the execution plan? we had a similar issue except it was faster on the sub and it turned out to be statistics
June 28, 2010 at 12:07 am
Sumon Basu (8/4/2008)
I have a clustered production server (12 CPU, 28 GB AWE Memory), and a clustered reporting server(12 CPU, 6 GB AWE Memory). There is transactional replication setted up from production to reporting server in an interval of 15 mins.Database Size is 7 GB approx.
I have a query, which executes at Production Server within a min, but at Reporting Server it took 28 mins (approx). If I make the syncronization interval 1 hour, then it is taking a time of around 2 mins.
I have tested the DB (Reporting Server) in my local PC which is having a HT Processor and 1 GB memory. It is taking around 11 mins.
Please suggest me, what is happening at Reporting Server end, how can I resolve the issue..... Thanks in anticipation.
June 29, 2010 at 8:03 am
1) check for blocking
2) statistics could well be old, leading to poor query plans on report server
3) get a professional in to do some performance analysis and tuning, and teach you on how to do the same. you get faster response time AND knowledge; win-win
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 30, 2010 at 10:16 am
You also need to understand how transactional replication works. If I run a single update query on the publisher that updates 1000 records, then on the subscriber, that is broken down into 1000 update queries that each update a single record. This is doen to ensure absolutely identical results.
There are literally 1000's of reasons why you might be seeing this behavior. You need to do some baisc performance monitoring of the system and determine what your bottleneck is.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply