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…


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…

2

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…

2

SQL Server on Linux: Quick Performance Monitoring

I have been asked several times about how to get a Performance Monitor like view on Linux.   There are lots of Linux tools available (top, iotop, Grafana, and SQL Sentry just scratch the surface of available options) to monitor the Linux system.  Allow me to share one such example to capture and monitor a system….