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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

  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 […]

SQL Server’s Column Store feature is designed to accommodate large amounts of data and associated compression capabilities.  Microsoft studied numerous data layouts associated with column store and the CPU instructions required to process that data.   Various algorithms have been optimized throughout the column store code base.   A specific, SQL Server 2016  enhancement is the […]

SQL Server 2016 enhanced the bulk insert activities (BULK INSERT), leveraging the CPUs vector instructions.   The vector based approach allows the bulk data to be processed faster.   For example, when inserting data from a text file the integer conversion leverages SSE instructions when the character length is more than 4 characters and trace flag […]

The AlwaysOn log transport uses a SQL Broker based design to send and receive messages between the primary and secondary replicas.    Studying the transport behavior revealed various improvement opportunities.   Many of you have read the various materials indicating that log transportation between the primary and secondary is commonly 1/3 that of file copy speed.   […]

SQL Server 2016 introduces two distinct changes in the AlwaysOn transport, compression design.   Improved compression algorithms Parallel compression of log block data   Compression can be performed faster, using less resource overhead and maintains compression ratios.   SQL Server 2016 also introduces parallel compression operations.  The following chart outlines the performance and resource gains […]

The SQL Server 2016, AlwaysOn, log transport takes advantage of hardware based encryption to significantly improve scale and performance.   SQL Server 2016 development efforts continued focusing on the AlwaysOn log shipping transport.  Testing revealed that software based encryption, while fundamentally sound, could be improved using hardware based capabilities.   The exchange of information between […]

SQL Server leverages a worker pool for the In-Memory Optimized Database capabilities.  Prior to SQL Server 2016, increasing the size of the pool required you to enable a startup trace flag.   SQL Server 2016 removed the trace flag and updated the design to dynamically adjust the size of the In-Memory Optimized Database worker pool […]

SQL Server leverages MSDTC for distributed transactions (begin distributed transaction, remote proc trans, etc.)   Prior to SQL Server 2016 the MSDTC service must be running (started) prior to any SQL Server, DTC based transaction activity.   SQL Server 2016 enhances SQL distributed transaction capabilities leveraging MSDTC, On Demand startup.  The On Demand startup of MSDTC […]

SQL Server 2016 improves the XEvent Linq reader scalability and performance.    The XEvent UI in SQL Server Management Studio uses the XEvent Linq reader to process the events for display.   Careful study of the XEvent Linq reader revealed opportunities for scalability and performance improvements.     Note:  The XEvent Linq reader is .NET based and […]