Chia sẻ qua

SQL 2016 - It Just Runs Faster Announcement

  • Bài viết

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 Escalation 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.

Comments

  • Anonymous
    February 23, 2016
    Great! Will some of this performance improvents trickle into Express and/or Standard editions?
  • Anonymous
    February 25, 2016
    Can you please provide some further information on "Automatic soft NUMA configuration" to clarify that this is not being confused with the fact that SQL Server 2016 implements the behavior of trace flag 8048 by default, whereby memory node partitioning is at CPU core level and not at CPU socket / NUMA node level.The evidence put forward to support the claim "It just runs faster" is vapor trail thin. Can you also clarify what "Spatial pattern" actually refers to, is this alluding to something to do with spatial data types ?, could you please consider reviewing and re-writing this post. I find it difficult to believe that Bob Ward or anyone from his team had any involvement in writing this.
  • Anonymous
    February 25, 2016
    Thanks for info. could you please provide more information about Builds prior to the SQL Server 2016 release may require trace flags or configuration modifications to enable enhancements? . Ideally i am interested in the trace flags
  • Anonymous
    February 25, 2016
    Nice! I am glad new generations of MS products are becoming not just another bunch of additional features, which just make it more complex for most customers (and useful just for some), but improve the product as a whole (UX, performance etc.).
  • Anonymous
    March 31, 2016
    The comment has been removed
  • Anonymous
    May 07, 2016
    Do any of these speed improvements concern the Standard edition or are they just for Enterprise? e.g. the 25% faster claim, is that applicable only to Enterprise? Most blog posts this year claim speed improvements, but they don't say for which edition which is confusion and potentially misleading since no clarity is provided. A detailed matrix of enhancements against each edition would be massively helpful to your customers and help with upgrade decisions. Thank you.
  • Anonymous
    May 07, 2016
    The changes are 'not limited' to Enterprise Edition. While some features are only available in the Enterprise Edition, improvements in core speed and capabilities were made to the broad engine itself.
  • Anonymous
    June 08, 2016
    I've found something that is definitely not faster.8 minutes to create a table valued function with schema binding.SOS_SCHEDULER_YIELD with hundreds of seconds of CPU.No blocks.Then it finally finishes. Will be raising a premier ticket tomorrow.