SQL Server According to Bob

Two guys who work on SQL Server

How It Works: SQL Server Locking WAIT_WITH_LOW_PRIORITY

How It Works: SQL Server Locking WAIT_WITH_LOW_PRIORITY – How does it avoid blocking other lock requests.

Read More

SQL Server for Linux Performance Monitoring Improvements

SQL Server for Linux Performance Monitoring Improvements – Upgraded SQL Server PMDA

Read More

SQL Server On Linux: Logger.ini Size and Rollover Additions to SQL Server 2019 CU4

For SQL Server on Linux, there are additional logging capabilities …

Read More

How It Works: TSQL TRY/CATCH Behavior Fooled Me

If I would have simply read the documentation (https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15) the answer to my problem is answered.

Read More

SQL Server BDC Hints and Tips: VDI Seeding for High Availability

A high availability SQL Server BDC uses the Virtual Device Interface (VDI) to seed databases to secondary replicas.

Read More

SQL Server BDC Hints and Tips: Contained AGs, Ports, containedag_msdb, logs, …

This blog post focuses on connecting to the SQL Server BDC, some helpful log files and utility outputs. Understanding a few basics about a SQL Server BDC and the Contained Availability Group (containedag) makes managing and troubleshooting easier.

Read More

SQL Server dbcc clonedatabase – Gotcha

The dbcc clonedatabase is a handy command to clone metadata and information such as query disk store. Then you can use the database for various activities …

Read More

SQL Server BDC Hints and Tips: Changing Grafana/Kibana Display Time

The Grafana and Kibana interfaces default to the local browser time zone on a SQL Server BDC cluster … how to adjust the time zone for Grafana and Kibana.

Read More

SQL Server BDC Hints and Tips: TEMPDB Disk Usage

How TEMPDB can impact the pod, evictions and disk space usage.

Read More

SQL Server BDC Hints and Tips: The node’s Journal can be your best friend

If you are new to Kubernetes (K8’s), like I am, you quickly learn that the servicing node’s journal holds a wealth of information.

Read More

Using SQL Server’s SNITrace to Troubleshoot Networking Issues

Learn how to troubleshoot SQL Server networking issues using network, XEvent and SNITracing.

Read More

SQL Server I/O Basics Chapter #2

Additional details of SQL Server I/O patterns and capabilities.

Read More

SQL Server I/O Basics Chapter #1

Fundamental SQL Server I/O patterns and capabilities.

Read More

Using SQLBCP Native/Format File vs Text File and the BOM

BCP fundamentals when using format file and UNICODE data.

Read More

