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?

There's no easy way to get that done, at all. Instead, you could reseed the identity to start at the max negative value and then let it increment naturally from...

Statistics out of date affect memory because the allocations will be wrong, causing all sorts of issues. If the stats suggest 1 row when 1 million will be returned, the...

I know some of the authors of the Microsoft books. They're good. However, they do "teach to the test". This means it will help you pass the tests. However, it...

Why not just use SQL Dependency Tracker?

It does make the code harder to read though. While it doesn't matter from a technical or performance stand point with INNER JOINs as was already pointed out, it does...

Like Chris, show us the execution plan to get good advice.

One immediate issue,

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning