SQL Server According to Bob

Two guys who work on SQL Server

Tag: How It Works

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

SQL Server Linux: Directory fsync Activities

When Creating, Renaming or Deleting (remove/unlinking) a file, Linux requires the direct parent directory to be synchronized.  As documented in the manpage for fsync core changes to the directory require the directory itself to be synchronized.  “Calling fsync does not ensure that the entry in the directory containing the file has also reached disk. For […]

Read More

SQL Server Linux: fsync and Buffered I/O

I was asked to validate  the SQL Server behavior in light of the error condition involving fsync/fdatasync outlined in this article.  An example of the error: The application does a buffered write and receives success. (This means data can be stored in file system cache, non-stable media.)  An fsync/fdatasync is used to make sure the […]

Read More

SQL Server Instant File Initialization: SetFileValidData (Windows) vs fallocate (Linux)

SQL Server Books Online documents Instant File Initialization and the associated security considerations.   This blog highlights the underlying file system implementations and differences in behavior between Windows and Linux. SQL Server performs the following API calls when creating or extending (growing) data and log files. CreateFile – Create or open a file SetEndOfFile– Establish the […]

Read More

SQL MYSTERIES: Tracing BCP Might Fool You

Today I was tracking a large BCP.exe ‘IN’ operation and monitoring the sys.dm_exec_requests entries.  The ‘BULK INSERT’ command entry appeared, showing the CPU, reads, writes, … but these values where getting reset from time to time.  Here are 3 snapshots from my system. cpu_time    total_elapsed_time      writes      session_id   start_time              command 1387        1396                    32          51           2018-08-07 00:45:42.670 […]

Read More

How It Works: SQL Server DTC (MSDTC and XA Transactions)

I have found the term DTC is used in many ways so for the purposes of the discussion here are a few terms.  When talking DTC, I am talking about the feature set, MSDTC as the OLE-TX implementation and XA for the X/Open XA implementation. DTC Distributed Transaction Coordination – Generic term MSDTC Microsoft specific […]

Read More

How It Works: SQL Server Deadlock Trace Flag 1222 Output

  The trace flag 1222 can be very powerful and helpful in tracking down the cause of a deadlock when used correctly. This week I was tracking down a blocking situation, which I expected the lock monitor to resolve as a deadlock.   The test in question is designed to cause large amounts of deadlocks in […]

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

How It Works: BULK INSERT (BCP) TDS Traffic

Yesterday I had a discussion with a customer and realized that the BCP, TDS pattern could use a bit of documentation. Customer Scenario/Question: I am using BCP.exe to import a large data set.   BCP.exe takes 15 minutes to complete but I only see a few seconds for the BULK INSERT command when monitoring dm_exec_requests.  What […]

Read More

How It Works: How is SQL Server Error 833, 15 Sec I/O Detected

  I was approached to revisit the details of how Error Message 833 is detected and reported. SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d seconds to complete on file [%ls] in database [%ls] (%d).  The OS file handle is 0x%p.  The offset of the latest long I/O is: %#016I64x. […]

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

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

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

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

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