Local variables won't be sniffed, so you avoid parameter sniffing... Unless you're getting recompiles. Then, a local variable also gets sniffed.

Ain't query tuning fun?

May be a data issue. The stuff getting loaded into the temp table is not stored as a date maybe? Hard to know not seeing the data. Check the data...

When say standard SQL, did you hard code passing the filters or use local variables? If you hard coded the values, it's the same as parameter sniffing (it's using those...

It sounds like bad parameter sniffing, but you're going to have to get the plans to be sure. Without the plans, you can look at the statistics for the column(s)...

The best tool for the job, if you want detailed query behaviors, is Extended Events. They are the least cost way to gather detailed query metrics. You only really need...

I was on Poland recently, but no one let me drive a tank... as far as I can recall.

To really deal with this, you need to get some input and insight into the load process. I know you say you have zero control over it, but, that's where...

Let's start with the last question first, yes, an index rebuild will reduce the level of fragmentation.

First question, is fragmentation of indexes bad?

Well, probably not, no. Fragmentation hurts in a...

I'd need to see more specifics around this. There's nothing magically or uniquely bad or dangerous about a LEFT JOIN when manipulating data, so there's got to be more to...

Defining "used" may lead you to different ways of checking this. I would first get this definition so that you can understand what you then need to do to ensure...

Oh, god. It's really Dynamics. People were cracking jokes about this on Twitter.

Yeah, there is literally no easy way around this. Microsoft purchased this horrible piece of software and then...

The only one of those that offers the slimmest of hopes is the log explorer. However, you better not have the log on simple recovery or that's a lost hope...

It won't...

You're missing Availability Groups and then all the third party solutions through software and hardware. Also not mentioned, just backing up the database on one server and restoring to another....

You are in a world of pain. There is literally no easy way out. At all. So very sorry.

If, and it's probably a huge "IF", you are lucky and they...