Using DTS on a Replicated Database

Question

Post reply

Using DTS on a Replicated Database

jbalbo

SSCertifiable

Points: 7583
More actions
April 14, 2020 at 3:48 pm

#3742786

Hi
I've had an issue with queries running slow on a new transnational replicated DB.
I was told that it is slow because I am running against the database directly and need to use DTS to
create another DB to access the data in queries. Would anyone have any links, ideas they can pint me to

Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

I think your request raises more questions in me than answers:

What do you mean by DTS? I sincerely hope you don't mean the old Data Transformation Services from SQL Server 2000 days
I assume you mean Transactional Replicated DB, please correct me if I'm wrong
When you say you are running queries against "the database directly" which database are you referring to? The source or target of the replication?
How large or complex are the queries that are causing problems? Did they cause problems before the replication?

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

Hi Chris

Thanks for getting back. Yes, I do mean Transactional Replicated DB... sorry.

Let me tell you my sad story...

We were getting a backup copy of a DB from our vendor each night that we restored for Reporting Services

The switched\switching over to a Transactional Replicated Db every 5 minutes or so . Which is great so we have updated data

The problem some of my Stored Procedures run super slow when I run it against the Repl Database. I mean from 2 seconds to 4 minutes

Someone told me to create a copy of he DB using SSIS and DTS. Then run the Stored procedures against the new databse

So I'm looking to see where I can start researching this ...

Hope this makes sense.. and thanks

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 3

So - you were using a restored copy of the database and these queries ran in an acceptable time. Correct?

Now - on the new replicated database the same queries take a lot longer to execute. Correct?

If so, the question really should be: what changed - and how would that change affect the query processing? Creating yet another copy of the tables on a fixed schedule may improve the query performance - but you lose the real-time data availability.

The problem is that you are now running against an 'active' database. The replication process must take out locks on the tables being updated from the production system which is going to block your select queries. If you are trying to query a table that is actively being inserted/updated/deleted by the replication process - your query will have to wait...

It might be worth the time and effort to research setting up RCSI on the subscriber. That could remove the blocking by the replication process and allow your queries to perform better. You can start here - https://www.brentozar.com/archive/2014/07/performance-tuning-sql-server-transactional-replication-checklist/ - which lists quite a few options to consider.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

So are you querying the database that is the target of the replication or the source of the replication? I'm still not sure. Also if you already have 2 copies of this data now, the original vendor and the replicated copy, what will generating a third database gain you and how would you do that without having further delays and interference from some sort of data copy process?

I would initially think the problem is in the queries you are trying to run, not with the replication. Querying a database that is updated by replication makes perfect sense and there are many implementations of that without requiring a third database. Are you able to see the waits on these queries and what operations in them are the most expensive?

jbalbo SSCertifiable Points: 7583 More actions · Answer 5

I am querying on the target.

I have added with(NOLOCK) to the query with no luck.

The query I'm testing with runs for 4 minutes on the replicated db and 2 seconds on the backup/restore copy I'm testing with

I have another DB that is using transactional Replication and don't have this issue, it is a smaller DB but also a smaller server)

I really don't know alot about this put I compared the settings and the only difference was on the db that is slow "allow Snapshot Isolation" is true whereas it is false on the one with no issue

doug.brown SSCertifiable Points: 5608 More actions · Answer 6

I would start by checking indexes. Replication usually doesn't copy indexes, so the replicated DB most likely doesn't have indexes that Prod does.

jbalbo SSCertifiable Points: 7583 More actions · Answer 7

Thanks Doug, just checked a few and they all have a clustered, unique, primary key located on PRIMARY

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 8

jbalbo wrote:

Thanks Doug, just checked a few and they all have a clustered, unique, primary key located on PRIMARY

So what indexes are missing? Review the table(s) on the source system and see what indexes they have available. Most likely they are not replicating any of the non-clustered indexes. This makes sense, since the database will be used for reporting it is likely that you would want different non-clustered indexes in the subscriber.

You also have snapshot isolation enabled - but it does not appear you have RCSI enabled. Instead of using NOLOCK - you want to set your queries up to use the snapshot transaction isolation level.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

This is a much better option than NOLOCK - as it insures that your query does not block writes and your query will read data that is consistent from the time it started.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

jbalbo SSCertifiable Points: 7583 More actions · Answer 9

Hi Jeff

Thanks for getting back.

First not to sound dumb, but I don't know much about doing indexing in SQL.. but

I ran a snippet I found and you were correct the replicated DB does not have non-clustered indexes.

for example one table on the backup/restore db has 5 non-clustered where the replicated has 0

So question is, is the lack of the indexes causing the same procs to run slow?

BTW in my testing, a proc Im testing has a select top 1 within the main select and when I remove the inner it is much faster

So if the lack of indexes is causing the slowness, how do I add indexes? Do I need to?

Again sorry fr the lack of knowledge....

jbalbo SSCertifiable Points: 7583 More actions · Answer 10

Hi...

So I took the tables in the SP I used for testing, created a copy of the replicated DB, added the nonclustered indexes to the tables in the copy (of the replicated DB) to match the ones in the backup/restore copy of the DB and it runs MUCH faster down to 1 second for 4+minutes..

So how would I approach this issue, add all indexes to the replicated DB ?

Can I do that, will that screw up replication on the other side(coming in) ?

if I can add the indexes, do I need to do it one by one ir is there a way to copy them from my backed up copy?

Thanks for everyone's help on this, more appreciated than you know !!

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 11

You could have the vendor modify the replication to include the non-clustered indexes for those tables you have identified - or you can add them to the replicated database.

Creating your own indexes should not cause any issues - that is one of the purposes of replication. This is to allow you to create different indexes from what is in production that are more useful for reporting.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

jbalbo SSCertifiable Points: 7583 More actions · Answer 12

I spoke with our vendor and he said he was going to turn on non-clustered index = true....

I'll report back... I'm just hoping it won't slow down replication

jbalbo SSCertifiable Points: 7583 More actions · Answer 13

So this leads me to another question...

My vendors says that they have a plan in place and that I should change my side of the replicated DB like Jeff stated earlier.

Is this a manual process where I look up the Indexes that existed on the old DB or can I copy them all from the old BackedUp Db to the new Replicated one?

Thanks

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 14

It would be a manual process - unless the vendor turns on the non-clustered indexes. Remember, the idea is that you can create different indexes on the replicated database to support your reporting requirements - since most indexes on the production database would be there to support the application.

You probably don't need them all either - just the ones that satisfy your queries.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs