title: Join large table to small in SQL Server with clustered columnstore index takes too long
body: <p>I am experiencing very slow performance when trying to join 2 tables: one has 39M rows, the other 10k (35 sec). This runs on Azure SQL Premium instance, which is very decent server</p>
  
  <pre><code>select m39.* 
  from [Table_With_39M_Rows] m39 
  inner join [Table_With_10K_Rows] k10 on m39.[Id] = k10.[Id] 
  </code></pre>
  
  <p>even a <code>count(*)</code> takes around 10 seconds</p>
  
  <pre><code>select count(*) 
  from [Table_With_39M_Rows] m39 
  inner join [Table_With_10K_Rows] k10 on m39.[Id] = k10.[Id] 
  </code></pre>
  
  <p>Here are the table details:</p>
  
  <ul>
  <li><p>Table <code>[Table_With_39M_Rows]</code> has around 39 million rows (50 columns) with a clustered columnstore index:</p>
  
  <pre><code>CREATE CLUSTERED COLUMNSTORE INDEX CCI_Table_With_39M_Rows 
     ON Table_With_39M_Rows
  CREATE UNIQUE NONCLUSTERED UNCI_Table_With_39M_Rows_Id (Id ASC)
  </code></pre></li>
  <li><p>Table <code>[Table_With_10K_Rows]</code> has around 10k rows (50 columns) and <code>Id</code> as the primary key</p>
  
  <pre><code>ALTER TABLE Table_With_10K_Rows 
      ADD CONSTRAINT PK_Table_With_10K_Rows 
      PRIMARY KEY CLUSTERED([Id] ASC)
  </code></pre></li>
  </ul>
  
  <p><a href="https://i.stack.imgur.com/isN8G.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/isN8G.png" alt="enter image description here"></a></p>
  
  <p>Clustered ColumnsStore index scan takes 99% and slows everything down.</p>
  
  <p>How can I optimize this particular join? What indexing strategy should I employ?</p>