SQL Server "fix" query for Hash Match instead of Nested Loops

Question

At the moment I have a view that is running very slow. It takes about 3 minutes to execute. This suddenly happened.

To debug what was going on I used an old dataset and compared both queries. On the old dataset the view returns the data within a second.

I noticed one difference: the current dataset runs on "nested loops" while my old dataset runs with a Hash Match.

You can see that in the picture:

I'm aware of the fact that you simply cannot "force" it to use Hash Match as I saw in other topics, yet I'm having a really hard time finding out why its going for a nested loop.

Is this because something in my dataset is not unique?

How can I fix my current dataset to start using this Hash Match again?

Answer 1

If you are going to this you should at least use the OPTION ( HASH JOIN ) syntax instead. Using the LEFT HASH JOIN syntax implicitly enforces the join order.

This is not desirable for hash joins where normally you would expect the smaller of the two tables being joined to be the build input, the top-most table in the execution plan. Using the option syntax allows the optimizer to decide at run-time which is the best join order. See these two articles for further reading:

http://blogs.msdn.com/b/craigfr/archive/2006/08/10/687630.aspx

https://msdn.microsoft.com/en-GB/library/ms173815.aspx

Answer 2

Ill just answer this so it can be 'closed'.

The solution was from @Mark Sinkinson

I could force the Hash Match via

LEFT OUTER HASH JOIN

While I permanently fixed it by updating my statistics via

UPDATE STATISTICS table_or_indexed_view_name