SQL Server According to Bob

Two guys who work on SQL Server

Category: Uncategorized

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

BCP fundamentals when using format file and UNICODE data.

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

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