Scalability and Performance Enhancements in SQL Server 2012

The SQL Server product group has made significant modifications to the existing SQL Engine to improve scalability and Performance associated with the SQL Server Database Engine. Some of the main enhancements that allow organizations to improve their SQL Server workloads include the following:

Columnstore Indexes More and more organizations have a requirement to deliver Breakthrough and predictable performance on large data sets to stay competitive. SQL Server 2012 introduces a new in-memory, columnstore index built directly in the relational engine. Together with advanced query-processing enhancements, these technologies provide blazing-fast performance and improve queries associated with data warehouse workloads by 10 to 100 times. In beta tests customers have experienced a 400 percent improvement in performance.

Partition Support Increased to dramatically boost scalability and performance associated with large tables and data warehouses, SQL Server 2012 now supports up to 15,000 partitions per table by default. This is a significant increase from the previous version of SQL Server, which was limited to 1000 partitions by default. This new expanded support also helps enable large sliding-window scenarios for data warehouse maintenance.

Online Index Create, Rebuild, and Drop Many organizations running mission-critical workloads use online indexing to ensure their business environment does not experience downtime during routine index maintenance. With SQL Server 2012, indexes containing varchar(max), varchar(max), and varbinary(max) columns can now be created, rebuilt, and dropped as an online operation. This is vital for organizations that require maximum uptime and concurrent user activity during index operations.

Achieve Maximum Scalability with Windows Server 2008 R2 Windows Server 2008 R2 is built to achieve unprecedented workload size, dynamic scalability, and across-the-board availability and reliability. As a result, SQL Server 2012 can achieve maximum scalability when running on Windows Server 2008 R2 because it supports up to 256 logical processors and 2 terabytes of memory in a single operating system instance.