Share via

SQL 2016 - It Just Runs Faster Announcement

  • Article

SQL Server 2016 'It Just Runs Faster'

 

A bold statement that any SQL Server professional can stand behind with confidence. My development collogues and I are starting a regular blog series, outlining the vast range of scalability improvements, allowing SQL Server 2016 to run across a wide array of hardware configurations, faster and better than previous releases of SQL Server.

 

Try SQL Server 2016 Today

 

In the Sep 2014 the SQL Server CSS and Development teams performed a deep dive focused on scalability and performance when running on current and new hardware configurations. The SQL Server Development team tasked several individuals with scalability improvements and real world testing patterns. You can take advantage of this effort packaged in SQL Server 2016. - https://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/

 

“With our focused investment in performance and scale, simply upgrading to SQL 2016 could bring 25% performance improvement. SQL 2016 supports 3X more physical memory than previous versions. The new column store engine and query processing technology could increase query performance up to 100X and the new In-memory OLTP engine can process 1.25million batches/sec on a single 4 socket server, which is more than 3X of SQL 2014. “
- Rohan Kumar, Director of SQL Software Engineering

 

"SQL Server 2016 running on the same hardware as SQL Server 2014, 2012, 2008, 2008 R2 or 2005 uses fewer resources and executes a wide range of workloads faster. I have studied code check-ins and tested the improvements seeing the scalability improvement first hand and running SQL Server 2016 for internal SQL Support needs since Mar 2015 because of the improved features and scalability."
- Bob Dorr, Principle Engineer SQL Server Support

 

For example, by default SQL Server 2016 provides automatic, soft NUMA configuration. The following table is taken from an ASP.NET, session state cache, stress test.

 

Auto-soft NUMA

Batch Requests / Sec

Enabled

1.20 Million

Disabled

0.74 Million

 

  • DBCC scales 7x better
  • Various spatial patterns execute 100s of times faster with specific paths up to 2000x faster
  • Multiple log writers
  • …

 

are just a few of the blogs we have slated.

 

Note: Builds prior to the SQL Server 2016 release may require trace flags or configuration modifications to enable enhancements.

 

Bob Dorr - Principal SQL Server Software Engineer

Bob Ward- Principal SQL Server Software Engineer

Ryan Stonecipher - Principle SQL Server Software Engineer

 

Follow-up

I have received several inquiries about SKU applicability. While it is true that some features are SKU specific, these improvements were done in the engine so it could be leveraged by any SQL Server SKU. For example, the updated SOS_RWLock is simply a change to the synchronization primitive. Where SOS_RWLock is used, no matter the SKU, the new design applies.

Even when a change only appears in a specific feature today, it is still driving additional benefit discussions and improvements. For example, the improved compression capabilities applied to AlwaysOn are being tested and evaluated for use by the Backup/Restore code line.

 

Table of Contents

SQL 2016 – It Just Runs Faster: DBCC Scales 7x Better https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-dbcc-scales-7x-better/
SQL 2016 – It Just Runs Faster: DBCC Extended Checks https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-dbcc-extended-checks/
SQL 2016 – It Just Runs Faster: Native Spatial Implementation(s) https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-native-spatial-implementations/
SQL 2016 – It Just Runs Faster: TVPs with Spatial Column(s) https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-tvps-with-spatial-columns/
SQL 2016 – It Just Runs Faster: Spatial Index Builds Faster https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-spatial-index-builds-faster/
SQL 2016 – It Just Runs Faster: -T1117 and -T1118 changes for TEMPDB and user databases https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-t1117-and-t1118-changes-for-tempdb-and-user-databases/
SQL 2016 – It Just Runs Faster: Automatic TEMPDB Configuration https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-automatic-tempdb-configuration/
SQL 2016 – It Just Runs Faster: LDF Stamped https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-ldf-stamped/
SQL 2016 – It Just Runs Faster: Instant File Initialization https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-instant-file-initialization/
SQL 2016 – It Just Runs Faster: Automatic Soft NUMA https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-automatic-soft-numa/
SQL 2016 – It Just Runs Faster: Updated Scheduling Algorithms https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-updated-scheduling-algorithms/
SQL 2016 – It Just Runs Faster: Dynamic Memory Object (CMemThread) Partitioning https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-dynamic-memory-object-cmemthread-partitioning/
SQL 2016 – It Just Runs Faster: SOS_RWLock Redesign https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-sos_rwlock-redesign/
SQL 2016 – It Just Runs Faster: Indirect Checkpoint Default https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-indirect-checkpoint-default/
SQL 2016 – It Just Runs Faster: Larger Data File Writes https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-larger-data-file-writes-2/
SQL 2016 – It Just Runs Faster: Multiple Log Writer Workers https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-multiple-log-writer-workers/
SQL 2016 – It Just Runs Faster: Column Store Uses Vector Instructions (SSE/AVX) https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-column-store-uses-vector-instructions-sseavx/
SQL 2016 – It Just Runs Faster – BULK INSERT Uses Vector Instructions (SSE/AVX) https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-bulk-insert-uses-vector-instructions-sseavx/
SQL 2016 – It Just Runs Faster: AlwaysOn Log Transport Reduced Context Switches https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-alwayson-log-transport-reduced-context-switches/
SQL 2016 – It Just Runs Faster: AlwaysOn Parallel Compression / Improved Algorithms https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-alwayson-parallel-compression-improved-algorithms/
SQL 2016 – It Just Runs Faster – AlwaysOn AES-NI Encryption https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-alwayson-aes-ni-encryption/
SQL 2016 – It Just Runs Faster: In-Memory Optimized Database Worker Pool https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-in-memory-optimized-database-worker-pool/
SQL 2016 – Leverages On Demand MSDTC Startup https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-leverages-on-demand-msdtc-startup/
SQL 2016 – It Just Runs Faster: XEvent Linq Reader https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-xevent-linq-reader/

Comments

  • Anonymous
    June 17, 2016
    Very helpful post. Bookmarked it for future reference to find SQL 2016 posts.