SQL Server 2014 improvements Vs performance …
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. These improvements and how we can make use of them to help us get the most out our databases. Here are some of these help DBA to get most out our database.
1. Partition switching and indexing
2. Columnstore indexes
3. Buffer pool extensions
4. Cardinality estimator
6. Resource Governor
Partition switching and indexing:
With SQL Server 2014, it is possible for individual partitions of partitioned tables to be rebuilt and let start with index.
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.During peak hours, the system supports read-only queries for reports. Columnstore index, if configured appropriately on the correct tables, can be used to achieve up to 10 times the query performance compared to traditional row-based storage and up to 7 times the data compression over the
original data size, number of benefits, including the following:
• Columns often have similar data that is stored contiguously on disks. This can result in high compression rates.
• High compression rates improve the query performance as they have a smaller in-memory footprint; therefore, SQL Server can carry out more query processing in the memory.
• Columnstore indexes can reduce CPU usage. Batch-mode execution is a new query execution mechanism added to SQL Server that reduces CPU usage. Batch-mode execution is integrated with the columnstore storage format. This is sometimes referred to as vector-based or vectorized execution.
• A typical query will often select only a few columns from a table, which can reduce the total I/O from the physical storage, thus improving the overall performance again. Microsoft on the columnstore versions at the clustered columnstore index can now be updated in SQL Server 2014 the clustered columnstore index can now be updated in SQL Server 2014. the clustered columnstore index can now be updated in SQL Server 2014.
Creating a clustered columnstore index We can create a clustered columnstore index for a table using either TSQL or the management studio GUI. Using Management Studio, connect to the instance of SQL Server that houses your database, expand the databases and tables, and right-click on the index folder to choose New Clustered Columnstore Index. It is worth noting here that if you have a normal rowstore-clustered index, you won’t be able to create a clustered columnstore index
When the wizard starts, we will notice that there is no key column. it’s also worth noting that all columns are included columns. It is also worth noting that the clustered columnstore index does not work with other indexes. If another index exits, you won’t be able to create a clustered columnstore index, and if you have a clustered columnstore index, you won’t be able to create another index type. Much like a normal clustered index which sorts and stores the data rows of table, a clustered columnstore index will store the data for the entire table. Clustered columnstore indexes are an enterprise-only feature in SQL Server 2014.
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.
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.
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. Data pages get modified in memory, and the changed data pages are known as dirty pages. The checkpoint process, internal to SQL Server, writes the data pages back to the disk. Memory pressure on the server, along with database checkpoints, will cause dirty pages in the buffer cache to be removed from the cache and written to mechanical disks and then read back into the cache. SQL Server I/O operations are usually small random reads and writes of the order of 4 to 16 KB of data. Small random I/O patterns will incur frequent seeks, which will compete for the mechanical disk arm. This can
reduce the I/O performance and reduce the aggregate I/O throughput of the system. SQL Server, a reasonable approach to solving these I/O bottlenecks was to add more RAM, or alternatively, to add additional highperformance I/O spindles, or a combination of the two. These options are definitely helpful and are still likely to be helpful in SQL Server 2014. However, there are some drawbacks associated with them.
• RAM is generally more expensive than data storage drives and adding extra spindles and disk drives increases capital expenditure in hardware acquisition. This can increase operational costs by increasing the power consumption and the probability of component failure.
• Mechanical disk drives can and will fail eventually.
The buffer pool extension feature allows SQL Server to extend the buffer pool cache by making use of Solid State Drives (SSD). This enables the buffer pool to accommodate a larger database working set, which forces the paging of I/Os between RAM and the SSDs instead of the mechanical disk. This effectively offloads small random I/O operations from mechanical disks to SSDs. Because SSDs offer better performance through lower latency and better random I/O performance, the buffer pool extension can significantly improve the I/O throughput, thus removing I/O bottlenecks and speeding up the performance of a system. As I/O is usually the slowest component
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. 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.
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, sys.databases, and sys.stats options.
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 AX_IOPS_PER_VOLUME settings to control the physical I/Os issued for user threads in a given resource pool.
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.
Microsoft Certified Solutions Associate (MCSA)