Database Reference
In-Depth Information
A columnstore indexes maintenance strategy should depend on the volatility of the data and the ETL processes
implemented in the system. You should rebuild indexes when a table has a considerable amount of deleted rows
and/or a large number of partially populated row groups.
It is also advantageous to rebuild partition(s) that have a large number of rows in open delta stores after the ETL
process has completed, especially if the ETL process does not use a bulk insert API.
SQL Server 2014 Batch-Mode Execution Enhancements
Even though we have not specifically discussed how to write queries that trigger batch-mode execution in SQL
Server, it is impossible not to mention that SQL Server 2014 has multiple enhancements in this area relative to
SQL Server 2012. Only few operators in SQL Server 2012 can support batch-mode execution. As an example,
batch-mode execution in SQL Server 2012 does not support any join types with the exception of inner hash joins;
it does not support scalar aggregates, nor does it support the
union all
operator, and it has quite a few other
limitations.
A large number of those limitations have been addressed in SQL Server 2014. Now batch-mode execution
supports all join types and outer joins, explores different join orders during the query optimization stage, and
supports scalar aggregates and the
union all
operator. Moreover, the execution algorithms for various operators have
been improved. For example, the hash join operator in SQL Server 2014 can now spill to
tempdb
without switching to
row-mode execution, which was impossible in SQL Server 2012.
Let's look at an example and run a query with a scalar aggregate, as shown in Listing 35-7, against the database
that we created in Listing 34-3 in the previous chapter.
Listing 35-7.
Batch-mode execution: Test Query
select sum(Amount) as [Total Sales]
from dbo.FactSales;
When you run this query in SQL Server 2014, you will see an execution plan that utilizes batch-mode execution,
as shown in Figure
35-11
.
Figure 35-11.
Execution plan of test query in SQL Server 2014
Unfortunately, SQL Server 2012 is unable to use batch-mode execution with scalar aggregates, and it produces
the execution plan shown in Figure
35-12
, which is significantly less efficient.
