Snapshot Isolation on Transactional Replicated DB _slow ????

Question

Post reply

Snapshot Isolation on Transactional Replicated DB _slow ????

jbalbo

SSCertifiable

Points: 7580
More actions
May 31, 2021 at 1:58 pm

#3890223

Hi
We went from a backup and restore Reports database to a transactional Replicated DB.
I have a job overnight that creates a table, the jobs took about an hour, now on the replicated copy of the DB the job take 12+ hours
The job does do a bunch of updates .
I tried adding SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in the stored procedure with no better results.
What about Snapshot Isolation?
Not sure how to use or if it will help.
Any ideas or where I can read would be appreciated

Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Andy sql SSCrazy Eights Points: 9484 More actions · Answer 2

Unfortunately, Replication is an unloved area of MSSQL. Microsoft hasn't made any changes for years. On the plus side, it still works and is reliable.

I don't quite understand your scenario, or what you are trying to achieve. You have a "job" which runs on the replicated database; what does this job do? Purely SELECT statements?

A Subscriber/Publisher scenario is very different to a backup/restore scenario. For example, if the Publisher is very busy, every single transaction will be pushed to the Subscriber; this could mean table locks, exclusive locks, schema locks, etc on the Subscriber; none of this would happen with a simple backup/restore.

jbalbo SSCertifiable Points: 7580 More actions · Answer 3

Hi Andy,

Thanks for getting back. Sorry, I am horrible at explaining the situation !

Let me try again. I do solely reporting, but I do have this job that creates a table of flattened data overnight bunch of updates and a table recreation for a dashboard report. I did this because using a stored proc to get all that data was slow. The report runs quickly off the table I built giving its only reading one table . Our vendor for this data went from a backup restore model once daily to a transactional replication model every few seconds. The same stored proc runs for about 45 minutes(it gets a bunch of data) using the backup restored DB. The transactional rep db take 10+ hours!!! Using the same stored proc. I checked the indexes on both and they are the same. I did a snapshot of the replicated db , I now know that wouldn't work. I also tried to back up and restore the replicated db to another copy , but still takes 10+ hours. I guess I really dont know enough about the differences in replication and backup restore. Is there a way to get a copy of the replicated DB to look like a backup restore? I'm open for any ideas.. thanks for listening

Andy sql SSCrazy Eights Points: 9484 More actions · Answer 4

Where is the table being created? In the Subscriber database? Does the table also exist in the Publisher database? (and does it get replicated?).

Have you dug into what is happening during the 10-hour execution? Are there blocks, locks, waits, etc?

For the scenario you describe, maybe Log Shipping is a better solution. Just a thought.

Andy

jbalbo SSCertifiable Points: 7580 More actions · Answer 5

I write the table to a DB I store the Stored procs. Just read from the replicated DB

I'm definity getting waits I'll start reading about log shipping