Chris-475469 (4/19/2016)

---

Hi Kevin,

I'm not new to being a DBA, just new to my current role and have been given this process to look at.

The INSERT statements are the biggest offenders...

1) What you have posted goes WAY WAY WAY WAY beyond free help from a forum.

2) You need to find out which of those steps is taking the longest...

tcronin 95651 (4/19/2016)

---

one of the problems before was the PLE as almost zero, new server has 512 gig of RAM. Old server CPU was never really above 45%. ...

Atif-ullah Sheikh: why would you want to pay the price of building two clustered indexes on (potentially hugely fat) temp tables only to hit them once each? That is almost...

Like I said, you may not be able to do anything about this if the root cause is that your old box had poor IO and the new one is...

At 11:09am two days ago I gave the OP 2 things to try (although...

tcronin 95651 (4/18/2016)

---

Will start checking for recompiles, this is a fairly old code set from vendor way to hard to describe. ALso seeing a lot of deadlocks which did...

tcronin 95651 (4/18/2016)

---

had old 3rd party oltp system on older HP server with 64 gig of memory. This weekend went to brand new super HP server (512 gig of...

If the function you mention is a Scalar or Multi-StatementTVF UDF then those will void the use of parallelism, which clearly can make things slower with the volume of data...

Completely disagree with the first sentence. And even more vehemently...

ScottPletcher (4/18/2016)

---

THAT IS A...

I tell clients to only run statement completed profiler traces for a VERY short period when they want to see just how truly horrific Scalar and Multi-StatementTVF User Defined Functions...

Jeff Moden (4/15/2016)

---

Heh... wow... this thread certainly jumped the track. 😉

The original post is pretty clear. Everything flies when the last condition is removed even though a...

ScottPletcher (4/15/2016)

---

TheSQLGuru (4/15/2016)

---

ScottPletcher (4/15/2016)

---

The simplest rules is:

Never use ISNULL in a WHERE or JOIN clause.

Whether the column is nullable or not is irrelevant.

The proper way to code this:

where isnull(SalesOrderID,-1) =...

Lynn Pettis (4/15/2016)

---

Just one thing I saw:

case

...