SQL Server According to Bob

Two guys who work on SQL Server

Create Table – Disk vs In-Memory Optimized

I had an interesting e-mail discussion related to the performance of create table.  The test being executed was a tight loop of create table statements.  The assumption put forth was in-memory optimized table creation was slower and they expected it to be faster.  We are not talking about the performance of inserts, updates, deletes and […]

Read More

How It Works: Sync IOs in nonpreemptive mode longer than 1000 ms

  Have you encountered the following in your SQL Server error log?       Long Sync IO: Scheduler 95 had 1 Sync IOs in nonpreemptive mode longer than 1000 ms There are two general types of I/O performed by SQL Server. Async – Vast majority of SQL Server I/Os, as outlined in the provided link: https://technet.microsoft.com/en-us/library/aa175396(v=sql.80).aspx […]

Read More

COLD, WARM, HOT … CLEAN, DIRTY, FREE … What Does All This Mean?

I was asked about these terms from a blog reader.  As I thought about these terms and how to blog about them I became concerned that I might just be as clear as mud, but I will do my best. Each of the terms means something within a specific context.   For example, when your teenager […]

Read More

How It Works: Session/SPID (–2) for DTC Transactions

I have written on this subject before but it seems to come up from time to time, as it did again this week.  The Session (SPID) = –2 is just a place holder used by SQL Server to indicate that the DTC transaction is still active but there are no sessions enlisted/propagated into the transaction. […]

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

The SQL Server Basic Installer: Just Install It!

20+ years ago when I joined Microsoft I was handed a diskette (maybe it was two), and was told “Here is SQL Server. Go install it”. So I proceeded to install SQL Server 4.20 on my Windows NT 3.1 desktop machine (I won’t tell you the hardware details. It would scare you). There was a […]

Read More

DBCC Trace Flags 2562 and 2549

  Erin Stellato and Jonathan Kehayias from sqlskills reached out asking for clarification of trace flags 2562 and 2549 behavior.  Trace flags 2562 and 2549 are documented in knowledgebase article: https://support.microsoft.com/en-us/kb/2634571 and the blog post https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-dbcc-scales-7x-better/ highlights the SQL 2016 DBCC performance improvements. For SQL Server 2008, 2012 and 2014 the trace flag behavior remains […]

Read More

How it Works: XEL Health Session and Shutdown

  There are a variety of posts talking about the black box recorder (XEL Health Session) explaining what the recorder is and does: https://blogs.msdn.microsoft.com/psssql/2012/03/08/sql-server-2012-true-black-box-recorder/ This week I ran into niche behavior while looking at the health session output.  While it is unlikely to have any impact on your server the output during a SQL Server […]

Read More

What Am I Working On (RDORR): SQL Server On Linux

To some of you SQL Server On Linux is old news from various announcements made a couple of months ago.  To others this is new information.  Yes, SQL Server runs on Linux!  We are in active, private preview and working towards the public preview release. Read More: https://www.microsoft.com/en-us/server-cloud/sql-server-on-linux.aspx Sneak Peak Demo: https://channel9.msdn.com/Shows/Data-Exposed/SQL-Server-on-Linux-Sneak-Peak It was Jan […]

Read More

Does SQL Server 2016 Require Trace Flag -T8048?

  Various changes from automatic soft NUMA to CMemThread partitioning have muddied the water around the trace flag –T8048 messaging. Prior to SQL Server 2016 the trace flag –T8048 is used to upgrade (only) NUMA partitioned, CMemThread objects to CPU partitioned based objects.  A few of the most common CMemThread objects have been upgraded to […]

Read More

Unresolved Deadlock vs Scheduler Deadlock

These are distinct errors but I have found that many people blend them together.  It really takes an entire chapter to explain these conditions so I am going try to do it in a page or two? Unresolved Deadlock An unresolved deadlock is generally a SQL Server product issue.   When an unresolved deadlock is detected […]

Read More

SQL 2016–Install MSVC Patch Required

I can’t emphasize this patch enough.   There is a MSVC, runtime library patch needed by SQL Server 2016 and without the patch the SQL Server service can simply terminate (crash.)  This may not produce stack dumps and the SQL Server error log often looks like it simply terminates (no logging of shutdown.) For complete instructions […]

Read More

How It Works: How SQL Server Determines Logical and Physical Processors

  SQL Server, as outlined in the following post (https://blogs.msdn.microsoft.com/psssql/2016/03/30/sql-2016-it-just-runs-faster-automatic-soft-numa) adjusts to various processor configurations.  I have received questions related to how SQL Server accomplishes the alignment on both bare metal and VM installations. The key is the GetLogicalProcessorInformation API, and the same logic occurs on bare metal and VM based SQL Server installations.   For  […]

Read More

How It Works: SQL Server 2016 SSE/AVX Support

My recent posts https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-column-store-uses-vector-instructions-sseavx/ https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-bulk-insert-uses-vector-instructions-sseavx/ have generated discussions about the SSE and AVX support boundaries. As indicated, SQL Server 2016 added the use of SSE and AVX instructions in various code paths to improve performance.  The inquiries have centered around the support boundaries of the SSE and AVX instructions.   This post will outline SQL Server’s […]

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