SQL 2016 - It Just Runs Faster Announcement

Published Jan 15 2019 04:51 PM 147 Views
Microsoft
First published on MSDN on Feb 23, 2016

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.

%3CLINGO-SUB%20id%3D%22lingo-sub-318684%22%20slang%3D%22en-US%22%3ESQL%202016%20-%20It%20Just%20Runs%20Faster%20Announcement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-318684%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Feb%2023%2C%202016%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20SQL%20Server%202016%26nbsp%3B%20'It%20Just%20Runs%20Faster'%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EA%20bold%20statement%20that%20any%20SQL%20Server%20professional%20can%20stand%20behind%20with%20confidence.%26nbsp%3B%26nbsp%3B%20My%20development%20collogues%20and%20I%20are%20starting%20a%20regular%20blog%20series%2C%20outlining%20the%20vast%20range%20of%20scalability%20improvements%2C%20allowing%20SQL%20Server%202016%20to%20run%20across%20a%20wide%20array%20of%20hardware%20configurations%2C%20faster%20and%20better%20than%20previous%20releases%20of%20SQL%20Server.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CA%3E%20Try%20SQL%20Server%202016%20Today%20%3C%2FA%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EIn%20the%20Sep%202014%20the%20SQL%20Server%20CSS%20and%20Development%20teams%20performed%20a%20deep%20dive%20focused%20on%20scalability%20and%20performance%20when%20running%20on%20current%20and%20new%20hardware%20configurations.%26nbsp%3B%26nbsp%3B%20The%20SQL%20Server%20Development%20team%20tasked%20several%20individuals%20with%20scalability%20improvements%20and%20real%20world%20testing%20patterns.%26nbsp%3B%26nbsp%3B%20You%20can%20take%20advantage%20of%20this%20effort%20packaged%20in%20SQL%20Server%202016.%20-%20%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fserver-cloud%2Fproducts%2Fsql-server-2016%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fserver-cloud%2Fproducts%2Fsql-server-2016%2F%3C%2FA%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CEM%3E%20%E2%80%9CWith%20our%20focused%20investment%20in%20performance%20and%20scale%2C%20simply%20upgrading%20to%20SQL%202016%20could%20bring%2025%25%20performance%20improvement.%20SQL%202016%20supports%203X%20more%20physical%20memory%20than%20previous%20versions.%20The%20new%20column%20store%20engine%20and%20query%20processing%20technology%20could%20increase%20query%20performance%20up%20to%20100X%20and%20the%20new%20In-memory%20OLTP%20engine%20can%20process%201.25million%20batches%2Fsec%20on%20a%20single%204%20socket%20server%2C%20which%20is%20more%20than%203X%20of%20SQL%202014.%20%E2%80%9C%20%3CBR%20%2F%3E%20-%20Rohan%20Kumar%2C%20Director%20of%20SQL%20Software%20Engineering%20%3C%2FEM%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CEM%3E%20%22SQL%20Server%202016%20running%20on%20the%20same%20hardware%20as%20SQL%20Server%202014%2C%202012%2C%202008%2C%202008%20R2%20or%202005%20uses%20fewer%20resources%20and%20executes%20a%20wide%20range%20of%20workloads%20faster.%26nbsp%3B%20I%20have%20studied%20code%20check-ins%20and%20tested%20the%20improvements%20seeing%20the%20scalability%20improvement%20first%20hand%20and%20running%20SQL%20Server%202016%20for%20internal%20SQL%20Support%20needs%20since%20Mar%202015%20because%20of%20the%20improved%20features%20and%20scalability.%22%20%3CBR%20%2F%3E%20-%20Bob%20Dorr%2C%20Principle%20Engineer%20SQL%20Server%20Support%20%3C%2FEM%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EFor%20example%2C%20by%20default%20SQL%20Server%202016%20provides%20automatic%2C%20soft%20NUMA%20configuration.%26nbsp%3B%26nbsp%3B%20The%20following%20table%20is%20taken%20from%20an%20ASP.NET%2C%20session%20state%20cache%2C%20stress%20test.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CDIV%3E%0A%20%20%20%3CTABLE%3E%0A%20%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%20%3CTD%3E%3CP%3E%3CSTRONG%3E%20Auto-soft%20NUMA%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3E%3CP%3E%3CSTRONG%3E%20Batch%20Requests%20%2F%20Sec%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%3CTD%3E%3CP%3E%3CSTRONG%3E%20Enabled%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3E%3CP%3E%3CSTRONG%3E%201.20%20Million%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%3CTD%3E%3CP%3E%3CSTRONG%3E%20Disabled%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3E%3CP%3E0.74%20Million%3C%2FP%3E%0A%20%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%0A%20%20%3C%2FDIV%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CUL%3E%0A%20%20%20%3CLI%3EDBCC%20scales%207x%20better%3C%2FLI%3E%0A%20%20%20%3CLI%3EVarious%20spatial%20patterns%20execute%20100s%20of%20times%20faster%20with%20specific%20paths%20up%20to%202000x%20faster%3C%2FLI%3E%0A%20%20%20%3CLI%3EMultiple%20log%20writers%3C%2FLI%3E%0A%20%20%20%3CLI%3E%E2%80%A6%3C%2FLI%3E%0A%20%20%3C%2FUL%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3Eare%20just%20a%20few%20of%20the%20blogs%20we%20have%20slated.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CEM%3E%20%3CSTRONG%3ENote%3A%20%3C%2FSTRONG%3E%20Builds%20prior%20to%20the%20SQL%20Server%202016%20release%20may%20require%20trace%20flags%20or%20configuration%20modifications%20to%20enable%20enhancements.%20%3C%2FEM%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EBob%20Dorr%20-%20Principal%20SQL%20Server%20Escalation%20Engineer%3C%2FP%3E%0A%20%20%3CP%3ERyan%20Stonecipher%20-%20Principle%20SQL%20Server%20Software%20Engineer%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CB%3E%20Follow-up%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%3CP%3EI%20have%20received%20several%20inquiries%20about%20SKU%20applicability.%20While%20it%20is%20true%20that%20some%20features%20are%20SKU%20specific%2C%20these%20improvements%20were%20done%20in%20the%20engine%20so%20it%20could%20be%20leveraged%20by%20any%20SQL%20Server%20SKU.%20For%20example%2C%20the%20updated%20SOS_RWLock%20is%20simply%20a%20change%20to%20the%20synchronization%20primitive.%20Where%20SOS_RWLock%20is%20used%2C%20no%20matter%20the%20SKU%2C%20the%20new%20design%20applies.%3C%2FP%3E%0A%20%20%3CP%3EEven%20when%20a%20change%20only%20appears%20in%20a%20specific%20feature%20today%2C%20it%20is%20still%20driving%20additional%20benefit%20discussions%20and%20improvements.%20For%20example%2C%20the%20improved%20compression%20capabilities%20applied%20to%20AlwaysOn%20are%20being%20tested%20and%20evaluated%20for%20use%20by%20the%20Backup%2FRestore%20code%20line.%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-318684%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Feb%2023%2C%202016%20SQL%20Server%202016%26nbsp%3B%20'It%20Just%20Runs%20Faster'%20%26nbsp%3B%20A%20bold%20statement%20that%20any%20SQL%20Server%20professional%20can%20stand%20behind%20with%20confidence.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-318684%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPerformance%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esql%202016%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 04:51 PM
Updated by: