For question #1, just do a test.  I think that you'll find that the stats take a lot longer because Index Rebuilds only affect/rebuild the columns they...

For question #1, just do a test.  I think that you'll find that the stats take a lot longer because Index Rebuilds only affect/rebuild the columns they actually use.  Stats...

Rebuilding an index does two things. First, since you're literally rebuilding the index, it reorders the pages and the rows on those pages. This has a benefit in that...

I wasn't sure about it. They were there...

Yes. Its a small table.  Question is , when there is 0 rows in the table, and when we say SELECT * FROM <Tname> , why SQL Server...

It can, yes. Ghost cleanup can also block you.

The vendor can't leave this with you. You can't change the code, and the code needs to be changed. You also...

Yes. Its a small table.  Question is , when there is 0 rows in the table, and when we say SELECT * FROM <Tname> , why SQL...

So, yeah, about what I expected. A clustered index scan and nothing else.

So, your #1 problem query is SELECT * from a table with two rows in it?

Dude, go...

For now, rebuilding all the indexes on the tables every 1 hour is giving some relief. query returns in few milli secs.

Yes sir. It returns 2 rows...

uploaded Actual execution plan. PFA zip file.

What do the execution plans look like? What do the queries look like. There's not a 'run faster' switch that can be flipped here. Without some understanding of the...

What difference will it make if...

What difference will it make if I...