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 MoreWhen 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 MoreI 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 MoreSQL 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 MoreToday 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 MoreI 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 MoreThe 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 MoreBack 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 MoreNote: 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 MoreYesterday 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 MoreI 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 MoreHave 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 MoreI 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 MoreThis 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 MoreThere 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 MoreVarious 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 MoreSQL 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 MoreMy 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