SQL Server According to Bob

Two guys who work on SQL Server

Tag: It Just Runs Faster

SQL Server Log Writer Workers

SQL Server 2017 leverages up to 4 log writer workers, on hidden schedulers, to assist in transaction log processing activities.   You may find the number of log write workers has been increased to 8 when running newer releases of SQL Server to accommodate larger systems.  (Caution: Pre-release software may change before final release.) During SQL […]

Read More

How It Works (It Just Runs Faster): Auto Soft NUMA…

Back in June, Bob Dorr gave you the thinking behind why SQL Server 2016 It Just Runs Faster due to changes in our SQL Server and NUMA configurations. Bob blogged about a new SQL Server 2016 feature called Auto Soft NUMA. As I’ve hit the road and talked more about SQL Server 2016: It Just […]

Read More

How It Works (It Just Runs Faster): Non-Volatile Memory SQL Server Tail Of Log Caching on NVDIMM

Note: We received feedback that there was some confusion on us calling this functionality “tail of the log caching” because our documentation and prior history has referred to the tail of the log as the portion of the hardened log that has not been backed up. This feature is actually officially called Persisted Log Buffer […]

Read More

SQL Server 2016 – It Just Runs Faster: Always On Availability Groups Turbocharged

When we released Always On Availability Groups in SQL Server 2012 as a new and powerful way to achieve high availability, hardware environments included NUMA machines with low-end multi-core processors and SATA and SAN drives for storage (some SSDs). Performance issues surrounding Availability Groups typically were related to disk I/O or network speeds. As we […]

Read More

How It Works: Reader / Writer Synchronization

This post is not about a specific SQL Server object but instead outlines a technique used in various locations to reduce contention while still providing thread synchronization.  There are hundreds of locations throughout the SQL Server code base that must account for multi-threaded access.   A common technique used in multi-threaded coding is a reader, writer […]

Read More

SQL 2016 – It Just Runs Faster Announcement

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

Read More

SQL 2016 – It Just Runs Faster: DBCC Scales 7x Better

Many of you have experienced (MULTI_OBJECT_SCANNER* based) waits while running DBCC CHECKS*(checkdb, checktable, …)   Internally DBCC CHECK* uses a page scanning coordinator design (MultiObjectScanner.)  SQL Server 2016 changes the internal design to (CheckScanner), applying no lock semantics and a design similar to those used with In-Memory Optimized (Hekaton) objects, allowing DBCC operations to scale […]

Read More

SQL 2016 – It Just Runs Faster: DBCC Extended Checks

Last week’s post (SQL 2016 – It Just Runs Faster: DBCC Scales 7x Better) talked about several improvements to DBCC CHECKDB to make it run faster. In today’s post, we will talk about additional improvements to extended logical checks. When checking database consistency using DBCC CHECKDB, in addition to the amount of data or number […]

Read More

SQL 2016 – It Just Runs Faster: Native Spatial Implementation(s)

Spatial data is among, if not the fastest, growing storage types for the SQL Server database.   It is common place for customers to have 3,000,000 or more rows in a database.   Customers are tracking vehicles, delivery locations, drilling positions and much more, leveraging the information to efficiently run their business.   To fully appreciate the […]

Read More

SQL 2016 – It Just Runs Faster: TVPs with Spatial Column(s)

Table Valued Parameters (TVPs) containing spatial columns can be used as input parameter(s) to stored procedures.  SQL Server 2016 improves the scalability, using native spatial validation(s), increasing performance by 15 times or more.   TVP Before the Fix:  8000 rows/sec TVP After the Fix: 120,000 rows/sec   ‘It Just Runs Faster’ – Apply SQL Server […]

Read More

SQL 2016 – It Just Runs Faster: Spatial Index Builds Faster

Index creation and tessellation are often intensive, spatial activities.    Along with the native and TVP, spatial enhancements additional work to optimize index creation and tessellation was completed.   Testing reveals that building a spatial index on SQL Server 2016, with the improved design, can be more than 2 times faster than SQL Server 2012 or […]

Read More

SQL 2016 – It Just Runs Faster: -T1117 and -T1118 changes for TEMPDB and user databases

Configuration of TEMPDB is often critical to scalability and throughput of SQL Server applications.  The following link (https://support.microsoft.com/en-us/kb/2964518) outlines how to configure SQL Server 2014 and 2012 for optimal scalability and performance.   A SQL Server 2016 primary goal was ‘It Just Works.’  Out of the box a customer should not have to engage in […]

Read More

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

Read More

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

Read More

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

Read More

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

Read More

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

Read More

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

Read More

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

Read More

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

Read More

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

Read More

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

Read More

SQL 2016 – It Just Runs Faster: Column Store Uses Vector Instructions (SSE/AVX)

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

Read More

SQL 2016 – It Just Runs Faster – BULK INSERT Uses Vector Instructions (SSE/AVX)

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

Read More

SQL 2016 – It Just Runs Faster: AlwaysOn Log Transport Reduced Context Switches

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

Read More

SQL 2016 – It Just Runs Faster: AlwaysOn Parallel Compression / Improved Algorithms

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

Read More

SQL 2016 – It Just Runs Faster – AlwaysOn AES-NI Encryption

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

Read More

SQL 2016 – It Just Runs Faster: In-Memory Optimized Database Worker Pool

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

Read More

SQL 2016 – Leverages On Demand MSDTC Startup

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

Read More

SQL 2016 – It Just Runs Faster: XEvent Linq Reader

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

Read More