在SQL Server 2012中，对存在ColumnStore Index的Table进行查询，出现WaitType：HTMEMO 和 HTBUILD

我修改了查询条件，将 or lt.Int_Col2=bt.Int_Col4 条件删除，再次执行，查询结果秒出。

上网Google了下这两个WaitType

HTMEMO (HT = Hash Table, MEMO = memory)

HTBUILD (HT = Hash Table, BUILD= build)

This wait type (and the other HT* waits) is when a thread is waiting for access to the shared hash table used during batch-mode processing. SQL Server 2012 used to use a hash table per thread and SQL Server 2014 now uses a shared hash table. This change was made to reduce the amount of memory required for the hash table, but comes at the expense of these waits when synchronizing access to the hash table. Typically these waits occur when queries involve columnstore indexes, but they can also occur without columnstore indexes being involved if a hash operator runs in batch mode.

摘自《What’s that HTDELETE wait type?》

It now uses one shared hash table instead of per-thread copy. This provides the benefit of significantly lowering the amount of memory required to persist the hash table but, as you can imagine, the multiple threads depending on that single copy of the hash table must synchronize with each other before, for example, deallocating the hash table. To do so, those threads wait on the HTDELETE (Hash Table DELETE) wait type.

在SQL Server 2012中，对存在ColumnStore Index的Table进行查询，出现WaitType：HTMEMO 和 HTBUILD

标签：