Contact Us

Contact Us



Pleas confirm by checkbox


Technical

Improve Performance with SQL Server 2012

Author_img
By talentica October 01, 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.

Related posts
3 Crucial Ways of Inserting Code into a Running Application without Creating Bottlenecks
Technical

3 Crucial Ways of Inserting Code into a Running Application without Creating Bottlenecks

By talentica January 28, 2022
Apache Spark Standalone Setup On Linux/macOS
Technical

Apache Spark Standalone Setup On Linux/macOS

By talentica October 20, 2021
Apache Flink Standalone Setup on Linux/macOS
Technical

Apache Flink Standalone Setup on Linux/macOS

By talentica October 13, 2021
Identity, Authentication, And Access Management Using Azure Active Directory  
Technical

Identity, Authentication, And Access Management Using Azure Active Directory  

By talentica September 22, 2021
Things to Know Before You Select A Crypto Wallet
Blockchain

Things to Know Before You Select A Crypto Wallet

By talentica September 20, 2021
Solve 3 Most Irritating Outlook Email Rendering Issues.
Technical

Solve 3 Most Irritating Outlook Email Rendering Issues.

By talentica September 15, 2021
Intuit Wasabi – A Scalable A/B Testing Solution
Technical

Intuit Wasabi – A Scalable A/B Testing Solution

By talentica September 01, 2021
How To Pick The Right Data Analytics Strategy For Serverless Systems?
Big Data

How To Pick The Right Data Analytics Strategy For Serverless Systems?

By talentica August 25, 2021
Change Notifications and Named Options using Options pattern in .NET Core
Technical

Change Notifications and Named Options using Options pattern in .NET Core

By talentica August 18, 2021
Create Strongly Typed Configurations in .NET Core
Technical

Create Strongly Typed Configurations in .NET Core

By talentica August 13, 2021

Stay updated

Get the latest creative news from Fubiz about art, design and pop-culture.