New and Enhanced Features - SQL Server 2012 (What's New in SQL Server 2012) - Part 4

Scalability and Performance

Scalability and performance enhancements enhancement is one of the important parts for SQL 2012. There are many new features and enhanced features available with SQL Server 2012, which improve the performance, like xVelocity helps enable a generational leap in performance across data warehousing and business intelligence.

1.      Columnstore Indexes: This new feature is completely unique to SQL Server. They are special type of read-only index designed to be use with Data Warehouse queries.

Basically, data is grouped and stored in a flat, compressed column index, greatly reducing I/O and memory utilization on large queries. Unlike regular indexes or heaps, which store data in B-Tree structure (in row-wise fashion), the column store index stores data in columnar fashion and uses compression aggressively to reduce the disk I/O needed to serve the query request.

This new index combined with the advanced query-processing enhancements offer blazingfast performance optimizations for data-warehousing workloads and other similar queries.

In many cases, data-warehouse query performance has improved by tens to hundreds of times.

xVelocity in SQL Server 2012: No more Vertipaq, it’s now called xVelocity in-memory technologies.

This is an umbrella name for all the in-memory technologies. This includes re-branding of the Vertipaq engine that runs inside PowerPivot and Analysis Services 2012 tabular to “xVelocity in-memory analytics engine” and the term xVelocity will be used to refer to the Column Store Indexes feature in SQL Server 2012 database.

In SQL Server 2008 R2, Microsoft released PowerPivot for Excel which introduced an in-memory analytics engine called VertiPaq.

Vertipaq enables Excel to process hundreds of millions of rows with sub-second response times on desktop hardware.  This engine uses in-memory column-oriented storage and innovative compression techniques to achieve these remarkable results.

In SQL Server 2012, Microsoft has taken the same Vertical engine and integrated it into Analysis Services. The results have been staggering with scan rates up to 10s of billions of rows per second on typical server hardware.  Customers who previewed SQL Server 2012 have also experienced compression ratios in excess of 100 times.

Usage Considerations: While xVelocity memory optimized column store indexes can provide big gains, there are some usage aspects to consider.

One of the ways that Column store indexes are memory optimized is through efficient use of the buffer pool. Column store indexes are paged in and out of the buffer pool similarly to row store pages, unlike other columnar implementations that only work if the entire dataset fits in memory. Since column store indexes are compressed and grouped by column, they typically have a much smaller memory footprint than the row store data.

Column store indexes are read-only; updates aren’t allowed for the indexes or base tables. To update the base table, drop or disable the column store index, do updates, and then rebuild the index.

Column store indexes are most effective with data warehouses using a star schema. You can get breakthrough performance by designing and optimizing for column store indexes. But even without schema or application changes, column store indexes can improve performance which then helps provide higher ROI on new and existing systems.

2.      Online Index Create, Rebuild, and Drop: Indexes containing XML, varchar(max), nvarchar(max), and varbinary(max) columns can now be created, rebuilt, and dropped as an online operation.

3.     Up to 15k Table Partitions: I have never attempted more than 5 or 6 partitions on any of the databases I have                    worked. However, it is interesting to know that SQL Server 2012 (Denali) has increased the partition limit to 15,000.        In earlier versions, the number of partitions was limited to 1,000 by default.