SQLSERVER reports SLOW on Replication database

Question

SQLSERVER reports SLOW on Replication database

Paul Seeberg 21

Our environment is 2 sqlserver 2017 databases on 2 different but identically set up servers (VM) , using transactional replication between them.

The replication process is working great. Great performance. NO complaints on the replication.

The issue is the reports. We have some complicated reports, that run well on the publisher (seconds) but take a LONNNNG time on the subscriber (6+ minutes) .

What I did first:

stopped the replication. retried the reports, No performance difference.

I then tried:
deleted the subscriber database, restored the production db onto the other server (that was the subscriber) , so it is no longer a subscriber, just a copy of the production database, and the reports ran like they did on the publisher .

we determined the slowness was due to the database being in replicated mode vs standalone database.

What could be causing the reports to run slow when database is in replication mode but run fine when the database is a regular database (no replication) . Any settings or other ideas to check ?

Any thoughts??

Thanks!

Accepted answer

1 additional answer

Your answer

Answer 1

Tom Phillips 17,771

As a default non-clustered indexes are not transferred via replication. The subscriber can have different indexes defined to support different uses, like reporting. The reason your backup/restore worked is likely because you transferred all the indexes.

The fact database being replicated does not have anything to do with your performance. Replication does not affect the performance of the subscriber.

Paul Seeberg 21 Reputation points

2021-08-24T19:05:17.367+00:00

The fact database being replicated does not have anything to do with your performance. Replication does not affect the performance of the subscriber.

Thanks... that is what I figured, the replication itself should not affect performance.

As a default non-clustered indexes are not transferred via replication.

OK.. I assumed all indexes came over.

In theory, if I recreate all the indexes that are on the publisher, it should perform similarly ?

Is there a proper way to include all indexes when creating the snapshot? Or does it have to be scripted after it is replicated ?
Tom Phillips 17,771 Reputation points

2021-08-24T20:01:07.897+00:00

If you want all the non-clustered indexes, go into Publication Properties, select Articles, Then in "Article Properties" select "Set properties for all table articles", then select "Copy nonclustered indexes" to true (you have to scroll up in the list).
Paul Seeberg 21 Reputation points

2021-08-25T00:20:14.1+00:00

Thank you!

I did that. Created a new snapshot and started it up. All the indexes came over this time

I will have to wait til tomorrow to see if that was the fix when the developers try it out.

If this is the cause of the slowness ( am fairly confident, at this time, that it is), I am glad it was a simple fix!
Paul Seeberg 21 Reputation points

2021-08-25T19:24:26.03+00:00

Thank you.

The developers did confirm the reports on the replicated server are performing virtually identically as they are on production.

This helped a lot!

I appreciate all the help!

Answer 2

Seeya Xi-MSFT 16,586

Hi @Paul Seeberg ,

Agree with Tom.
You can try Tom's method. If it doesn't work, please feel free to let us know.

Best regards,
Seeya

Share via

SQLSERVER reports SLOW on Replication database

1 additional answer

Your answer