A little under a week ago on the 25th of September a hotfix (2999809) was released that pertained to poor query performance when a query contains table joins in SQL Server 2014.

The poor performance may show up if all the following apply:

The concept of a hash join is explained very well here, but to summarize there are two phases;

If the hotfix is not applied, during a probe scan of a hash join, SQL Server will not use the complex bitmap filter to eliminate CCI segments. Complex bitmap filters were implemented as part of the improvements made to columnstore in SQL Server 2014. This whitepaper from Microsoft Research/SIGMOD goes into some details about hash joins (section 5.2) and both complex and simple bitmap filters (section 5.3).

In order to see these benefits the hash join has to be running as a batch mode operation (parallelized) as opposed to row mode. If you’re unfamiliar with batch/row processing modes then reading the whole of section 5 in the paper will be a decent place to start. If you find whitepapers a little dry… and who doesn’t… then this post (plus demos!) will help a great deal.

So to summarize; if your queries do fall into the category outlined at the beginning of this post it may be worth deploying this hotfix to a test instance and testing the hotfix. However I am sure that it will be part of a future Cumulative Update/Service Pack.