SQL Server According to Bob

Two guys who work on SQL Server

Category: SQL Server 2017

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

Read More

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

Read More

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

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

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 Server on Linux: How is Delete-On-Close Handled

Windows provides an option for CreateFile to delete a file when the file is closed (FILE_FLAG_DELETE_ON_CLOSE.)   Host Extension Handling Linux does not expose such an option as part of the open syscall.  Instead the host extension remembers that the open request was made with the FILE_FLAG_ON_DELETE option and after closing a file issues the remove […]

Read More

SQL Server on Linux: Why Do I Have Two SQL Server Processes

When starting SQL Server on Linux why are there two (2) sqlservr processes? systemctl status mssql-servermssql-server.service – Microsoft SQL Server Database Engine…   CGroup: /system.slice/mssql-server.service           ├─85829 /opt/mssql/bin/sqlservr       <——— WATCHDOG | MONITOR           └─85844 /opt/mssql/bin/sqlservr       <——— SQLSERVER.EXE The simple answer is the first entry (85829) is not what you are used to on a Windows system as sqlservr.exe […]

Read More

Take the SQL Server Mac challenge

When I graduated from college, one of the first computers I ever used was a MacIntosh. I loved the Mac, the user interface, and the overall footprint of that computer. I also started my career developing on UNIX systems with C++ and databases like Ingres. As I moved to other jobs, the PC was becoming […]

Read More

SQL Server 2017 Improved Resource Usage On Smaller Machines

What does improving resource usage on smaller machines really mean? https://support.microsoft.com/en-us/help/4078095 SQL Server is designed to scale across the spectrum of small machines to enterprise class servers.  The changes made in SQL Server 2017 CU4 improve resource usage on smaller machines, such as a VM or Container used for testing and development.  SQL Server always […]

Read More

Microsoft Engineering loves SQLBits

I have the blessed opportunity to speak at many customer events. SQLBits is always one of my favorites because of the great community that attends and the opportunity for me to travel to a great city like London. Well, for SQLBits 2018, it is not just me. Microsoft engineering is actually sending quite a few […]

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

Let’s talk SQL Server to kick off the PASS Facebook Live series

I’ve had such a long, great history with the PASS Summit, Community, and all the great people that have run this organization. I owe a great debt to this community to help me build a brand and reputation for my passion and talks on SQL Server. Social Media was never really part of the equation […]

Read More

SQL Server Management Studio Provides–“XE Profiler”

Bob Ward and I worked with our SQL Server Tool developers (thanks David) to enable ‘Quick XE Trace’ capabilities. The feature is available in the latest SQL Server Management Studio (SSMS) release. Despite the deprecation of SQL Profiler several years ago, as well as various documents and blogs pointing out the older trace facilities shortcomings […]

Read More

SQL Server Linux: Distributed transactions requiring the Microsoft Distributed Transaction Coordinator service are not supported on SQL Server running on Linux. SQL Server to SQL Server distributed transactions are supported. – CLARIFIED!!!

  SQL Server 2019 CTP2 SQL Server 2019 adds broad support for distributed transactions (both MSDTC/OLE-TX and XA) to SQL Server on Linux.   SQL Server 2019 on Linux enables the MSDTC service in SQLPAL providing distributed transaction capabilities on Linux alongside our SQL Server on Windows counterpart, supporting MSDTC/OLE-TX and XA transaction types. You can […]

Read More

Experience SQL Server 2017: Start your journey here

Updated Content! Please check out these new resources for SQL Server 2017 and SQL Server on Linux and Docker: Brk2045 upgrade sql server 2017 (on prem, iaa-s and paas) from Bob Ward Brk2051 sql server on linux and docker from Bob Ward With the 2017 PASS Summit approaching next week, I thought it would be […]

Read More

Experience SQL Server 2017: The Fast and the Furious

Please check out a new blog post I created detailing how SQL Server 2017 is Fast, Built-In. Faster than you think running on Linux, using Columnstore, and with right features and configuration. How we keep you fast and tuned with Adaptive Query Processing and Automatic Tuning. And we are the fastest database everywhere you need […]

Read More

SQL Server 2017 generally available on October 2nd…

Scott Guthrie and Rohan Kumar officially made the announcement today at Ignite that SQL Server 2017 will be generally available on October 2nd, 2017. Check out ScottGu’s blog post at: https://azure.microsoft.com/en-us/blog/new-azure-advancements-remove-cloud-barriers-for-enterprises-at-ignite-2017 Rohan’s Blog post at: https://blogs.technet.microsoft.com/dataplatforminsider/2017/09/25/microsoft-for-the-modern-data-estate/ Andrew Brust also wrote a very nice article detailing out all the great new features that come with SQL Server […]

Read More