Press "Enter" to skip to content

Author: Kevin Feasel

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

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

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

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

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

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

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

Row-Level Security in Serverless SQL Pools

Published 2021-05-19 by Kevin Feasel

Jovan Popovic has a method of implementing poor man’s row-level security in Azure Synapse Analytics serverless SQL pools:

Serverless Synapse SQL pools enable you to read Parquet/CSV files or Cosmos DB collections and return their content as a set of rows. In some scenarios, you would need to ensure that a reader cannot access some rows in the underlying data source. This way, you are limiting the result set that will be returned to the users based on some security rules. In this scenario, called Row-level security, you would like to return a subset of data depending on the reader’s identity or role.

Row-level security is supported in dedicated SQL pools, but it is not supported in serverless pools (you can propose this feature in Azure feedback site). In some cases, you can implement your own custom row-level security rules using standard T-SQL code.

Read on to see how.

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

SSMS Templating

Published 2021-05-19 by Kevin Feasel

Kenneth Fisher shows off templating in SQL Server Management Studio:

Several times over the last few weeks I’ve gotten a request to create a new work database. The individuals from this team each have their own database that they can use as a type of scratch pad and I guess they’ve been hiring. It’s simple enough to create the database and then grant the necessary permissions, but let’s face it, after the first time I was already tired of the GUI and scripted the process out. Running the script was better but I quickly became annoyed at having to make changes to the script. User names etc. So I decided to create a more permanent script. My first thought was to use dynamic SQL. While that would work, and I’m certainly comfortable with dynamic SQL, it just didn’t feel right for this. I decided in the end to use a trick from templates. If you set up parameter(s) in the file you can use Ctrl-Shift-M to scroll through them and make changes. In an odd twist these are called template parameters. 

Click through for a demo.

Comments closed