White Papers

30 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series

Storage Arrays

The Columnstore Index objects, such as the column segments and dictionaries, do not use the page-

oriented buffer pool. Instead, they use their own memory pool which is created automatically by SQL

Server memory management and designed for handling large objects. Columnstore Index query

operations consume more memory. This can be seen from Figure 10, where six users were run from TPC-

H on the database with no columnstore index and with columnstore index with 92% of RAM (117.7 GB)

allocated to SQL Server by max memory setting. For the database with columnstore index, the test

completed with error 701 “

There is insufficient system memory in resource pool ‘default’ to run this query

”.

This can be seen from Figure 10 presented with arrows, where the available memory reaches close to 0

MByte at times. These values were collected using Perfmon during the test duration.

Available Memory comparison – with and without Columnstore Index at 117.7GB SQL Server Figure 10

memory

Hence the SQL Server max memory setting was reduced to 115 GB from 117.7 GB for the columnstore

index studies. The MaxDOP (Maximum Degree of Parallelism) and Resource Governor settings were left at

defaults. A separate baseline test with no columnstore index (refer to Figure 11) was performed with this

changed memory setting at the SQL Server to compare with the columnstore index tests.

Note: To avoid memory issues like error 701, adjusting the MaxDOP (Max Degree of Parallelism) and

Resource Governor setting or providing sufficient RAM on the servers running SQL Server databases

would help. Refer to http://support.microsoft.com/kb/2834062 and

http://social.technet.microsoft.com/wiki/contents/articles/3540.sql-server-columnstore-index-

faq.aspx for more details. For the tests conducted in this paper, the MaxDOP and Resource Governor

settings were at defaults.

Figure 11 shows the average read throughput and query execution time comparisons for the three tests

performed (explained in Table 5). While implementing columnstore index on the largest partitioned table,

the query execution time reduced by 58% and the average read throughput reduced by 41% compared to

the baseline test without columnstore indexing. The slight increase in the baseline read throughput (906

2000

4000

6000

8000

10000

12000

14000

16000

18000

20000

Available Mmeory (MByte)

Test Duration

SQL Server memory set to 117.7 GB - Available Memory

Without Columnstore Index

With Columnstore Index