Essential Guide

SQL 2014: Investigating Microsoft's latest database release

A comprehensive collection of articles, videos and more, hand-picked by our editors

SQL 2014 features: Extended buffer pool and columnar indexes

Two major SQL Server 2014 features are its ability to extend the buffer pool and its implementing of clustered columnar indexes for improved querying.

This is the first of two articles detailing important features of the upcoming SQL Server 2014. This one focuses

on extending the buffer pool and implementing clustered columnar indexes in SQL 2014. The second will look at updating partitioned indexes and memory-optimized tables.

Microsoft has released the first public Community Technology Preview (CTP) of SQL Server 2014. Not surprisingly, the CTP introduces many of the new features that will be available in the final product. You can download the CTP from the TechNet Evaluation Center and run it on your own system so you can test some of these SQL Server 2014 features for yourself. This article digs into two of the new capabilities available in the CTP: extending the buffer pool and implementing clustered columnar indexes.

Extending the buffer pool in SQL 2014

Buffer memory plays a key role in storing data pages and reducing I/O. In SQL Server 2014, you can extend the buffer pool to a nonvolatile storage device such as a solid-state drive or an SSD array. SQL Server writes only clean pages to the buffer extension in order to minimize the risk of data loss. By extending the buffer pool, you can realize performance gains for online transaction processing, or OLTP workloads, especially those that are read-intensive.

Implementing a buffer memory extension is simply a matter of modifying the BUFFER POOL EXTENSION setting at the server level for a SQL Server instance. Before doing so, however, you might want to verify the setting's current configuration by querying the sys.dm_os_buffer_pool_extension_configuration dynamic management view, as shown in the following example:

SELECT * FROM sys.dm_os_buffer_pool_extension_configuration

The statement returns a row that provides details about the current setting. The row includes the state_description column, which shows the current state of the buffer pool. By default, the buffer pool extension is disabled, so the state will read BUFFER POOL EXTENSION DISABLED.

You can easily change the setting by running an ALTER SERVER CONFIGURATION statement that sets the BUFFER POOL EXTENSION setting to ON, as shown in the following T-SQL statement:

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME='C:\BufferPool\cache.bpe', SIZE = 5 GB);

When you change the setting to ON, you must provide a path and file name for the cache file. The file requires the .bpe extension. In addition, you must specify the cache size. In the example above, I use a folder and file on my local drive, but in practice, you'll likely specify an SSD or SSD array.

As soon as you run this statement, SQL Server creates the cache file in the target location. Figure 1 shows the file, cache.bpe, on my local drive.

Figure 1: You can specify a cache file to serve as your buffer pool extension.
Figure 1: You can specify a cache file to serve as your buffer pool extension.

That's all you need to do to extend your buffer pool. You can verify your setting by again querying the sys.dm_os_buffer_pool_extension_configuration dynamic management view. This time around, the state_description column will show the value as BUFFER POOL EXTENSION CLEAN PAGE CACHING ENABLED. The view will also return details about the cache file.

If you want to disable the buffer pool extension, you simply run the ALTER SERVER CONFIGURATION statement again, only this time changing the setting to OFF:

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;

After you run the statement, SQL Server will remove the cache file and the buffer pool will no longer be extended.

Implementing clustered columnstore indexes in SQL 2014

Microsoft introduced columnstore indexes in SQL Server 2012. Unlike typical B-tree indexes, a columnstore index uses a columnar format and leverages xVelocity's memory management capabilities and advanced compression algorithms. Columnstore indexes are particularly useful for highly aggregated data warehouse queries. However, SQL Server 2012 supported only nonclustered columnstore indexes that could not be updated, only dropped and re-created.

SQL 2014 has added a new type of index: the clustered columnstore index, which can be updated without dropping the index. You can create a clustered columnstore index as easily as you can any other type of index. For example, suppose we use the following SELECT…INTO statement to add the ResellerSales table to the AdventureWorksDW2012 database:

USE AdventureWorksDW2012;
GO

SELECT *
INTO ResellerSales
FROM FactResellerSales;

On my system, the statement inserts 60,855 rows into the new table. We can now create a clustered columnstore index on that table by using the following T-SQLstatement:

CREATE CLUSTERED COLUMNSTORE INDEX csi_clustered
ON ResellerSales;

