Please note: 12 year old thread.

Update Stats doesn't...

You said earlier

How did you identify that the session causing the blocking did not have an open transaction?

To diagnose blocking you need to investigate locks and transactions,...

Honesty, integrity, able to ask for help when they're stuck.

I don't expect someone to know everything, no one does. I do expect someone to be able to admit when they're...

Locks are released either when the statement completes or when the transaction commits. The only way a sleeping connection can be holding a lock (other than the normal database lock)...

Check for open transactions. If those sessions are starting transactions and no committing, their locks will be held until thye commit, rollback or disconnect.

The risk is that when the SSD fails, your SQL Server instance shuts down and you won't be able to restart it until you replace the SSD or move TempDB...

Unique indexes should never be considered unused as they are enforcing the uniqueness of the column(s).

Maybe this will be of some help: https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/

Grant Fritchey (3/6/2014)

---

MysteryJimbo (3/6/2014)

---

Koen Verbeeck (3/6/2014)

---

MysteryJimbo (3/6/2014)

---

Koen Verbeeck (3/5/2014)

Pfff, than you have never been in Belgium before 😀

If we wouldn't be having the most traffic jam congested cities in the...

Maybe. Is it performing inadequately? Do you have to filter on the expression or can you filter on the raw columns instead without returning incorrect results?

Other than mis-placed commas, reserved words as table names, the where clause 'problem' is solved by putting the expression in the where. General hint, don't use column names as alias...

I'd probably start with the Virtual File Stats DMV.

Good, acceptable and worse for IO volumes is completely dependent on your hardware and application, there's no universal figure for how many...

Jeff Moden (3/5/2014)

---

If what you say is true, it seems to me that shrinking it to 0 wouldn't work everytime (at least for me, it has) in the SIMPLE recovery...