The single best way to "limit the identiy burn outs" is to stop seeding them at 1!! Always start identities at the LOWEST number available for the numeric type...

try running sp_whoisactive (freebie from sqlblog.com) while the stuck one is hung. Actually I would run it while you have all the concurrent runs going on. I wonder...

DOH!! Coffee clearly hadn't kicked in when I posted! At least I offered up a few improvements ... 🙂

Hardy21 (5/29/2012)

---

Lynn Pettis - you are right.

I have just given one option to change the query.

It will calculate and store filtered records in temp table so during delete, it will...

Brian Souder (5/16/2012)

---

I have a client with a Point of Sale system that the vendor of the PoS has left on a woefully undersized server. The vendor's recommendation was...

SQL Kiwi (5/27/2012)

---

htiteuf (5/23/2012)

---

With PDW AU3, I know stored procedures are supported but some functions in the SMP SQL Server product have not been implemented in SQL Server PDW, Transact-SQL...

Welsh Corgi (5/24/2012)

---

SQLKnowItAll (5/15/2012)

---

Assuming it is indexed properly, 10 million records should not be an issue. Let's say the join condition is called ID... Just do a select from...

Jayanth_Kurup (5/25/2012)

---

While i am enjoying this dicussion I wanted to point out there is a more obvious benefit to partioning than just the elimantion of unwanted partitions. Lock escalation occurs...

SOOOO many things could be at play here - you likely need to get a professional involved for a few hours to identify what the root cause is. Here...

Sean Lange (5/24/2012)

---

TheSQLGuru (5/24/2012)

---

Sean Lange (5/24/2012)

---

TheSQLGuru (5/24/2012)

---

Actually Grant I don't see even the slightest hint of a question from the OP - it is just a bunch of code. ...

Sean Lange (5/24/2012)

---

TheSQLGuru (5/24/2012)

---

Actually Grant I don't see even the slightest hint of a question from the OP - it is just a bunch of code. No request about...

I updated my post to include the connect bug link.

Here are my findings:

select MAX(mycol) from mytable_unpart where id<=10

NC INDEX scan

Table 'mytable_unpart'. Scan count 1, logical reads 283

select MAX(mycol)...

Gullimeel (5/24/2012)

---

I had put a real time example...

chandan_jha18 (5/24/2012)

---

TheSQLGuru (5/24/2012)

---

The left join on the MIN is going to suck in any case, but these are the real killers here I bet:

WHERE DsLocationCode = CAST(term.TerminalID AS VARCHAR)

AND...

The left join on the MIN is going to suck in any case, but these are the real killers here I bet:

WHERE DsLocationCode = CAST(term.TerminalID AS VARCHAR)

AND (sup.SupplierCode LIKE...