That counter is a cumulative count of recompiles. It's probably worth looking at in your situation. To see the affect of recompiles, I'd set up a server side trace and...

Gianluca Sartori (5/11/2010)

---

The only downside I see in using hints is that they have statement scope, not procedure scope. If I remember right, there's an issue still not fixed in...

Without the things that Gail asked for the only suggestion I can make is, reduce the size of the query. Any query 2000 lines long is probably going about stuff...

paul.goldstraw (5/11/2010)

---

Your English is fine 🙂

We did look a long time ago at what plans existed for the procedure. There were two and one was always called by the...

Robert Frasca (5/10/2010)

---

I applied the covered index to the Patient_Info_Fields table as a test on the client's machine and it made the problem go away. I've recommended it as...

Jeff Moden (5/10/2010)

---

There is such a thing as carrying too many tools on your "toolbelt" and...

Several of those queries are loading data into temporary tables and then loading the same data into permanent tables. Why? Can't they just run the query to move the data...

ankur.hooda (5/11/2010)

---

Hi,

For backup and recovery stratergy,

Consider following

1) how heavily are your db updated--> helps to decide log file size

2) size of db

3)how much db size increase on daily --->basis helps...

I'd dive in, but Gail's done a better job of keeping things clear than I would have.

The original post sounds like an issue around parameter sniffing and your issue sounds...

kastros.george (5/10/2010)

---

use

option keepplan

or somthing like that

i think the problem is that sql server tries to generate an execution plan, aby trying to estimate row size and row count

Use...

It's hard to know for sure, but I'd check the default ANSI settings on your connections. If they're different that could affect how that procedure is processed.

My next guess...

I would suggest you get the execution plan for the query and take a look at it. You're probably getting a table/clustered index scan, or possibly and index scan, instead...

What you described is blocking. Deadlocks occur when two processes attempt to generate locks on resources that the other process has locks on, usually in opposite order, so session_id =1...

It's hard to say why you'd see to plans on the same machine, but I'd have to assume it probably had something to do with statistics or possibly ANSI settings...

Robert Frasca (5/10/2010)

---

Grant Fritchey (5/10/2010)

---

I...