As you can see, we specify the CREATE CLUSTERED COLUMNSTORE INDEX keywords and the index name (csi_clustered), and then an ON clause that points to our target table. After running this statement, we can easily verify that the index has been created by viewing it in Object Explorer in SQL Server Management Studio, or SSMS, as shown in Figure 2.

Figure 2: SQL Server 2014 now supports clustered columnstore indexes.
Figure 2: SQL Server 2014 now supports clustered columnstore indexes.

Although a clustered columnstore index is easy to implement, you must work within a number of limitations when defining one. For example, the table cannot include nonclustered indexes, unique constraints, primary key constraints or foreign key constraints. And, of course, there can be only one clustered index. However, you can convert a table with a regular clustered index to one with a clustered columnstore index by using a simple CREATE CLUSTERED COLUMNSTORE INDEX statement, as shown in the following example:

DROP INDEX ResellerSales.csi_clustered;

CREATE CLUSTERED INDEX ix_clustered
ON ResellerSales (SalesOrderNumber, SalesOrderLineNumber);

CREATE CLUSTERED COLUMNSTORE INDEX ix_clustered
ON ResellerSales
WITH (DROP_EXISTING = ON);

To prepare our environment, we first use a DROP INDEX statement to remove the clustered columnstore index that we created in the previous example. Next, we define a CREATE CLUSTERED INDEX statement to build a regular clustered index based on the SalesOrderNumber and SalesOrderLineNumber columns.

After we've set up our environment, we use a CREATE CLUSTERED COLUMNSTORE INDEX statement to drop the regular clustered index and create the new columnstore one. In that statement, after we specify the target table in the ON clause, we include a WITH clause that sets the DROP_EXISTING option to ON. As a result, the existing clustered index will be dropped and the new clustered columnstore index created. Note, however, our CREATE CLUSTERED COLUMNSTORE INDEX statement must use the same index name as the original clustered index.

Now let's turn to rebuilding our clustered columnstore indexes. As mentioned earlier, SQL 2014 lets us do this without dropping and re-creating them. One option is to use the same CREATE CLUSTERED COLUMNSTORE INDEX statement we used in the previous example. SQL Server knows to rebuild the index even though it's a CREATE-type statement.

Another option for rebuilding the index is to use an ALTER INDEX statement, as shown in the following example:

ALTER INDEX ix_clustered
ON ResellerSales
REBUILD;

No magic here. We specify the name of the index and table, as appropriate, but also include the REBUILD clause.

Whether you use a CREATE CLUSTERED COLUMNSTORE INDEX statement or ALTER INDEX statement to rebuild your index, SQL Server updates it without you having to specifically drop it and re-create it. In fact, we can actually test how this works. First, let's use the sys.column_store_row_groups catalog view to check the status of our clustered index:

SELECT * FROM sys.column_store_row_groups
WHERE object_id = OBJECT_ID('ResellerSales');

Figure 3: A columnstore row group shows a compressed state when the index is up to date.
Figure 3: A columnstore row group shows a compressed state when the index is up to date.

Figure 3 shows the results returned by this statement. Notice that the state_description column shows the state as COMPRESSED, with a total_rows value of 60855. This is what we would expect and want. All rows are accounted for and compressed, indicating that the index is up to date. (A row group is an index segment. SQL Server divides a columnstore index into one or more row groups.)

Next, we'll add more rows to the table. The following INSERT statement inserts another 60,855 rows to the ResellerSales table:

INSERT INTO ResellerSales
SELECT *
FROM FactResellerSales;

Figure 4: A columnstore row group will show an open state if the index is not up to date.

Figure 4: A columnstore row group will show an open state if the index is not up to date.

Now let's query the sys.column_store_row_groups catalog view again. This time around, our results include two rows, as shown in Figure 4. Notice that the first row listed shows the state_description value as OPEN and the total_rows value as 60855, but our original row remains unchanged. In other words, we've added additional data to the table, but that data is not yet reflected in our columnstore index.

The next step is to rebuild the index so the new data has been properly incorporated:

ALTER INDEX ix_clustered
ON ResellerSales
REBUILD;

As you saw earlier, the ALTER INDEX statement must include the REBUILD clause in order to update the index. Now if you were to query the sys.column_store_row_groups catalog view, your results would once again include only a single row that shows the state_description value as COMPRESSED and the total_rows value as 121710.

About the author
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at his website.

This was first published in August 2013

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Essential Guide

SQL 2014: Investigating Microsoft's latest database release

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close