Press "Enter" to skip to content

Category: Bugs

Adding Debug Logic to T-SQL Procedures

Published 2021-10-13 by Kevin Feasel

Deborah Melkin does not take kindly to bugs:

I often find that I have to write complicated stored procedures where I need to check things as I go along. My go-to for using this snippet is when I write stored procedures that use dynamic SQL. You’d be surprised (or not) at how often I have had to do this over the years. There’s been functionality where the user gets to choose the columns being used, rewriting ORM data layer “catch-all” queries to improve performance, and cross database queries where the name of the database may not be the standard name (think development and QA databases living on the same SQL instance.)

Click through for an example of where the @Debug parameter pays off. My recollection was that, for really long NVARCHAR(MAX) strings, running PRINT by itself might cut off the code after ~4000 characters, but that could be a historical recollection.

1 Comment

Persist Sample Percent Bugfix in SQL Server

Published 2021-09-15 by Kevin Feasel

John Sterritt has good news for us:

Hi Everyone, this is John Sterrett. I am a SQL Server Consultant in Austin, TX. Last year I blogged about a feature called Persist Sample Percent. It had a nasty bug that could negatively impact performance. I have great news to share. The fix is now rolled into SQL 2016 SP2 CU17 and SQL 2019 CU10. Pedro Lopes let me know that with the fix now queued for SQL 2017 CU26, this becomes fixed in all versions.

Read on to see what this mean and why it’s important.

Comments closed

Debugging Memory Access Violation Errors

Published 2021-08-30 by Kevin Feasel

Sean Gallardy explains access violations:

Access Violations (AV) are another common error that will cause SQL Server to take a memory dump. These can occur for a variety of reasons, and unlike the last one (Non-Yielding Scheduler) it’s quite the task, especially given public symbols don’t have structure data and offsets.

Read on to see what they are, what a dump file may include, and why they can be so difficult to debug.

Comments closed

Security Breach in Cosmos DB: ChaosDB

Published 2021-08-27 by Kevin Feasel

Nir Ohfeld and Sagi Tzadik discovered a flaw in Azure Cosmos DB:

Nearly everything we do online these days runs through applications and databases in the cloud. While leaky storage buckets get a lot of attention, database exposure is the bigger risk for most companies because each one can contain millions or even billions of sensitive records. Every CISO’s nightmare is someone getting their access keys and exfiltrating gigabytes of data in one fell swoop.

So you can imagine our surprise when we were able to gain complete unrestricted access to the accounts and databases of several thousand Microsoft Azure customers, including many Fortune 500 companies. Wiz’s security research team (that’s us) constantly looks for new attack surfaces in the cloud, and two weeks ago we discovered an unprecedented breach that affects Azure’s flagship database service, Cosmos DB.

Read on for details about the attack. Microsoft has already mitigated the issue by disabling the functionality necessary to pull off the attack. H/T Ben Stegink.

Comments closed

Dealing with Non-Yielding Schedulers

Published 2021-08-26 by Kevin Feasel

Sean Gallardy breaks up the party:

One of the most common items that will cause a memory dump in SQL Server is a non-yielding scheduler (generally referred to as NYS). What the heck does that mean? Why would it cause a memory dump? Is there anything that can be investigated? Good questions, let’s take a look.

Read on to learn what these are, why they’re not something you want to deal with on a regular basis, and how you can get more information on what happened out of a dump file. Which is also going to be helpful for Microsoft staff to diagnose and correct the underlying issue (if possible).

Comments closed

Ways to avoid the MERGE Operator

Published 2021-08-05 by Kevin Feasel

Michael J. Swart has important bullet points:

Aaron Bertrand has a post called Use Caution with SQL Server’s MERGE Statement. It’s a pretty thorough compilation of all the problems and defects associated with the MERGE statement that folks have reported in the past. But it’s been a few years since that post and in the spirit of giving Microsoft the benefit of the doubt, I revisited each of the issues Aaron brought up.

Some of the items can be dismissed based on circumstances. I noticed that:

– Some of the issues are fixed in recent versions (2016+).

– Some of the issues that have been marked as won’t fix have been fixed anyway (the repro script associated with the issue no longer fails).

– Some of the items are complaints about confusing documentation.

– Some are complaints are about issues that are not limited to the MERGE statement (e.g. concurrency and constraint checks).

Spoilers: some + some + some + some is still a lot less than all. Read the whole thing.

Comments closed

Memory-Optimized tempdb Metadata Bug

Published 2021-07-02 by Kevin Feasel

Mark Wilkinson shines a light on a bug:

Hey folks! Today I’ll share another bug we found recently when testing some new SQL Server 2019 instances in our development environment. This one is a bit concerning as it could affect a lot of shops and the bug presents itself during a pretty common use case.

The unfortunate thing is that ours is exactly the type of environment that memory-optimized tempdb metadata was made for.

Comments closed

Categorizing Why Bugs Can Be Tricky

Published 2021-06-24 by Kevin Feasel

Julia Evans has a list:

Hello! I’m very slowly working on writing a zine about debugging, so I asked on Twitter the other day:

If you’ve run into a bug where it felt “impossible” to understand what was happening – what made it feel that way?

Of course, bugs always happen for logical reasons, but I’ve definitely run into bugs that felt like they might be impossible for me to understand (until I figured them out!)

I got about 400 responses, which I’ll try to summarize here. I’m not going to talk about how to deal with these various kinds of “impossible” bugs in this post, I’ll just try to classify them.

Click through for the major categories, as well as explanations and sub-categories. I think an interesting follow-up to this is to ask why we find ourselves in situations where we get these sorts of bugs and what (if anything) we can do to minimize or eliminate the likelihood of their appearance.

Comments closed

Bug with Filtered Index on Computed Column

Published 2021-06-21 by Kevin Feasel

Erik Darling points out a weird bug:

At some point in the past, I blogged about a silent bug with computed columns and clustered column store indexes.

In this post, I’m going to take a quick look at a very loud bug.

Normally, you can’t add a filtered index to a computed column. I’ve always hated that limitation. How nice would that be for so many currently difficult tasks?

Click through to see how you can create a filtered index against a computed column, as well as all of the pain it provides.

Comments closed

Recursive UDF Bug in SQL Server 2019

Published 2021-05-12 by Kevin Feasel

Erik Darling finds the bugs so you don’t have to:

I see people do things like this fairly often with UDFs. I don’t know why. It’s almost like they read a list of best practices and decided the opposite was better.

This is a quite simplified function, but it’s enough to show the bug behavior.

While writing this, I learned that you can’t create a recursive (self-referencing) scalar UDF with the schemabinding option. I don’t know why that is either.

I’m going to go out on a limb and say that if you run into this bug, it’s your own fault.

Comments closed