Por que a subconsulta usa paralelismo e a junção não?

Conforme já indicado nos comentários, parece que você precisa atualizar suas estatísticas.

O número estimado de linhas que saem da junção entre locatione testrunsé muito diferente entre os dois planos.

Estimativas do plano de junção: 1

Estimativas do plano de subconsulta: 8.748

O número real de linhas que saem da junção é 14.276.

É claro que não faz sentido intuitivo que a versão de junção deva estimar que 3 linhas devem vir locatione produzir uma única linha unida, enquanto a subconsulta estima que uma única dessas linhas produzirá 8.748 da mesma junção, mas mesmo assim consegui para reproduzir isso.

Isso parece acontecer se não houver cruzamento entre os histogramas quando as estatísticas são criadas. A versão de junção assume uma única linha. E a busca de igualdade única da subconsulta assume as mesmas linhas estimadas que uma busca de igualdade em relação a uma variável desconhecida.

A cardinalidade das execuções de teste é 26244. Supondo que seja preenchido com três IDs de local distintos, a consulta a seguir estima que 8,748as linhas serão retornadas ( 26244/3)

declare @i int

SELECT *
FROM   testruns AS tr
WHERE  tr.location_id = @i

Dado que a tabela locationscontém apenas 3 linhas, é fácil (se assumirmos que não há chaves estrangeiras) criar uma situação em que as estatísticas são criadas e, em seguida, os dados são alterados de uma forma que afeta drasticamente o número real de linhas retornadas, mas é insuficiente para disparar a atualização automática de estatísticas e recompilar o limite.

Como o SQL Server obtém o número de linhas que saem dessa junção tão errado, todas as outras estimativas de linha no plano de junção são enormemente subestimadas. Além de significar que você obtém um plano serial, a consulta também recebe uma concessão de memória insuficiente e as classificações e junções de hash se espalham para tempdb.

Um cenário possível que reproduz as linhas reais versus estimadas mostradas em seu plano está abaixo.

CREATE TABLE location
  (
     id       INT CONSTRAINT locationpk PRIMARY KEY,
     location VARCHAR(MAX) /*From the separate filter think you are using max?*/
  )

/*Temporary ids these will be updated later*/
INSERT INTO location
VALUES      (101, 'Coventry'),
            (102, 'Nottingham'),
            (103, 'Derby')

CREATE TABLE testruns
  (
     location_id INT
  )

CREATE CLUSTERED INDEX IX ON testruns(location_id)

/*Add in 26244 rows of data split over three ids*/
INSERT INTO testruns
SELECT TOP (5984) 1
FROM   master..spt_values v1, master..spt_values v2
UNION ALL
SELECT TOP (5984) 2
FROM   master..spt_values v1, master..spt_values v2
UNION ALL
SELECT TOP (14276) 3
FROM   master..spt_values v1, master..spt_values v2

/*Create statistics. The location_id histograms don't intersect at all*/
UPDATE STATISTICS location(locationpk) WITH FULLSCAN;    
UPDATE STATISTICS testruns(IX) WITH FULLSCAN;

/* UPDATE location.id. Three row update is below recompile threshold*/
UPDATE location
SET    id = id - 100

Em seguida, executar as seguintes consultas fornece a mesma discrepância estimada versus real

SELECT *
FROM   testruns AS tr
WHERE  tr.location_id = (SELECT id
                         FROM   location
                         WHERE  location = 'Derby')

SELECT *
FROM   testruns AS tr
       JOIN location loc
         ON tr.location_id = loc.id
WHERE  loc.location = ( 'Derby' )