sys.dm_exec_requests is my bestest buddy in the whole world. It's my go to position for this type of scenario. Assuming no long running query or blocking scenario, I might expand...

Further, it's completely dependent on a number of factors that you haven't outlined. You're not comparing apples to apples in this question.

Remember, while it has the word "table" in the definition of Common Table Expression, a CTE is nothing but a query. It's not a table. Comparing it to a temp...

Sure. You can. I'd be cautious about adding unique indexes because if you don't have a matching constraint on the other side, you could get incorrect data input and then......

Jeff has already addressed question 1. On question 2, yeah, most tables ought to have a clustered index. Much of SQL Server's storage and retrieval optimizations are built around the...

There are basically two good ways to get query metrics out of the system. You can query the cache and see what's there for query performance, or you can audit...

Yeah, just run a query on the server that uses RAISEERROR and the appropriate error level. Nothing else needed. It won't hurt the server or any processes on the server.

Any login can have a query that causes blocking. That's just how SQL Server works. Based on what you've described, that's about all I can see. To minimize blocking, go...

Looking it over, the only hope for tuning is, as was already pointed out, to eliminate the key lookup. Otherwise, we're just processing a lot of data and that's going...

Access.

But seriously, Access & ODBC and... yeah... Access.

There must be differences. No such thing as magic. Differing data sets leading to an error, maybe corruption on that database, something. Keep looking and look in and around settings...

If the same code doesn't work, then I'd start checking settings. First, default ANSI settings on the server. Collation on the server and the database. Also check collation on tempdb....

It's usually not a network problem, but it might be. It's usually an issue where the client code is doing row-by-row processing. There is no other wait occurring. It's literally...

UNION is an aggregate function that is basically going to give you unique values. If you want to eliminate them from the query any other way, it's probably through having...

And remember, those costs are just estimated values. They don't represent a literal real-world performance metric. It's just a measure of what the optimizer thinks a given operation will cost...