No...

winash (10/22/2010)

---

If you right click on the SELECT operator on the plan and choose "Properties" there's a "Reason for Early Termination of Statement Optimization" that has the value "Time Out"....

Except that you're on 2005 and I have no...

Actually I'd save both for further analysis and backup.

Then I'd clear only the bad one and see if everything is fixed.

Grant Fritchey (10/22/2010)

---

Looking at the plan... First thing, it's timing out on the optimization process, which is an issue. The only way to reduce that is to simplify the query,...

Also if you are using dynamic sql, different accounts mean different permissions hence possibly different "best" plans.

At the very least it can be considered 2 distinct requests if you forgot...

It still means different plan.

For sql server those are 4 distinct queries :

exec dbo.spProc

exec spProc

exec Dbo.spProc

exec dbo.SPPROC

Depending on what the parameters of the first call were, that's the plan...

So you're working for free since then? :w00t:

Ninja's_RGR'us (4/30/2008)

---

Sherbrooke, Québec, Canada (90 minute south of Montréal).

It was more a contract than a job... but I'm sure I can find more of those if I need to... but...

Do a simple test in your test proc on prod.

Add with no lock or readuncommitted.

That will tell you if you are experiencing locking.

You way also be waiting on disks, cpu...

krypto69 (10/21/2010)

---

okay so I followed your advise ...ran the execution plan on both prod and stage boxes..

On my prod box (query 4) I have a clustered index scan..cost of 81...

Have you tried running it with different parameters to try to get the "fast" plan back?

Once you get it back you can always use plan guides or always force that...

AJHall (10/22/2010)

---

This disconnect has become niche as I’m aging. I often find myself explaining, especially in this economy, that firing a high salary IT worker might be more crippling to...

What about this forum?

http://www.sqlservercentral.com/Forums/Forum1550-1.aspx

We need way more info than that to optimize this query.

We need table definitions (create table scripts) along with all the keys, indexes and triggers if any.

We also need sample...