Just to know what are the values they passing. And needs to pass different parameters for testing purpose.

I haven't tested yet. But there are some temp tables in the SP. It is also causing for recompile

Thanks to everyone.

Yes.

Sorry my bad some spelling mistake for declared table variable & Updating tablevariable.

Thanks everyone!

Yes. I am aware of Go statement but here I am not using any batch terminator.

Declare @Myvar Tab(Id int, Latesttime DateTime)

Insert into @Myvar (Id, LatestTime)

(select Id, Latesttime from TableA...

No here I am talking about the exact duplicates. I have two indexes with only one same column for both. But having one with high user seeks and one with...

Actually I think I am doing some thing wrong here.

Actually I declared variable as table and inserted some values into that table variables from select statement of output from two...

Thank you.

Thanks for you replies.

I am thinking about applying union and exists. I have to see the columns & data types. If it works, I will check the time difference.

Actually his...

Actually I tried with Outer Apply but it increased the execution time. Actually that SP is not taking that much time from SSMS but from the application it is little...

I am not sure why did they use no lock hint. They use without blocking. Might be they don't need accurate data. The dev lead think that we can do...

I am new to the environment. I think might be ID they use for the most of the queries. Is it better to leave that table with out creating any...

So we don't need to consider about the user seeks here. Ok. Thank you.

Yes you are right. Showing that the indexes are using and can't drop when you have partially duplicates.

If we have exact duplicates and both has user seeks

one has...

They ask me to find the exact duplicate and partially duplicate Indexes. Also I need to drop the indexes that are duplicated either exactly or partially. So I have to...