1) IIRC, it is mandatory to update all statistics with FULLSCAN when upgrading from SQL 2005. You could be getting a horribly bad plan on the delete.

2) use sp_whoisactive...

Well, I think you are correct - it does seem to be odd behavior. Unfortunately it is so odd that I cannot offer any insight without being on the...

RTaylor2208 (11/15/2013)

---

TheSQLGuru (11/14/2013)

---

Please don't just read a few blog posts, run a few forum questions and then set off doing AGs!!! NOTHING good will come of that! This...

1) exactly what query are you using to identify the signal waits (and waits in general)? My guess is something like Glenn Berry's which is since server uptime or...

Please don't just read a few blog posts, run a few forum questions and then set off doing AGs!!! NOTHING good will come of that! This is complex...

Nevyn (11/14/2013)

---

To DBAs, this isn't important, because *we* know that identity columns are arbitrarily assigned. However, the users of the report in question are accustomed to seeing mostly consecutive values...

In case you aren't UNION will do...

You have views and functions in play here, you haven't given us any information about the tables involved, the indexing on those tables, the data value skew situation, row counts,...

krypt0rg (11/14/2013)

---

Thank you. That suggestion was extremely helpful. I reduced the AppPool min memory to 40% and DBCC works like a charm now. I'll see if i can leave it...

You can actually just do the UNION ALL stuff and your calculations without a CTE too.

shew (11/14/2013)

---

>>Why is having no gaps in what is probably an artificial primary key (column without meaning) important?

To DBAs, this isn't important, because *we* know that identity columns are arbitrarily...

I didn't say you set the min memory for the dbcc group. I think it could be related to the 70% min for the app group. Reduce that...

GREAT catch Ed! I focused on just the memory leak situation and zoomed right over the obvious lack of MAX MEMORY cap! Personally I would start with something a...

GilaMonster (11/14/2013)

---

Remove the hint unless there is no other way to get the optimiser to choose the index that you know to be best, you know why the index you're...

When it is hung, run sp_whoisactive to see if some peculiar wait shows up.

Also, can you restore the log file MANUALLY?