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…

3

SQL Mysteries: SQL Server Login Timeouts – A Debugging Story

This blog takes you through the debugging journey, refreshing us on old concepts and introducing some new ones. Reported Symptoms ·         Random connection failures from both SQL Authentication and AD based logins ·         Failures occur from remote clients or sqlcmd executed directory on the server (/opt/mssql-tools/bin) ·         Unpredictable when failures occur ·         Non-yielding scheduler reports…

1

SQL Server Worker Thread Default Calculation

SQL Server 2017 introduced a small change to SQL Server’s default worker thread calculation, accounting for smaller environments.   When running on smaller environments SQL Server reduces the worker target. For an X64 installation, using the sp_configure ‘max worker threads’ value of 0 SQL Server uses the following calculation. On small environments SQL Server always uses…

1

Viewing and Sorting XEvents Efficiently (Code Samples) – XEProfiler

I was doing backups and clean-ups and ran across a couple of sample projects for XEvent and event_sequence processing I thought others might find helpful. – Enjoy! The sample code is provided “as is” and any express or implied warranties, including the implied warranties of merchantability and fitness for a particular purpose, are disclaimed. In…


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…


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…


SQL Server On Linux: Forced Unit Access (Fua) Internals

  Overview SQL Server relies on Forced-Unit-Access (Fua) I/O subsystem capabilities to provide data durability, detailed in the following documents: SQL Server 2000 I/O Basic and SQL Server I/O Basics, Chapter 2 Durability: “In database systems, durability is the ACID property which guarantees transactions that have committed will survive permanently. For example, if a flight…

1

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…


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…


SQL Server Mysteries: The Case of the Dropped AD Group Login

Dylan (who kindly wrote up the contents of this blog for me) was modifying the tests for Active Directory Login activities.  As Dylan and I reviewed the changes a specific behavior involving Active Directory Group Logins caught our attention. Imagine you have a group on your domain [CONTOSO\group] which has a member [CONTOSO\user], and the…