Press "Enter" to skip to content

Curated SQL Posts

Deploying SQL Server To Kubernetes The Easy Way

Published 2019-01-16 by Kevin Feasel

Andrew Pruski doesn’t want to mess with a bunch of yaml files:

In previous posts I’ve run through how to deploy sql server to Kubernetes using yaml files. That’s a great way to deploy but is there possibly an easier way?
Enter Helm. A package manager for Kubernetes.
Helm packages are called charts and wouldn’t you know it? There’s a chart for SQL Server!
Helm comes in two parts. Helm itself is the client side tool, and tiller, which is the server side component. Details of what each part does can be found here.

They’re making it too easy now…

Comments closed

Filtered Index Trickiness

Published 2019-01-16 by Kevin Feasel

Greg Low explains some of the tricky bits behind using filtered indexes:

If you think about it, if all we’re ever going to use is one part of the index, i.e. just the unfinalized rows, having an entry in there for every single row is quite wasteful, as although the vast majority of the index will never be used, it still has to be maintained.
So in SQL Server 2008, we got the ability to create a filtered index. Now these were actually added to support sparse columns. But on their own, they’re incredibly useful anyway.

I use these on occasion but less than I want to, and a big part of the reason why is in this post, particularly around parameters.

Comments closed

The Difficulties Of Database Load Testing

Published 2019-01-16 by Kevin Feasel

Brent Ozar shares some of the trouble you might run into when database load testing:

Managers think that to simulate more load, they can just take the production queries and replay them multiple times, simultaneously, from the replay tool. We’ve already talked about how you can’t reliably replay deletes, but even inserts and updates cause a problem.
Say we’re load testing Stack Overflow queries, and our app does this:

UPDATE dbo.Users
  SET Reputation = Reputation + 1
  WHERE Id = 22656;

If try to simulate more load by running that exact same query from 100 different sessions simultaneously, we’re just going to end up with lock contention on that particular user’s row. We’ll be troubleshooting a blocking problem, not the problem we really have when 100 different users run that same query.

Click through for several issues you can run into and Brent’s advice.

Comments closed

Pobody’s Nerfect: The Andy Mallon Story

Published 2019-01-16 by Kevin Feasel

Andy Mallon shares a great story of a critical business mistake and then overcoming that self-inflicted adversity in a hotel room in Kalamazoo:

I still have vivid memories of that night. I’d ordered pizza so that I could stay back at my hotel room and finish my punch list of things before go-live the next day. It was after 2am, and I was sitting at the kitchen counter of the Residence Inn in Kalamazoo, MI, the pizza box still open next to me as I worked my way through a large pepperoni.

I got to the item on my punch list for “delete all test appointments.” The logic here was pretty simple: All the test appointments were for the same imaginary test patient. Just find all of that person’s appointments, and delete them. I decided I would do this one doctor at a time to make sure I didn’t mess it up too badly.

It’s a harrowing story with a happy ending.

Comments closed

Erasure Coding In Hadoop

Published 2019-01-15 by Kevin Feasel

Guy Shilo explains erasure coding, a new feature in Hadoop 3:

The benefits are, of course, space-saving, and for large files also improved performance (blocks striped across datanodes can be read in parallel, and less blocks are written because there is no x3 replication). The larger the file the more notable is the performance gain.

Erasure encoding is disabled by default and you can enable it for only certain directories in HDFS. Some articles like this one suggest thatbest practice is to enable Erasure coding only for “cold” data that you do not write often, and for “hot” data use regular replication. However, in my tests I did not witness any problem dealing with hot data (maybe it’s evident in larger scales).

Click through for the full story on how it works.

Comments closed

Converting CSV To ORC

Published 2019-01-15 by Kevin Feasel

Mark Litwintschik investigates whether Spark is faster at converting CSV files to ORC format than Hive or Presto:

Spark, Hive and Presto are all very different code bases. Spark is made up of 500K lines of Scala, 110K lines of Java and 40K lines of Python. Presto is made up of 600K lines of Java. Hive is made up of over one million lines of Java and 100K lines of C++ code. Any libraries they share are out-weighted by the unique approaches they’ve taken in the architecture surrounding their SQL parsers, query planners, optimizers, code generators and execution engines when it comes to tabular form conversion.

I recently benchmarked Spark 2.4.0 and Presto 0.214 and found that Spark out-performed Presto when it comes to ORC-based queries. In this post I’m going to examine the ORC writing performance of these two engines plus Hive and see which can convert CSV files into ORC files the fastest.

The results surprised me.

Comments closed

Blinking Lifx Lights Without IFTTT

Published 2019-01-15 by Kevin Feasel

Allison Tharp has a project to blink a set of Lifx lights a team’s color when they score:

The first step is to generate an API token via the Lifx API here (https://cloud.lifx.com/settings). Keep this token safe and don’t let others see it!

In my functions file, I created 3 new functions for controlling the lights: invoke-setLight, invoke-Pulse, and invoke-Breathe. To understand what the API was expecting, I followed the Lifx API documentation here. As far as API documentation goes, this one is pretty good. Most functions have an interactive portion at the bottom which allows you to test it out yourself and also see what inputs the API expects.

As a Bills fan, at least I wouldn’t have to worry about the lights wearing out from overuse.

Comments closed

LISTAGG In Snowflake DB

Published 2019-01-15 by Kevin Feasel

Koen Verbeeck continues investigating Snowflake capabilities:

Since SQL Server 2017, you have the STRING_AGG function, which has almost the exact same syntax as its Snowflake counterpart. There are two minor differences:
– Snowflake has an optional DISTINCT
– SQL Server has a default ascending sorting. If you want another sorting, you can specify one in the WITHIN GROUP clause. In Snowflake, there is no guaranteed sorting unless you specify it (again in the WITHIN GROUP clause).

It looks like LISTAGG is the ANSI standard name, though SQL Server followed Postgres’s lead in calling their function STRING_AGG.

Comments closed

When Synchronous AG Secondaries Are Out Of Sync

Published 2019-01-15 by Kevin Feasel

David Fowler explains that just because an Availability Group is set up as synchronous, doesn’t mean you can never experience data loss on failover:

The primary replica is constantly monitoring the state of it’s secondaries. With the use of a continuous ping, the primary node always knows if the secondaries are up or down.

It’s when SQL detects that one of it’s synchronous replicas goes offline is when interesting things can happen.

So here’s the discussion that came up, if a synchronous replica goes offline for whatever reason, SQL won’t be able to commit any transactions and that means we can be confident that the secondary is up to date, right?

Read on to learn the answer. Which is “no.” But David explains why, so you should read that instead of just having me say it.

Comments closed

Straight Talk On Trace Flags

Published 2019-01-15 by Kevin Feasel

Pam Lahoud explains the purpose of trace flags and talks about a very important trace flag, 4199:

Some trace flags are used to enable enhanced debugging features such as additional logging, memory dumps etc. and are used only when you are working with Microsoft Support to provide additional data for troubleshooting. These trace flags are not ones you want to leave turned on in a production system as they may have a negative impact on your workload. An example of one of these flags would be TF 2551 which is used to trigger a filtered memory dump whenever there is an exception or assertion in the SQL Server process. These trace flags are only used for a short period of time and typically only at the recommendation of Microsoft Support, so they will likely always be around.

If you are a DBA and are not extremely familiar with trace flags, you really want to read this article.

Comments closed