SQL Server According to Bob

Two guys who work on SQL Server

Author: admin

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

How It Works: Sync IOs in nonpreemptive mode longer than 1000 ms

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

COLD, WARM, HOT … CLEAN, DIRTY, FREE … What Does All This Mean?

I was asked about these terms from a blog reader.  As I thought about these terms and how to blog about them I became concerned that I might just be as clear as mud, but I will do my best. Each of the terms means something within a specific context.   For example, when your teenager […]

Read More

How It Works: Session/SPID (–2) for DTC Transactions

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

How It Works: Reader / Writer Synchronization

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

The SQL Server Basic Installer: Just Install It!

20+ years ago when I joined Microsoft I was handed a diskette (maybe it was two), and was told “Here is SQL Server. Go install it”. So I proceeded to install SQL Server 4.20 on my Windows NT 3.1 desktop machine (I won’t tell you the hardware details. It would scare you). There was a […]

Read More

DBCC Trace Flags 2562 and 2549

  Erin Stellato and Jonathan Kehayias from sqlskills reached out asking for clarification of trace flags 2562 and 2549 behavior.  Trace flags 2562 and 2549 are documented in knowledgebase article: https://support.microsoft.com/en-us/kb/2634571 and the blog post https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-dbcc-scales-7x-better/ highlights the SQL 2016 DBCC performance improvements. For SQL Server 2008, 2012 and 2014 the trace flag behavior remains […]

Read More

How it Works: XEL Health Session and Shutdown

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

What Am I Working On (RDORR): SQL Server On Linux

To some of you SQL Server On Linux is old news from various announcements made a couple of months ago.  To others this is new information.  Yes, SQL Server runs on Linux!  We are in active, private preview and working towards the public preview release. Read More: https://www.microsoft.com/en-us/server-cloud/sql-server-on-linux.aspx Sneak Peak Demo: https://channel9.msdn.com/Shows/Data-Exposed/SQL-Server-on-Linux-Sneak-Peak It was Jan […]

Read More

Does SQL Server 2016 Require Trace Flag -T8048?

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

Unresolved Deadlock vs Scheduler Deadlock

These are distinct errors but I have found that many people blend them together.  It really takes an entire chapter to explain these conditions so I am going try to do it in a page or two? Unresolved Deadlock An unresolved deadlock is generally a SQL Server product issue.   When an unresolved deadlock is detected […]

Read More

SQL 2016–Install MSVC Patch Required

I can’t emphasize this patch enough.   There is a MSVC, runtime library patch needed by SQL Server 2016 and without the patch the SQL Server service can simply terminate (crash.)  This may not produce stack dumps and the SQL Server error log often looks like it simply terminates (no logging of shutdown.) For complete instructions […]

Read More

How It Works: How SQL Server Determines Logical and Physical Processors

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

How It Works: SQL Server 2016 SSE/AVX Support

My 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

SQL 2016 – It Just Runs Faster Announcement

SQL Server 2016  ‘It Just Runs Faster’   A bold statement that any SQL Server professional can stand behind with confidence.   My development collogues and I are starting a regular blog series, outlining the vast range of scalability improvements, allowing SQL Server 2016 to run across a wide array of hardware configurations, faster and better […]

Read More

SQL 2016 – It Just Runs Faster: DBCC Scales 7x Better

Many of you have experienced (MULTI_OBJECT_SCANNER* based) waits while running DBCC CHECKS*(checkdb, checktable, …)   Internally DBCC CHECK* uses a page scanning coordinator design (MultiObjectScanner.)  SQL Server 2016 changes the internal design to (CheckScanner), applying no lock semantics and a design similar to those used with In-Memory Optimized (Hekaton) objects, allowing DBCC operations to scale […]

Read More

SQL 2016 – It Just Runs Faster: DBCC Extended Checks

Last week’s post (SQL 2016 – It Just Runs Faster: DBCC Scales 7x Better) talked about several improvements to DBCC CHECKDB to make it run faster. In today’s post, we will talk about additional improvements to extended logical checks. When checking database consistency using DBCC CHECKDB, in addition to the amount of data or number […]

Read More

SQL 2016 – It Just Runs Faster: Native Spatial Implementation(s)

Spatial data is among, if not the fastest, growing storage types for the SQL Server database.   It is common place for customers to have 3,000,000 or more rows in a database.   Customers are tracking vehicles, delivery locations, drilling positions and much more, leveraging the information to efficiently run their business.   To fully appreciate the […]

Read More

SQL 2016 – It Just Runs Faster: TVPs with Spatial Column(s)

Table Valued Parameters (TVPs) containing spatial columns can be used as input parameter(s) to stored procedures.  SQL Server 2016 improves the scalability, using native spatial validation(s), increasing performance by 15 times or more.   TVP Before the Fix:  8000 rows/sec TVP After the Fix: 120,000 rows/sec   ‘It Just Runs Faster’ – Apply SQL Server […]

Read More

SQL 2016 – It Just Runs Faster: Spatial Index Builds Faster

Index creation and tessellation are often intensive, spatial activities.    Along with the native and TVP, spatial enhancements additional work to optimize index creation and tessellation was completed.   Testing reveals that building a spatial index on SQL Server 2016, with the improved design, can be more than 2 times faster than SQL Server 2012 or […]

Read More

SQL 2016 – It Just Runs Faster: -T1117 and -T1118 changes for TEMPDB and user databases

Configuration of TEMPDB is often critical to scalability and throughput of SQL Server applications.  The following link (https://support.microsoft.com/en-us/kb/2964518) outlines how to configure SQL Server 2014 and 2012 for optimal scalability and performance.   A SQL Server 2016 primary goal was ‘It Just Works.’  Out of the box a customer should not have to engage in […]

Read More

SQL 2016 – It Just Runs Faster: Automatic TEMPDB Configuration

Various KBs, whitepapers and blogs have outlined the need for the creation of multiple, TEMPDB files, same sized files, trace flags and the like.   All of these configuration options increase the scalability of your SQL Server.   In an effort to simplify the tempdb configuration experience, SQL Server 2016 setup has been extended to configure […]

Read More

SQL 2016 – It Just Runs Faster: LDF Stamped

When creating or growing the database log file (LDF) a byte pattern is stamped.  The pattern establishes the proper log block layout with end of log marker(s.)   SQL Server 7.0 changed the LDF format from the original, 2K, Sybase database page design to sector aligned log blocks stamped with all zeros (0x00).    Creation or […]

Read More