You have functions around columns in the where clause. Game over until you can eliminate that. In aggregate that is the second worst thing I see in my performance tuning...

Just review your logs. There are any number of blog posts out there on how to do that. See fn_dblog().

Ola's stuff is open source. Just go to the relevant part and grab the code and modify to suit. Or script your own using system tables. Very doable. I don't...

Again not enough information. What was the monitoring interval? If it was 30 seconds you are doing very poorly. If it was 180 seconds then you are waiting one second...

Sturev (5/25/2016)

---

You scared the crap out of me with that second post...

Welcome. Sometimes our brains don't work like we wished they would. 😀

Come now - I know you could have figured this one out by extending what I had!! 😛

WITH t AS (

SELECT ROW_NUMBER() OVER (ORDER BY a1.AFFILIATIONID, a1.BACKAFFILID) AS rownum,

a1.AFFILIATIONID,...

j21283 (5/25/2016)

---

Ah ok thought I was perhaps missing something for the analysis - I've ran a few scripts related to this and this is what we get:

TempDB avg io stall...

Look up DISTINCT in Books Online.

I need all expected outputs for your sample data.

Sturev (5/25/2016)

---

I've been off in force.com land and...

j21283 (5/25/2016)

---

Hi Kevin,

Thanks for your response - this was where I was leaning as well - I will say before the upgrade of these various components our performance seemed to...

You are very welcome. When you have 45000 hours working with SQL Server your brain will probably...

Sturev (5/25/2016)

---

Wow Kevin, that's like light years beyond my sql skills, thanks a ton!

So I have 4 other fields in mix that I need to bring along and your solution...

Kludgy as heck, but it seems to give the desired results. :hehe:

CREATE TABLE #a (AFFILIATIONID varchar(20),

...