SQL Server 2019 Many No Longer Capture Mini-Dump (SQLDump####.mdmp)

I was looking at a SQL Server which had generated ten(10) SQLDump####.txt files but only a single(1) SQLDump####.mdmp file.  We introduced a new feature in SQL Server 2019 to save disk space for mini/crash dumps which can skip the suspension and capture for identical dump signatures, saving disk space and reducing the impact on the […]

Read More

SQL Server: OS Error 665 (File System Limitation) and Linux

I have previously tested and blogged about the NTFS, sparse, file system limitation error 665: https://blogs.msdn.microsoft.com/psssql/2015/06/10/operating-system-error-665-file-system-limitation-not-just-for-dbcc-anymore/ when running DBCC or using Snapshot databases with SQL Server. Recently a customer asked me if they would encounter the same limitation if they moved to Linux. The answer is not the same limitation and for EXT and XFS […]

Read More

SQL Mysteries: Why is my SQL Server experiencing lots of 17830 (TCP 10054) errors?

I was reviewing a test run this week that had more SQL 17830 (TCP 10054 / 0x2746) errors than I could explain from just kills or login timeouts. I started looking at the output in the connectivity ring buffer and the matching XEvent, error_reported event where error_number = 17830. I thought I would find my […]

Read More

How It Works: SQL Server Lock Iteration / Enumeration

When executing a query to enumerate the locks, such as select * from sys.dm_tran_locks, how does SQL Server scan the locks and avoid impacting the overall concurrency? I recently posted on aspects of the SQL Server Lock Manager and found the iteration of the locks interesting as I was stepping in the code. https://bobsql.com/how-it-works-sql-server-login_stats-sys-dm_exec_sessions https://bobsql.com/how-it-works-sql-server-lock-partitioning […]

Read More

How It Works: SQL Server LOGIN_STATS (sys.dm_exec_sessions)

To track the login statistical information, enable the sp_configure value ‘common criteria compliance enabled‘ setting and restart SQL Server. The sys.dm_exec_sessions DMV is one way to query the statistical login information. The rows shown in this example belong to the sa user on my system. The values are different because the values are obtained when […]

Read More

How It Works: SQL Server Lock Partitioning

Previous posts have discussed partitioning, for example, a partitioned memory object https://blogs.msdn.microsoft.com/psssql/2011/09/01/sql-server-20082008-r2-on-newer-machines-with-more-than-8-cpus-presented-per-numa-node-may-need-trace-flag-8048/ and how a latch maintains the desired access (exclusive, shared, …) https://blogs.msdn.microsoft.com/psssql/2009/01/28/hot-it-works-sql-server-superlatching-sub-latches/ Quick Refresher Over Partitioned Protection/Locking Acquiring shared access requires only the local partition be acquired (lightweight scalability) Acquiring exclusive access requires all partitions be acquired (heavier and can be slower) Shared […]

Read More

SQL Server on IoT Edge and Developer Machines – Smaller Footprint

SQL Server will ship Azure SQL Database Edge: https://azure.microsoft.com/en-us/services/sql-database-edge With the announcement I can tell you more about one of the things we have been working on; SQL Server running on IoT Edge and Developer machines in under 500MB of memory. The effort goes beyond IoT Edge devices and extends to the common developer experience. […]

Read More

SQL Mysteries: Causality tracking vs Event Sequence for XEvent Sessions

You need to make sure to order the events by event_sequence when doing analytics on the XEvent data. It has been proposed to me that you can use the causality tracking sequence as a sort target, which may work as long as you understand how the activity ids are associated with the parent and the […]

Read More

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 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

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

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

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

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

SQL Server on Linux: CU4 – NewSequentialId() – Uuid

  Prior to SQL Server 2017 CU4 for Linux (Linux only) the generation of a sequential UUID may not function as expected.  SQL Server’s NewSequentialId() calls the Windows API UuidCreateSequential, which is limited in SQLPAL, prior to the CU 4 update. SQL Server on Linux – CU4 advances the logic in the SQLPAL, in support […]

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

SQL Server on Linux: Kerberos Troubleshooting, Hints and Tips and Hard Code Debugging

SQL Server on Linux uses the GSSAPI and SSSD service for Active Directory (AD) authentication activities.   Thus, Kerberos is the path for success for AD authentication and just in case you have to troubleshoot a problem I have a few tips. My Short Story I was encountering an issue on my Ubuntu 16.04 system but […]

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

SQL Server Mysteries: The Case of the Suspended, Awaiting Command, and Blocked Session

  I ran into a scenario showing my session as suspended, awaiting command and blocked!  My first thought was that this had to a bug. As many of us have documented, for years, a sleeping, awaiting command, session is a session waiting on the client to submit a TSQL command to the SQL Server. The […]

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

SQL Server Mysteries: The Case of the Not 100% RESTORE…

I recently visited a customer onsite and presented to them topics on SQL Server 2016. After the talk, I opened up the floor for the audience to ask me questions. One question I got went like this “I’ve tried to restore a database on SQL Server using the WITH STATS option. When I run the […]

Read More

SQL Server Mysteries: The Case of the Not 100% RESTORE…

I recently visited a customer onsite and presented to them topics on SQL Server 2016. After the talk, I opened up the floor for the audience to ask me questions. One question I got went like this “I’ve tried to restore a database on SQL Server using the WITH STATS option. When I run the […]

Read More

SQL Server On Linux: Core-minidumps and Breakpad

As pointed out in my previous post, debugging on Linux brings a few new twists to those of us used to the Windows debugging landscape.  One of these twists is the need to produce a core dump. On Linux a common way to capture a dump is to generate a core dump (gcore, etc.)  You […]

Read More

SQL Server on Linux: Scatter/Gather == Vectored I/O

Scatter/gather capabilities allow more efficient memory to disk transfers reducing redundant memory copies, sorting and other activities applications may require to gain improved I/O performance. If my memory serves me correctly SQL Server started using the ReadFileScatter and WriteFileGather APIs in SQL Server 6.5 SP3.  It may not have been this exact build but as […]

Read More

SQL Server on Linux: An LLDB Debugging Tale

You are aware of our statements indicating “Microsoft Loves Linux.”  Over the last couple of years the open source activities at Microsoft have accelerated all around me.  Recently I blogged about the design of of the debugger bridge and the use of LLDB.  In this post I want to highlight my recent debugging session into […]

Read More

SQL Server Mysteries: The Case of TDE and Permanent Tempdb Encryption

I’m a huge Sherlock Holmes fan (I’ve read all the books, watch Elementary on CBS every week, and loved the most recent season Four of Sherlock) so when I recently got a question about some unexplained behavior for SQL Server, I thought of the idea of posting some of these as I get and solve […]

Read More

SQL Server on Linux: Debugging ELF and PE Images (dbgbridge)

In my last post I highlighted the marriage of PE and ELF images within the same process space to build SQL Server on Linux.  In this post I will expand upon the dbgbridge component, as mentioned by Slava in his latest channel 9 video. The dbgbridge (Debugger Bridge) is a critical component in the SQL […]

Read More

SQL Server on Linux: ELF and PE Images Just Work

Last March I moved from 22 years in SQL Server support to the SQL Server development team, working on SQL Server on Linux project and reporting to Slava Oks.  As Slava highlights in his recent blog post, he also contacted me in early 2015 to assist with supportability of SQL Server on Linux.  I quickly […]

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

The SQL Server Installer Version 2: Just Install It!

Back in July, I told you about a new installation experience for SQL Server. One of our goals from the outset was to ensure we could iterate and enhance this experience as we obtained feedback from the community. Today, we are proud to announce Version 2 of the new SQL Server Installer. Here is the […]

Read More

SQL Server 2016 – It Just Runs Faster: Always On Availability Groups Turbocharged

When we released Always On Availability Groups in SQL Server 2012 as a new and powerful way to achieve high availability, hardware environments included NUMA machines with low-end multi-core processors and SATA and SAN drives for storage (some SSDs). Performance issues surrounding Availability Groups typically were related to disk I/O or network speeds. As we […]

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

Create Table – Disk vs In-Memory Optimized

I had an interesting e-mail discussion related to the performance of create table.  The test being executed was a tight loop of create table statements.  The assumption put forth was in-memory optimized table creation was slower and they expected it to be faster.  We are not talking about the performance of inserts, updates, deletes and […]

Read More