Press "Enter" to skip to content

Category: Bugs

Access Violation Querying System Table Functions with Parallelism

Published 2020-10-20 by Kevin Feasel

Eitan Blumin has a write-up of an interesting bug in SQL Server:

The Access Violation error is triggered when an execution plan with parallelism involves specific system table functions. We found that the error occurs ONLY with parallel execution plans.

Therefore, in order to reproduce it, you’ll need:

– SQL Server instance with MaxDOP setting not equal to 1
– At least 2 available CPU cores

Click through for additional details, including a script to generate the same error yourself.

Comments closed

Disabled Indexes Tell No Compression Tales

Published 2020-09-02 by Kevin Feasel

Eric Cobb gives us a warning around disabling indexes:

Here at work we have a very large, very intensive data load that disables and rebuilds indexes as part of the process. We recently added compression to many of the tables and indexes in the database because it was growing quite large (around 28TB at the time). After adding compression, we got the database size down to somewhere around 17TB.

So you can imagine our surprise when the DB size jumped back up to over 30TB after the last data load! In trying to figure out what happened I discovered that most of the data compression was gone.

That’s…not great. Eric shows us a demo as well and notes that it still applies to SQL Server 2019. I’d be apt to call it a bug, myself.

Comments closed

Bug with Halloween Protection and the OUTPUT Clause

Published 2020-07-27 by Kevin Feasel

Paul White writes up a bug report:

Looking at the execution plan, it is hard to see how deleting a row (at the Clustered Index Delete) then inserting it again (at the Clustered Index Insert) could possibly result in a duplicate key in the index. Remember there is only one row, one column, and one index.

Logically, the only way this error can occur is if the Delete operator does not delete the row.

Read the whole thing. It’s probably not something you’ll ever come across yourself, hopefully.

Comments closed

Availability Group Bug when Removing and Adding the Same Database

Published 2020-06-29 by Kevin Feasel

Josh Darnell takes us through a tricky problem:

I came across a bug in SQL Server 2016 where the Availability Group (AG) health check can get stuck in an infinite loop after removing and re-adding a database from an AG.

Unfortunately, I don’t know exactly what version this bug was introduced. I first noticed the problem on SQL Server 2016 SP2 CU7 GDR (13.0.5366.0). It may have existed before then, but I never encountered it.

Read on for a workaround. And hopefully there will be a proper fix soon. Also, it’d be interesting to see if it can be reproduced in 2017 or 2019.

Comments closed

Diagnosing Out of Memory Failures with SQL Server 2017

Published 2020-04-17 by Kevin Feasel

Lonny Niederstadt had a curious issue:

When [Max Server Memory] can be attained by SQL Server, [Target Server Memory] will be equal to [Max Server Memory].  If memory conditions external to SQL Server make [Max Server Memory] unattainable, [Target Server Memory] will be adjusted downward to an attainable value. As workload is placed on the system, [Total  Server Memory] grows toward [Target Server Memory].  That’s typical, expected behavior.

In this case, the story stays boring until after the following midnight.  There wasn’t enough workload to drive much growth of [Total Server Memory] until about 2:15 am, after which [Total Server Memory] grew fairly rapidly.  [Total Server Memory] reached a plateau between 3:00 am and 3:15 am, and then stepped slightly down later.  [Target Server Memory] was never attained.  That’s curious.

Indeed it is. Read the whole thing. And, given that it is labeled as Part 1, stay tuned for Part 2.

Comments closed

Power BI Warning Regarding “Store datasets in enhanced metadata format”

Published 2020-04-13 by Kevin Feasel

Imke Feldmann does not recommend turning on the “Store datasets in enhanced metadata format” setting in Power BI all willy-nilly:

Background

With the march release came function “Store datasets in enhanced metadata format”. With this feature turned on, Power BI data models will be stored in the same format than Analysis Services Tabular models. This means that they inherit the same amazing options, that this open-platform connectivity enables.

Limitations and their consequences

But with the current setup, you could end up with a non-working file which you would have to build up from scratch for many parts. So make sure to fully read the documentation . Now!

Read on to see what has Imke concerned.

Comments closed

Powershell Interactive Debugging in Visual Studio Code

Published 2020-04-08 by Kevin Feasel

Jess Pomfret shows how to use the interactive debugger in Visual Studio Code to troubleshoot an issue in Powershell code:

So I figured I’d take a look and see what was happening and how we could fix it. Now I’m going to be honest with you, my usual method of debugging involves adding Write-Host 'Hi‘, or piping objects to Out-GridView. I did start down this route, but the Get-DbaRegServer function calls an internal function, and things quickly got complicated.

Luckily, the PowerShell extension for VSCode includes a debugger so we can level up our game and use that to track down our issues.

Click through to see how it works.

Comments closed

VARCHAR Columns and Bytecode Version Mismatch in R

Published 2020-04-06 by Kevin Feasel

Dave Mason runs through a tricky problem with SQL Server Machine Learning Services:

During my testing, I’ve found R handles CHAR and VARCHAR data within the input data set as long as the ASCII codes comprising the data is in the range from 0 to 127. This much is not surprising–those are the character codes for the ASCII table. Starting with character code 128, R begins having some trouble. 

Read on to see the problem. Dave’s advice at the end is sound (and frankly, my advice for any string data in SQL Server).

Comments closed