Bill Talada (1/26/2016)
Rarely if ever will query plans be available on a database given to a data warehouse or for importing. Otherwise running profiler or looking at DMVs might help.
The worst case is where most every table contains PK and FK columns called ID or similar that potentially join everywhere.
The best case is when GUIDs have been used. It is so much easier to believe a join than when identities are used and every table has the value 1 through 100 in several key columns.
I'm not sure I agree with the 'Rarely if ever' part of your statement.
This may have been true in the past...
However, since the management data warehouse was added to SQL Server many years ago, it's far more common to have a pool of plans to draw upon. This is only going to be more true moving forward with SQL Server 2016 and the Query Data Store.
While I understand not having plans for a database if you do not have access to the server with the production workload.
I do not think that poor planning and administration practices is a reason to ignore a powerful approach to a problem.
This is also one of the first times I have seen "The best case is when GUIDs have been used", usually for performance reasons I've found the opposite to be true. I do think you make a valid point here though for identifying uniqueness.