October 19, 2018
SQL Server 2016 Is Faster Than Ever

Why is SQL Server 2016 Faster Than Ever

SQL Server 2016 is designed to work faster than any earlier version of SQL Server. There was a set of tweaking that was earlier required to be a manual process. At the same time, an experienced DBA was required to do all the tweaking. Experience with trace flags, TempDB best practices were required to keep SQL Server up to speed. That changed with SQL Server 2016. In this release, a lot of those tweaks are preconfigured which is why SQL Server 2016 is faster than ever. Want to know what these tweaks are? Read along:

How SQL Server 2016 Is Faster Than Ever

Tempdb Improvements

Unnecessary adjustments like Trace flags 1117 and 1118 have been removed and the behavior is enabled by default for tempdb system database.

That trace flag 118 instructs SQL Server to avoid “Mixed extents” and use “Full extents”  See KB 2154845

TRACE FLAG 1117 – Grow All Files In a FILEGROUP Equally

Current Setup:

  • Metadata latch contention
  • High rate of “Create/Drop” workloads
  • LATCH_EX waits on underlying system tables

Currently the only solution is to rewrite stored procedures in order to reduce temp table usage.

SQL Server 2016 Improvements:

  • Improved scanning algorithms
  • Reduced metadata contention
  • Optimistic locking of system tables under shared latch

Setup Options

You can now use the new setup installer to pre-configure size and auto growth of TempDB.

tempdb-1

Trace Flag Behaviours

Spatial:

-T8048
-T6531
-T6532
-T6533
-T6534

Query Optimizer
  • Trace Flag 4199
  • Trace Flag 2371
  • Trace flag 2453
  • Trace flag 8032
Errorlog

TF 3014 = TF 3014 + TF 3004 + TF 3212

Metadata Latch Contention

Better handles lower-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.

Dynamic Thread Safe PMO

One of the enhancements of the SQL Server 2016 core engine is scalability improvement, dynamically partitioning thread safe memory objects by NUMA or CPU, which enables higher scalability.

Support for 12 TB RAM of RAM

Multiple Log Writers

Why is SQL Server 2016 Is Faster Than Ever

Auto Soft NUMA

This solves the issues with cxpacket wait types due to high parallelism.

  • Detects 8+ CPUs
  • Targets of 5 to 8 CPUs
  • Scheduling Nodes
  • Per Instance level setting
  • Registry Overrides

DBCC Performance improvements 7x

Why is SQL Server 2016 Is Faster Than Ever

SQL Server 2016 replaces the following Trace-Flags
-T2562
-T2563
-T2549

The above trace flags make SQL Server to better maximize disk I/O resource usage when a user executes the DBCC CHECKDB command

Spatial

2000x more performance in SQL Server 2016 in comparison to previous editions.

See here for detailed information.

Database Cloning

tempdb-4Easy debugging of schema and statistics for query plan

Key Takeaways

SQL Server 2016 just works faster without any application changes. Greater performance and scale for existing workloads. It also provides new diagnostic tools to monitor performance.

Related Posts

SQL Server Always On Availability Groups SQL Server Always On Availability Groups I have already spoken about other high availability technologies like Log-Shipping, Database Mirroring, and ...
How to Find SQL Server Product Key When you install SQL Server, you would be prompted to enter a product key in order to install the correct Edition of SQL Server (2016, Enterprise, Sta...
How to Fix Windows Update error 0x80240fff in Wind... How to Fix Windows Update error 0x80240fff in Windows 10 A lot of users are complaining of getting an error message on their new laptop whenever they...
How to Fix Airplane mode not turning off in Window... Just as you would expect, the "Airplane mode" in Windows 10 is a system setting that allows you to turn off all wireless communications on your PC. Wi...
Disclosure: We are a professional review website that sometimes receive compensation or free units from the companies whose products we review. We test each product thoroughly and give high marks to only the very best. We are independently owned and the opinions expressed here are our own.

FOLLOW @ INSTAGRAM