To be honest the vendor won't tell use. It is transaction replication  for reporting

All I know is they won't turn on nonclustered inxedes in replication

thus the recreation each night

USE [Database];
GO

/****** Object:  StoredProcedure  [dbo].[Non-Clustered_Indexes]    Script Date: 4/16/2020 4:34:21 PM ******/

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[Non-Clustered_IndexesB]
AS
     BEGIN
...

Hi

Yes the overnight refresh drop nonclustered idexes, thats why I would run the job after the refresh

I have multiple

CREATE NONCLUSTERED INDEX [ix....

GO

CREATE NONCLUSTERED INDEX [ix....

GO

when I add the header

USE [dbname];

GO

SET...

Thanks Jeff...

One more dumb question....

This is a small IT Operation.... lol

What do I look for in my queries to tell me what indexes to create ?

If I do create all...

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...

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

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...

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...

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

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...

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...

So our vendor setup transactional replication to our report server.

We were using a daily backup and restore.

I tested with a Stored proc, I ran it against the backuped up DB...

Thanks for all the idea, first time I read about CREATE SYNONYM (I suck at this sometimes!!... lol)

I'm going to learn about this and start applying it ...

The problem is...

Hi Shyan,

Thank you very much, that worked great .

No All I need to find out  is if'ts it's possible to update  these 496 SP and Views in an automated fashion...

Hi

Sorry, i got caught up doing another project....

Honestly, I "sorta"  get what's going on here,

STUFF(
            (
...