SQL 2016 – It Just Runs Faster: Automatic TEMPDB Configuration

Various KBs, whitepapers and blogs have outlined the need for the creation of multiple, TEMPDB files, same sized files, trace flags and the like.   All of these configuration options increase the scalability of your SQL Server.   In an effort to simplify the tempdb configuration experience, SQL Server 2016 setup has been extended to configure…

1

SQL 2016 – It Just Runs Faster: LDF Stamped

When creating or growing the database log file (LDF) a byte pattern is stamped.  The pattern establishes the proper log block layout with end of log marker(s.)   SQL Server 7.0 changed the LDF format from the original, 2K, Sybase database page design to sector aligned log blocks stamped with all zeros (0x00).    Creation or…

1

SQL 2016 – It Just Runs Faster: Instant File Initialization

Database Instant File Initialization was added several SQL Server releases ago.   The instant file initialization feature scales the creation and expansion (growth) of database, DATA files.    The ‘Manage Volume Privilege’ option is off by default preventing many SQL Server installations from taking advantage of the feature.   SQL Server 2016 Setup provides the option to…

1

SQL 2016 – It Just Runs Faster: Automatic Soft NUMA

As hardware continues to expand and evolve SQL Server testing and customer reports have highlighted the need to partition activities for optimal scaling.  Partitioning based designs are common ways to localize activities and improve performance and scalability.    An example of how SQL Server leverages partitioning is the CMemThread object.   For thread safety various synchronization…

5

SQL 2016 – It Just Runs Faster: Updated Scheduling Algorithms

SQL Server 2016 gets a scalability boost from scheduling updates.   Testing uncovered issues with the percentile scheduling based algorithms in SQL Server 2012 and 2014.  A large, CPU quantum worker and a short, CPU quantum worker can receive unbalanced access to the scheduling resources.   Take the following example.  Worker 1 is a large, read…

0

SQL 2016 – It Just Runs Faster: Dynamic Memory Object (CMemThread) Partitioning

The CMemThread waits (PWAIT_MEMTHREAD) can be a point of contention as machine sizes advance.   The CMemThread object type is utilized in 100s of objects throughout the SQL Server code base and can be partitioned globally, by node or by cpu.     The vast majority of CMemThread objects leverage global partitioning.   Trace flag -T8048 only forces…

1

SQL 2016 – It Just Runs Faster: SOS_RWLock Redesign

The SOS_RWLock is a synchronization primitive used in various places throughout the SQL Server code base.  As the name implies the code can have multiple shared (readers) or single (writer) ownership.    Studying the SQL Server 2012 and 2014 implementation of the SOS_RWLock we found the core acquisition and wait list could be optimized.   SQL…

0

SQL 2016 – It Just Runs Faster: Indirect Checkpoint Default

There are two(2) distinct checkpoint paths provided starting with SQL Server 2014, referred to as Automatic and Indirect.   The vast majority of documentation today highlights the behavior of automatic (classic) checkpoint.  This post outlines some historical aspects of checkpoint and provides the recommendation to leverage Indirect Checkpoint.   Before SQL Server 7.0 The database…

0

SQL 2016 – It Just Runs Faster: Larger Data File Writes

SQL Server uses WriteFileGather for the vast majority of data file write requests.   The logic is to consolidate dirty pages into a single I/O request.   For example page 1:13 and 1:12 are dirty resulting in a single WriteFileGather operation.       Is BUF[1] Dirty – Yes – Write with gather near Do hash…

0

SQL 2016 – It Just Runs Faster: Multiple Log Writer Workers

  SQL Server 2016 introduces multiple log writer workers to the engine.    For many years the log device was limited by the capabilities of spinning media and hardware caches.    The advancement of hardware caches and fast storage types (SSD, flash, …) on large installations can saturate a single log write worker.   SQL Server 2016…

4