Igor Micev (7/6/2015)

---

Is the table's PK a foreign key in other tables. Are some foreign keys using the CASCADE-ing?

What about triggers?

You can always perform the deletion in batches, let's say...

Have you checked the Server/database Collation?

I'd start by adding the suggested index.

It's informing you of 79% impact.

Not sure it will work with reporting services,

However you could try using the hint OPTION FAST(n) n being the number of rows.

It will return the first n number of rows...

Solved

https://msdn.microsoft.com/en-us/library/dd207004.aspx

I didn't, I'm still investigating what's causing the problem.

Question

Why are the same tables getting cached multiple times?.

I would have...

I know it doesn't make sense. However...

It's not a case of 'which cached table to use', because they're all stripped of their columns (iirc) and data any one will do.

Now it's possible there are too many,...

I think it's session related,

If one temp table is created in a procedure, and the procedure is executed for example 1000 times,

The table is getting cached in the tempdb...

Hi,

The tables are getting explicitly dropped in the procedure.

"not whether someone thinks their crystal ball is better than yours"

I'm thinking its more of a case of this.

Agreed,

However it still needs re-writing as it has a massive overuse of temp tables.

7,000 of them cached in the tempdb.

It was causing a performance issue.

I freed the proc cache and...

Thanks Jacob,

That's exactly what I'm looking for.

Regards

Hi Lynn,

Thanks, sounds about right.

I clearly need to find some time to re-write this procedure.

Regards,

Simon

Ha a simple restore of the tracked DB worked.

Thanks Simon