Press "Enter" to skip to content

Curated SQL Posts

Ignoring Backups in the SQL Server Error Log

Published 2021-05-21 by Kevin Feasel

Garry Bargsley has a solution to an annoyance:

Whether you are new to SQL Server or a seasoned veteran, you will notice odd behavior in the SQL Server Error Log. When a database backup is performed, an entry is put into the SQL Error Log. The SQL Server team decided to log successful backup messages to the Error Log. If you ask most technology professionals, you will find that logging successful events are not really a common occurrence.  This behavior causes a bloated Error Log that can make it hard to find what you need quickly.

Luckily, that same SQL Server team built in a solution to this situation.

Read on to see what the solution is, as well as how to use it.

Comments closed

Executing GitHub Actions via CLI

Published 2021-05-21 by Kevin Feasel

Kevin Chant uses the GitHub CLI:

In this post I want to share some advice about using GitHub CLI with GitHub Actions for Data Platform deployments. Because I showed that at SQLDay last week.

For those who were not aware, there is a GitHub CLI you can use from the command line. You can download GitHub CLI from here.

Anyway, GitHub CLI was recently updated to support commands for GitHub Actions. GitHub Actions is the CI/CD mechanism that is now available in GitHub. Which I have covered in a few other posts, including the one you can find by clicking here.

Click through to learn more.

Comments closed

Temporal Table Performance Scenarios

Published 2021-05-21 by Kevin Feasel

Hugo Kornelis continues a series on temporal table performance:

Welcome to part eighteen of the plansplaining series. Like the previous posts, this one too focuses on temporal tables and their effect on the execution plan. After looking at data modifications in temporal tables and at querying with a most basic temporal form of temporal query, let’s look at the more advanced variations for temporal querying.

We’re still looking at getting data from a single query only in this post. We’ll look at joins in the next post.

Click through for these scenarios.

Comments closed

sqltop — SQL Server Process Viewer

Published 2021-05-21 by Kevin Feasel

Mark Wilkinson has a big announcement:

Hey folks! I’m proud to announce the first open source release of my sqltop tool! sqltop is an interactive command-line based tool to view active sessions on a SQL Server instance. In this post I’ll talk about why I wrote the tool, why I chose to write it in PowerShell, and walk through some of the challenges I faced during development.

I’ve had a chance to see this in action and it’s really cool. I’m glad Mark was able to get this open-sourced, so go check it out.

Comments closed

Running Dask on AKS

Published 2021-05-20 by Kevin Feasel

Tsuyoshi Matsuzaki sets up Dask as a distributed service:

In my last post, I showed you tutorial for running Apache Spark on managed kubernetes, Azure Kubernetes Service (AKS).
In this post, I’ll show you the tutorial for running distributed workloads of Dask on AKS.

By using Dask, you can run Scikit-Learn compliant functions and jobs for data which cannot fit in memory, or run in distributed manners. For simplicity, here I’ll use built-in Dask ML function (dask_ml.linear_model.LinearRegression) in this tutorial. (With the same manners, you can also run regular sklearn functions.)
Cloud managed kubernetes will make you speed up this large ML workloads.

Click through for the process. I’ve had some positive experiences with Dask as a dashboarding tool. It’s definitely one of the better ones if you’re big into Python.

Comments closed

Binary and Hexadecimal in Brief

Published 2021-05-20 by Kevin Feasel

Randolph West has a primer for us:

The CPU can only work with small amounts of data at a time. When the CPU is done with that data, it puts it in memory. Depending on how that data is managed (the on-or-off bit of information is literally called a bit because computer scientists are great at naming things), it usually forms part of a byte, which by convention is eight bits. In other words, you get eight bits of information per byte, represented by a series of 1s (electricity flowed for a fixed period of time) and 0s (no electricity flowed for that fixed period of time).

The uppercase “A” in the Latin alphabet, using the ASCII standard, is represented by the byte 01000001.

So, cool, this is binary. What does it actually mean? 

Read on for more.

Comments closed

Senior DBA Job Questions

Published 2021-05-20 by Kevin Feasel

Joey D’Antoni shares some sample job interview questions for hiring a senior DBA:

In my role as a consultant, it’s rare that I go on an interview anymore, though I occasionally get interviewed by a client, or interview potential DBAs for clients as part of my role. There are a number of these lists on the internet, but many of them are old and focus on trivia questions (there won’t be any questions on what’s clustered versus nonclustered index, but if you are interviewing for a senior role, you should know that anyway. I also like to focus on open ended questions, to gauge the depth of knowledge of the person I’m interviewing.

I will say that when hiring on the database engineer (i.e., development) side, the questions I love best aren’t trick questions; they’re experiential questions. For example, “Here is a common type of problem we need to solve. What would you do in this scenario?” And then we can dive in. As a quick example of one, “You’ve just taken over ownership of a database where most of the clustered index keys are uniqueidentifiers. Would you consider GUIDs a good clustered index candidate?” Then we can talk about yes or no, what makes for a good clustered index, and how you might go about changing it. Oh, and I’ll admit that my thought on this question has changed since Jeff Moden’s outstanding presentation on the topic.

Comments closed

High Availability Options for DBAs

Published 2021-05-20 by Kevin Feasel

Pamela Mooney has a list:

In previous articles in this series, I have stated that the job of the DBA is to make the right data available to the right people as quickly as possible.

Here is where we delve more into the word “available” and take it up a notch. SQL Server offers several options for high availability, and understanding the advantages and caveats of each one will give you the best chance of ensuring the availability of data in any scenario. Let’s discuss the options for high availability in general terms and find out where to go to get more information as you need it.

Due to the breadth of this article and keeping with the idea of just learning the basics, I am not going to cover Azure here except to say that Azure either has compatibility with these features in most of its offerings uses them in background processes.

Read on for the list.

Comments closed

Reasons Your T-SQL Might Fail a Code Review

Published 2021-05-20 by Kevin Feasel

Brent Ozar keeps a list:

It’s hard to set absolute rules about, “Feature X should absolutely never be used.”

However, there are some features that set off alarm bells when I see them. Usually, when I start asking more questions about when we’re using those particular features, I get answers of, “Oh, I didn’t know that was a problem.” As we have a bigger discussion, it leads to the piece of code failing the code review, and going back to the drawing board for improvements.

I’ve definitely done 1-7 on purpose and 8 in protest. 9 is something I do when running ad hoc SQL in SSMS just to make sure that I didn’t goof too hard (and usually it’s a rollback rather than commit). But this is a really good list.

Comments closed

The Value of Bubble Charts

Published 2021-05-19 by Kevin Feasel

Elizabeth Ricks takes us through a surprisingly tricky chart:

An extension of a scatterplot, a bubble chart is commonly used to visualize relationships between three or more numeric variables.  Each bubble in a chart represents a single data point. The values for each bubble are encoded by 1) its horizontal position on the x-axis, 2) its vertical position on the y-axis, and 3) the size of the bubble. Sometimes, the color of the bubble or its movement in animation can represent more dimensions. 

I say surprisingly tricky because it’s easy to overwhelm the user when trying to view bubble charts. I think the best scenarios are cases in which you have relatively few data points and the size element is mandatory.

Hans Rosling (RIP) did an outstanding job of displaying this kind of chart with the Gapminder dataset.

Comments closed