I know this is a really old thread, but I ran into this same problem again and wanted to share the solution I'm using. Essentially, what I did is...

NJ-DBA (10/24/2012)

---

it sounds like the open transaction is holding a lock that is blocking the other transaction. You need to either commit, or rollback that first transaction to release...

it sounds like the open transaction is holding a lock that is blocking the other transaction. You need to either commit, or rollback that first transaction to release the...

A couple of problems here. First, oddly enough, it looks like sa doesnt have permissions in testdb... sa is a 'login' which doesnt have a user in the...

I decided I could use execution counts of the top 200 most costly queries (measured by delta total_worker_time daily) as a measure of the "work done" in a day. ...

wolfkillj (8/9/2012)

---

NJ-DBA (8/1/2012)

---

Testing revealed that the primary factor influencing the duration of the “if exists” query is lack of an index with th filename column in the key. Particularly, this...

Basically, yes. Essentially I'm looking to...

ugh efficiency not effiency

Testing revealed that the primary factor influencing the duration of the “if exists” query is lack of an index with th filename column in the key. Particularly, this column should...

Testing revealed that the primary factor influencing the duration of the “if exists” query is lack of an index with th filename column in the key. Particularly, this column...

never goes above 0....

William Melcher (8/1/2012)

---

Have you stepped through your C# code to see when your procedure is called and what parameters are being passed to it? Can you run the stored procedure...

I really dont think it's stats or plan related. I was able to recreate the problem on another machine, taking a lot of variables out of the problem. ...

last one... I really should have waited and put this all as one post...

The app keeps the connection open for the duration of the run and does not do sp_reset_connection.

also, we are aware there is a missing index that would cover the "if exists" query, but I dont think a missing index would make the duration increase over time......