Performance Improvements - Getting Started with SQL Server 2014 Administration - page 79

As you can see, we have two rows affected and the table has been updated. Now, turn
on the execution plan (use the actual execution plan, not the estimated execution plan
option) and run the following SELECT statement. Turn on the include actual execution
plan to see whether the columnstore index is used to satisfy the following query:
SELECT * FROM t2
As shown in the following screenshot, the execution plan will show a columnstore
index scan to satisfy the query:
Buffer pool extensions
Buffer pool extensions allow you to make use of solid-state drives as extra RAM
on your database server. They provide unified integration of a nonvolatile random
access memory (solid-state drive) extension to the Database Engine buffer pool,
which can significantly improve the I/O throughput. Buffer pool extensions are an
Enterprise edition feature, so you have to pay the premium price for SQL Server
Enterprise Edition to use this feature.
The main goal of a SQL Server database is to store, retrieve, and manipulate data.
Therefore, you should expect higher disk access and disk I/O on many production
systems. These disk storage operations can consume many server resources and take
a relatively long time to complete; this delay can be the cause of performance issues
that are caused solely by the I/O throughout. SQL Server, in order to counteract the
delays that I/O operations can cause, has always tried to have an efficient I/O. It
does this by making use of the memory or RAM. The buffer pool serves as the main
memory allocation source for SQL Server. How the buffer pool is controlled and
managed is a key component in achieving this memory efficiency and thus reducing
the need for costly I/O operations. The buffer management component consists of
two mechanisms: the buffer manager to access and update database pages, and the
buffer pool to reduce the database file I/O.