White Papers

32 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
Figure 12 shows the increase in percentage CPU utilization when using the columnstore index measured
using Perfmon at the SQL Server. Even though the CPU utilization percentage increased, it remained well
below 80%. The increase in CPU utilization is attributed to the data compression and other memory
management operations involved during the columnstore index query processing.
CPU utilization comparisons – With and without Columnstore Index Figure 12
Note: To reduce CPU time, a new set of query operators called batch mode processing, which
processes a batch of rows all at once, occurs when using columnstore index. Standard query
processing in SQL Server is based on a one row at a time iterator model. The query optimizer decides
to use batch-mode or row-mode operators. In SQL Server 2012, only a subset of the query operators is
supported in batch mode. They are scan, filter, project, hash (inner) join and (local) hash aggregation.
The sample SAN HQ results for these tests can be seen in Figure 13 and Figure 14. Figure 13 shows the
pool view in SAN HQ for the baseline test without columnstore index and Figure 14 shows the
columnstore index on the LineItem table while running six users from Benchmark Factory.
15
31
34
43
58
61
0
10
20
30
40
50
60
70
No Columnstore
Index
Columnstore Index
on LineItem
Columnstore Index
on Lineitem & Order
% CPU Utilization
CPU Utilization comparision
Average CPU utilization
Max CPU utilization