Improve Performance with SQL Server 2012

Microsoft designed SQL Server 2012 as a threshold of technologies that would help deliver a major leap forward in performance. These improvements have, to a large extent, made customers rely on SQL Server 2012 to manage their explosive growth in data volume and complexity.

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.

One thought on “Improve Performance with SQL Server 2012

  1. christi parks November 26, 2012 / 12:00 pm

    Hello, i would like to ask that what is the benefits of sql training, what all topics should be covered and it is kinda bothering me … and has anyone studies from this course http://www.wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance…
    would really appreciate help… and Also i would like to thank for all the information you are providing on sql training.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s