Lee Forst (1/18/2011)

---

Thank you guys for the confirmation on this. We had a case of a new stored procedure that was put into production. Within a week we...

The thing is, your process is causing the blocking. I'd seek a solution by address the process. I think you're in a "Doctor, doctor, it hurts when I do this"...

I'd refer to Itzik's book as well.

But, while you're waiting for it to arrive, there is some explanation here. Here's a bit more. None of this addresses it directly...

Yeah, without a, pick your time period, 6 months/year/decade worth of transaction monitoring, there is no way to know what data was accessed.

If you're interested in what has been access...

Unless I'm reading this wrong, 20gb is very small as databases go. I agree that seperating out the storage to different filegroups would allow you to break up the backup...

So if I understand the process, you've got a query that inserts into Table A (prosumably selecting from other sources), selects from that table using some kind of aggregate query,...

GilaMonster (1/17/2011)

---

A stored procedure can only have one plan cached at a time (baring different set options), so if you have a case like Grant describes, where different parameters can...

That is an excellent...

dajonx (1/17/2011)

---

Ohh, ok. I understand. Thank you very much for shedding some light on this subject for me. If, by chance, you happen to have any literature...

I'd suggest spending time over at Paul Randal's blog. Here's a link to articles on cache over there.

Try this. Instead of the COUNT statement, use an EXISTS statement. It will perform much better.

IF EXISTS (SELECT * from dbo.tbl_WhateverItWas WHERE Val1 = @Param1 AND Val2 = @Param2)...

Of course,...

It is important to be Ernest.

Why do you think it's not related to management studio? The queries are retrieiving the amount of space available. Couldn't someone be running one of the standard reports from SSMS...

Yes and yes.

When you alter a stored procedure, the plan in cache, if there is one, is marked as invalid, so a new one is created the next time...

You can't access that DMV for a particular database. Best thing you could do would be to run multiple queries against sys.dm_exec_waiting_tasks as your queries are running to determine what...