Chapter 6. Performance Improvements

In Chapter 3, In-Memory Optimized Tables, and Chapter 4, Delayed Durability, we looked at two of the new performance features in SQL Server 2014 that can help our systems perform more efficiently. There have been some other improvements and features added in this latest version of SQL Server that can also, if used correctly, help improve the performance of our databases. In this chapter, we will briefly look at some of these improvements and how we can make use of them to help us get the most out our databases.

We will be discussing the following topics:

• Partition switching and indexing

• Columnstore indexes

• Buffer pool extensions

• Cardinality estimator

• Statistics

• Resource Governor

Partition switching and indexing

With SQL Server 2014, it is possible for individual partitions of partitioned tables to be rebuilt.

In the following code example, we will rebuild a single partition, that is, partition number 5 of the IX_GETEST partitioned index on the dbo.t1 table:

ALTER INDEX IX_GETEST
ON dbo.t1
REBUILD Partition = 5

The ONLINE = ON option can also be used and now contains a further switch WAIT_AT_LOW_PRIORITY option that will allow you as a DBA to specify how long the rebuild process should wait for the necessary locks. The WAIT_AT_LOW_PRIORITY option will also allow the DBA to configure the termination of blocking processes related to the rebuild statement. This can be used in combination with the REBUILD Partition option.

Columnstore indexes

Columnstore index is a technology used for storing, retrieving, and indexing using the column data as opposed to the traditional row-based formats. Columnstore indexes were first introduced in SQL Server 2012 with certain limitations, and some of the limitations have been removed in SQL Server 2014. In SQL Server 2014, there is support for both clustered and non-clustered columnstore indexes, which is a change from SQL Server 2012 as it only supported non-clustered index columnstore indexes.

The SQL Server columnstore index differs from more traditional index types as it stores and manages data using the column-based data storage and column-based query processing. Columnstore indexes were initially designed to work well for data warehousing and decision support environments. In these types of environments, it's not uncommon that bulk loads run during an overnight batch processing job, when users are not accessing the system. During peak hours, the system supports read...

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...

Cardinality estimator and query plans

The cardinality estimator has been redesigned in SQL Server 2014. It has been redesigned in order to improve the quality of query plans and thus improve the query performance. The new cardinality estimator includes assumptions and algorithms that work well on modern transactional systems (OLTP) and data warehousing databases. Microsoft has made these changes based on customer feedback. The following is the feedback from a Microsoft customer:

while most queries will benefit from the change or remain unchanged, a small number might show regressions compared to the previous cardinality estimator.

The changes to the cardinality estimator means that you should allow additional testing of your database as you migrate and move them to SQL Server 2014 to ensure that this change does not affect your database performance in a negative way.

Statistics

One of the problems while updating statistics on large tables in SQL Server is that the entire table has to be scanned, for example, while using the WITH FULLSCAN option to scan the entire table, even if only recent data has changed. This is also true when using partitioning. Even if only the newest partition has changed since the last time, the statistics are updated. Updating the statistics again requires a scan of the entire table, not just the current partition. This scan option includes all the partitions that didn't change. You can now update statistics incrementally with the introduction of SQL Server 2014, which can provide help with this problem.

The CREATE STATISTICS option and related statistic statements now allow for individual partition statistics to be created and updated using the INCREMENTAL option. Other related statements that allow or report incremental statistics include: UPDATE STATISTICS, sp_createstats, CREATE INDEX, ALTER INDEX, ALTER DATABASE SET, DATABASEPROPERTYEX...

Resource Governor

In the previous editions of SQL Server, the Resource Governor allowed you to specify the limits on the amount of CPU or memory that a process can use as part of the resource pool. In SQL Server 2014, this has now been improved with new Resource Governor settings to include the I/O activity too. In SQL Server 2014, you can use the new MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings to control the physical I/Os issued for user threads in a given resource pool.

Summary

In this chapter, we have discussed some of the new performance features of SQL Server 2014 that can help improve your database performance. We looked at partition switching and indexing, discussed some of the improvements in columnstore indexing, and looked at the enhancements in the Resource Governor and how changes to the cardinality estimator work. Hopefully, there is something that you can make use of from these improvements in your databases to improve their performance. As you can see, SQL Server 2014 has some great new features for the DBA. These new features will allow the DBA to implement a more robust and highly available production environment. The enhancements and changes to some of the SQL Server performance features, namely In-Memory Optimized tables and Delayed Durability, provide the DBA with an additional set of tools to get their databases performing at an optimal level.