A few of the major enhancements in performance and scalability include:
Increase in Partition Support
The number of table partitions has been extended from 1000 to 15000 per table by default in order to dramatically boost scalability and performance associated with large tables and data warehouses. With 15000 partitions can be stored for 40+ years if the partitioning is done on daily basis. Large sliding-window scenarios for data warehouse maintenance are now enabled with the help of this new expanded support. Pushing the data from staging table to one of the partition is a metadata change that makes operations very fast. There is also a change in the sampling algorithm for partitioning, which improves the query performance and has better memory usage. Starvation of partition switch due to schema stability lock has been eliminated.
Columnstore Indexes
Microsoft has included Column Store index, a revolutionary indexing technique feature in the database world, in the upcoming SQL Server code name “Denali”. It is built directly in the relational engine. It is mainly designed to accelerate data warehouse queries. The performance of query in the data warehouse environment is known to have become 1000 times better. Some customers have also said to have experienced a 400 percent improvement in performance. Internally it creates a separate page for each individual columns using batch processing (vector-mode) algorithm and applies the compression algorithm on top of it. So in a way, it implies more business value to the customer as the response time decreases drastically with lesser hardware. Columnstore Indexes help organizations deliver breakthrough and predictable performance on large data sets to stay competitive.
Create, Rebuild, and Drop Online Indexes
Online operation is a key to performance and throughput, as it does not block other operations accessing the same resources, especially, in case of queries which are frequently run and are long running.
SQL Server 2012 enables indexes that contain varchar(max), nvarchar(max), and varbinary(max) columns to be created, rebuilt, and dropped as an online operation. Usually indexes are not created on columns with such data types but they may be included in column key. This proves to be highly beneficial for companies running mission-critical workloads who require maximum uptime and concurrent user activity during index operations.
Fast Fulltext
FullText queries now use query optimization and parallel query execution algorithms.Fulltext index creation uses parallel master merge algorithm which creates indexes in parallel and then merges them together. So the operation is faster with minimal blockings. Also, TOP is understood by fulltext engine in SQL Server 2012. Earlier fulltext engine sent all documents to the database and then the database engine discards non-qualifying rows.
Temporary Statistics Generation on Database Snapshots
If the database snapshot is queried in SQL Server 2012, it automatically generates the required statistics temporarily. So query generates a better execution plan and is faster without any changes by the developer.
New and Enhanced Query Optimizer Hints
The syntax for the FORCESEEK table hint has been modified. Index and index columns can be specified to further control the access method on the index. The FORCESCAN table hint has also been added. It complements the FORCESEEK hint by specifying that the query optimizer uses only an index scan operation as the access path to the table or view referenced in the query. The FORCESCAN hint can be useful for queries in which the optimizer underestimates the number of affected rows and chooses a seek operation rather than a scan operation.
New Functions
Microsoft has added number of new functions. Using the new window functions would lead to a huge performance benefit, for the given scenario. The date specific functions are more simplified and faster. For example: EOMONTH function would return the last day of the month, without writing complex code for handling the leap year.
Windows Server Core Support
SQL server 2012 can take advantage of many new features supported in windows 8. One of the biggest improvements in Windows 8 is the Windows Server Core Support. Windows Server Core Support is a GUI less version of windows. It works on DOS and PowerShell commands and consumes 50% less memory and has lower disk space utilization ratio. It is also more secure and requires fewer patches. So in a way SQL Server 2012 has the potential to truly be a server product.
To sum it up, performance and scalability enhancements in SQL Server 2012 enables more online operations, interactive response time and business value from data. It also requires less hardware and reduces the need for summary aggregates.
