-- The query requires a scan of the whole 100M rows to evaluate the ORDER BY.
-- An index with Destination, Origin, Airline and Tax will help.
-- You *probably* don't need to join the lookups until after the aggregate -
-- this help will speed up the query.
;WITH Preaggregate AS (
	SELECT TOP 10 
		FAF.Destination,
		[CountDestCityCode] = Count(FAF.Destination),
		FAF.Origin,
		FAF.Airline 
	FROM dbo.FactFares AS FAF 
	WHERE (FAF.Tax >= 0) 
	GROUP BY FAF.Destination, FAF.Origin, FAF.Airline 
	ORDER BY Count(FAF.Destination) DESC
)
SELECT 
	DLDest.CityCode AS [DestCityCode],
	FAF.[CountDestCityCode],
	DL.CityCode AS [OriginCityCode],
	DA.AirlineName AS [AirlineName] 
FROM Preaggregate FAF
LEFT OUTER JOIN DimLocation AS DLDest ON FAF.Destination = DLDest.Location_Key 
LEFT OUTER JOIN DimLocation AS DL ON FAF.Origin = DL.Location_Key 
LEFT OUTER JOIN DimAirlines AS DA ON FAF.Airline = DA.AirlineCode