Press "Enter" to skip to content

Month: April 2021

Semi-Join Plan Weirdness

Published 2021-04-12 by Kevin Feasel

Erik Darling has an interesting scenario for us:

This post isn’t meant to dissuade you from using EXISTS or NOT EXISTS when writing queries. In fact, most of the time I think they make a lot of sense.

But weird things can happen along the way, especially if you don’t have supporting indexes, or if supporting indexes aren’t chosen by the optimizer for various reasons.

In this post, I’m going to show you a query plan pattern that can occur in semi-join plans, and what you can do about it.

Click through for the problem and the solution. Me? I don’t like semi-joins on principle. Either join or don’t join; give me none of these cowardly half-measures. I’m not sure what to think about anti-semi-joins because I’m apparently anti semi-join for the purposes of this belabored joke, but I’m a bit suspicious of them as well.

Comments closed

Page and Data Row Structure in SQL Server

Published 2021-04-12 by Kevin Feasel

Deepthi Goguri digs into data page internals:

Each byte you see in the picture has a purpose. The first two blocks containing a byte, Status Bit A and Status Bit B contains the bitmap information about the row, like if the row is logically been deleted/ghosted, row type information, versioning tag, if the row contains any NULL values, Variable length columns. The next 2 bytes is used for storing the length of the fixed length data. The next n bytes are for storing the fixed length data itself. There is a null bitmap after that which will have both the 2-byte column count in the row and null bitmap array. Regardless of if the column in null or not, each and every column will have one bit per every column.

Read on to see how those 8kb pages fill up so quickly.

Comments closed

Securing the sa Login

Published 2021-04-12 by Kevin Feasel

Chad Callihan has a few tips for making that sa login safer:

Every SQL Server install includes the sa login as a sysadmin. This can be good for consistency; however, that also makes it a prime target for attackers trying to get into your SQL Server. That is one of many reasons why you should make the following changes to protect your sa login from being used in an attack.

Click through for the list. Troll me says the best answer is to rename sa (like Chad mentions), create a new account called sa, and write a script to add a rule to your firewall banning the IP of anybody who logs in with this account.

Comments closed

Setting Default Parameters in Powershell

Published 2021-04-09 by Kevin Feasel

Jeffrey Hicks doesn’t have time for parameters:

A quick post today to remind you of a way to make PowerShell even easier to use. PowerShell cmdlets and functions obviously help us get a lot done, and most commands offer a number of parameters to customize what needs to be done. Unless you love typing, you probably would like an even easier way to use PowerShell. Let me show you.

Read on to see how. My one concern here would be if you forget about the parameters, you may end up with unexpected results compared to people without your default parameters. This seems to me a case for using these defaults somewhat sparingly.

Comments closed

Trickiness Around CASE Expressions

Published 2021-04-09 by Kevin Feasel

Edwin Sanchez takes us through a few issues you might run into with CASE expressions:

If you are trying to scan the subheadings to see what the issues are, I can’t blame you. Readers, including me, are impatient.

I trust that you already know the basics of SQL CASE, so, I won’t bore you with long introductions. Let’s dig into a deeper understanding of what’s happening under the hood.

Click through for the list.

Comments closed

Windows Server Failover Clustering Error Code 5054

Published 2021-04-09 by Kevin Feasel

Josh Darnell walks us through an error when setting up an Availability Group:

For setting up the environment, I was following this really in-depth guide from former Data Platform MVP and current Microsoft employee Ryan J. Adams: Build a SQL Cluster Lab Part 1

The guide is generally fantastic, and provides a lot of good insight into the non-SQL Server related aspects of setting up an Availability Group. I’d highly recommend checking it out if you’re interested in that sort of thing.

Relevant to this post, he has provided a diagram of how the different networks are configured:

If you’re very experienced with networking, you may already have some idea of what the problem is going to be. Don’t spoil it for everyone else okay?

I’ll admit I did not have an idea of what the problem was.

Comments closed

HammerDB CLI for Oracle Running on Azure

Published 2021-04-09 by Kevin Feasel

Kellyn Pot’vin-Gorman goes through a rough experience:

Disclaimer: I’m not a big fan of benchmark data.  I find it doesn’t provide us as much value in the real world as we’d like to think it does.  As Cary Milsap says, “You can’t hardware your way out of a software problem” and I find that many folks think that if they just get the fastest hardware, their software problems will go away and this just isn’t true.  Sooner or later, it’s going to catch up with you-  and it rarely tells you what your real database workload needs to run most efficiently or what might be running in your database that could easily be optimized to save the business time and money.

The second issue is that when comparing different workloads or even worse, different platforms or applications, using the same configuration can be detrimental to the benchmarks collected, which is what we’ll discover in this post.

That said, Kellyn dives into the problem and documents several of the issues in building out this test.

Comments closed

The Pain of OR Clauses

Published 2021-04-09 by Kevin Feasel

Erik Darling wants you to embrace the healing power of AND:

This is one of my least favorite query patterns, because even with appropriate indexes, performance often isn’t very good without additional interventions.

Without indexes in place, or when “indexes aren’t used”, then the query plans will often look like one of these.

Maybe not always, but there are pretty common.

It’s something that I do wish the optimizer could be smarter about. One important thing to note in Erik’s demo: the OR clause is on two different columns, so SELECT x.Col1 FROM dbo.TblX x WHERE x.ID = 8 OR x.ID = 7 works fine, but WHERE x.ID = 8 OR x.SomethingElse = 14 is liable to cause performance issues on a large enough table.

Comments closed

Restoring a TDE Database to a Different Server

Published 2021-04-09 by Kevin Feasel

Chad Callihan reminds us of the other half of backups:

You setup TDE for your database. Great! Now you need to restore that database to a different server. Maybe you’re migrating off of an older server or maybe there is testing to be completed for an upcoming release. If we try to backup/restore or attach the database on a different server we’ll get some variation of an error stating “cannot find server certificate with thumbprint…” and the process will fail. There are a few steps to get the TDE database restored and we’re going to walk through them today.

Read on for those instructions.

Comments closed

Finding Spinlock Owners in Dump Files

Published 2021-04-08 by Kevin Feasel

Forrest McDaniel leaves us with a head injury:

Now, I may not be an expert, but sqlmin!Spinlock sounds like…a spinlock. This thread has been spinning for over a minute, never returning to a waiting state, because something else is holding the spinlock resource.

Thankfully, helpful friends alerted me to a blog that revealed the value of an acquired spinlock “is the Windows thread ID of the owner.” Meaning I might be able to find the cause.

Read the whole thing.

Comments closed