From the MVPs: SQL Server 2014 Performance Enhancements
This is the 41st in our series of guest posts by Microsoft Most Valued Professionals (MVPs). You can click the “MVPs” tag in the right column of our blog to see all the articles.
Since the early 1990s, Microsoft has recognized technology champions around the world with the MVP Award . MVPs freely share their knowledge, real-world experience, and impartial and objective feedback to help people enhance the way they use technology. Of the millions of individuals who participate in technology communities, around 4,000 are recognized as Microsoft MVPs. You can read more original MVP-authored content on the Microsoft MVP Award Program Blog .
This post is by SQL Server MVP Shehap El Nagar . Thanks, Shehap!
SQL Server 2014 Performance Enhancements
Database Performance has become an important subject for any database administrator, database Analysts and IT Directors. That is why Microsoft has focused on the performance factor within SQL Server 2014 to achieve 10x-30x improvementwithout touching your code whatsoever, they did that through in-Memory OLTP with no page buffer. Then you can create memory optimized table and add it to a memory optimized file group which is translated to .dll entry points. Moreover its data is more resilient, in other words its data remains intact after a server crash as they look like resident on disk. Awesome! I don’t have enough words to say how much it improved significantly an OLTP transaction to insert and delete 1 million records from 32 sec to just 2 seconds! In addition you no longer worry about heavy delete processes used for archiving because records are actually not deleted but just marked as deleted and another garbage collection process will take place asynchronously to clean up the deleted data without affecting on live transactions. Additionally, no changes are needed from developers on code level as it is one of the major myths of in-memory OLTP technology
This dream of performance was achievable through in-memory OLTP and other important projects targeted at rebuilding the architecture of the SQL engine from the ground to process queries much more efficiently such as:
· No lock management (No locks or latches at all) so we can get much high concurrency between transactions where in memory OLTP doesn't use either pages or buffer so we can survive easily from latch and locks accidents respectively and thus increase concurrency up to the limit of HW resources.
This feature is also defined while creating tables as memory optimized tables
· Natively compiled SPs .dll entry point translated form SQL Server code to C code, then to machine code and that is made much more efficient business logic processing. This added the flavor of C compilers into our database because all of these dll entry points are accomplished with C source code files and the good thing here is that no injection at all for these files so it is secured enough .
This feature is defined while creating stored procedures
· Atomic fashion of SPs execution where no definition for protected region and no share for buffer cache. This in turn achieves the height concurrency up to the limit of HW resources
This feature is defined while creating stored procedures as well.
· CCI (clustered column stored index) which made it applicable to update/delete/insert data to tables having CCI oppositely to NCCI (Non-clustered column store index) within SQL Server 2012 where they were just read. It has been supporting more operators like union all, full outer join, scalar aggregates, distinct aggregates, not in - in & exists. I don't mean supporting them is just like allowing or disallowing using these operators, because both CCI and NCCI can use these operators, but it is about how they can use these operators in row mode (row by row) or in batch mode (bulk row). You will see remarkable performance improvements once your query execution plans select batch mode to run these operators instead of row mode
Furthermore, this feature is defined while creating indexes at tables
· COLUMNSTORE_ARCHIVE compression which can add basic improvement of 30 % but it is applicable only to tables having CCI (clustered column stored index)
This feature is also defined while compressing any data entity having CCI
The performance enhancements are not the only improvements in SQL Server 2014. It is distinctly different from any previous SQL Server version on 4 major levels:
· Hybrid Clouding support and integrity
· Frictionless scale up
· Performance and stress power of database
· Efficient business logic processing
Below is a quick diagram depicting the SQL Server evolutions starting from SQL Server 2000 through SQL Server 2014.
In a continuing series on my site SQL Performance Tuning, I will elaborate on the most significant new features in SQL Server 2014. These outstanding improvements make SQL Server 2014 standout as the best RDBM.