Not seeing the query or the structure, my answer is going to be vague.

In general, yes. Situationally, a seek is better than a scan. However, it really depends...

There isn't a way to do this with backup and restore. The entire idea of the backup is that it is a full copy in every possible regard of the...

If something is difficult, the best possible solution to that difficulty is to just not do it... said no one who accomplished things.

Yes, choosing the right clustering keys...

So, without seeing the details, this is a speculative answer.

All deadlocks are fundamentally performance issues. If the transactions clear fast enough, you'll never see a deadlock. So, if...

I don't know SentryOne well enough to tell you what it shows.

You can use extended events to capture recompile events. This blog post will show one...

This is a huge topic.

First, I'd suggest capturing query behavior using extended events. You can see all the calls made to the database and determine which specific calls...

That warning is only a problem when you're filtering on the values. If you're simply retrieving the columns (JSON or not), it shouldn't negatively affect you. I'd still follow Lynn's...

This is a long and involved topic. I'll try to keep it short and focused. A clustered index is not simply an index. It also defines the storage of the...

If it's blocking itself is the query going to parallel execution? Check the execution plan to see what's going on with the query.

If you're upgrading from anything prior to 2014 to 2016 or greater (and don't ever upgrade to 2014), follow this simple set of steps:

Restore database
Leave compatibility level...

I'd experiment with making it a JOIN instead of an exists, but there's nothing inherently evil in the code that I can see. Did they give you a "correct" answer?