Scalability and Performance Enhancements (Database Engine)

SQL Server 2012

Scalability and performance enhancements in the Database Engine include columnstore indexes.

The SQL Server 2012 introduces a new data warehouse query acceleration feature based on a new type of index called an xVelocity memory optimized columnstore. This new index, combined with enhanced query processing features, improves data warehouse query performance by hundreds to thousands of times in some cases, and can routinely give a tenfold speedup for a broad range of decision support queries. This can allow end users to get more business value from their data through fast, interactive exploration. IT workers can reduce development costs and ETL times because columnstore indexes limit or eliminate the need to rely on pre-built aggregates, including user-defined summary tables, and indexed (materialized) views. Furthermore, columnstore indexes can greatly improve ROLAP performance, making ROLAP more attractive. For more information, see Columnstore Indexes.

Indexes containing XML, varchar(max), nvarchar(max), and varbinary(max) columns can now be created, rebuilt, and dropped as an online operation.

SQL Server 2012 supports up to 15,000 partitions by default. In earlier versions, the number of partitions was limited to 1,000 by default.

A FILESTREAM filegroup can contain more than one file. For a code example that demonstrates how to create a FILESTREAM filegroup that contains multiple files, see CREATE DATABASE (Transact-SQL).

You can improve I/O scalability for FILESTREAM data by placing different files within the same FILESTREAM filegroup on different volumes. This feature eliminates the need for complicated workarounds that use partitioning and multiple FILESTREAM filegroups.

Community Additions