Performance improvements in SQL Server 2014

In this article I would like to summarize the following SQL Server 2014 introduces a variety of amazing performance improvements!!

Cache Pool Extension (buffer pools Extensions)

The idea of a cache pool extension is simple: Store page files on very fast storage, such as SSD drives, to extend the cache pool. Cache pool extensions are very handy, and if you can't add more memory to your database server physically, consider using a cache pool extension.

Resource governor (Resource Governor)

The resource Governor was introduced in SQL Server 2008 for the first time, but it is not a mature technology at that time because you cannot regulate I/O operations at the storage level, and you cannot limit the cache pool size for a specified workload group. These have changed in SQL Server 2014, because now you can suppress I/O operations. Limiting buffer pool usage is still not working, but who knows what will happen in SQL Server 2016:).

Lock Priority (lock priorities)

Perhaps you already know that in the Enterprise version, SQL Server provides you with online operations, or I can call them "near-online operations." They are almost online because internal SQL Server still acquires some locks, which can lead to blocking situations. So SQL Server 2014 introduces a lock priority , where if that happens, you can control how SQL Server handles it.

Clustered Columnstore Index (Clustered column store Indexes)

The most popular promotion introduced in SQL Server 2014 is the clustered Columnstore index , which in SQL Server is a stunning new way to handle column storage data. Also, the clustered Columnstore index changes directly-without the skill of partitioning the switch.

Memory-Optimized tables (in-memory OLTP)

Using memory-Optimized tables Microsoft claims that you can increase your workload performance by up to 100 times times. What a cool day! Now everything is stored directly in memory and will not involve your physical storage (including transaction logs if you want to). Another memory-optimized table is based on the so-called lock-free data structure, which means that locks, blockages, latches, and rotary locks all disappear. Of course, the use of this promising method has side effects, there are restrictions ...

deferred transactions (Delayed transactions)

No matter how good your workload is, the last hurdle and bottleneck is the transaction log every time. Because SQL Server uses the pre-write log mechanism (Write-ahead Logging mechanism), the transaction must always be physically written to the transaction log before the transaction commits. But your transaction log is on slow storage and your performance and productivity will be hurt. So SQL Server implements so-called deferred transactions ...

cardinality calculation (cardinality estimation)

In relational databases, cardinality computing is the most important thing, because these cardinality calculations flow into the query optimizer, and its job is to generate a good enough execution plan. In SQL Server 2014, Microsoft has completely rewritten cardinality calculations to overcome some of the limitations, based on historically important components.

Performance improvements in SQL Server 2014