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:

  • You query contains an inner join
  • both tables have a Clustered Columnstore Index
  • There is a hash join executing in batch mode
  • the plan decides that a bitmap filter will be selective enough to be useful
  • the bitmap filter used is a complex bitmap filter (filter is made up of multiple columns and data types, whilst simple is a single column filter)

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

  1. the building of the hash table of the left side input (build phase). This is when a bitmap filter is created
  2. finding the matches in the second input (probe phase). This is when the filter is used
The fix pertains to a complex bitmap filter being created and utilized by the hash join. The fix doesn’t specify how or why complex bitmap filters weren’t used but now they will be used if the optimizer feels it will be useful.

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.