I don't see in your query the need to do a join. Try instead using WHERE.
SELECT TOWNLANDS.TL_ID,TOWNLANDS.Shape FROM dbo.TOWNLANDS as townlands with(index(FDO_Shape)) WHERE townlands.Shape.STIntersects (Shannon.Shape.STBuffer(2.0))=1
In the original query the join appeared of no benefit to the result; I didn't see any columns from the Shannon table in the select line. Therefore, it seems like extra work.
