Press "Enter" to skip to content

Month: February 2021

Indicators of Schema Issues

Published 2021-02-04 by Kevin Feasel

Erik Darling has a good list of schema-related issues:

Something is broken in the way that you store data.

You’re overloading things, and you’re going to hit big performance problems when your database grows past puberty.

Most of what he’s describing in this post is a failure of atomicity, which implies a failure to achieve first normal form. Mind you, all of these functions are perfectly reasonable as part of data loading, and many of them are perfectly reasonable in the SELECT clause of a query (though that’s still a sign of failure of atomicity), but once you start throwing them into the WHERE clause, we’ve got problems.

Comments closed

Memory Grants: Query Memory and Workspace Memory

Published 2021-02-04 by Kevin Feasel

Deepthi Goguri continues a series on memory grant internals:

The memory grant for the above query is 183496/8=22937 pages of memory grant. The total available grant for the queries is 92442. We can run the above query 4 times to hit the total memory grant (22937*4=91748). We can use the SQLQueryStress tool to run the query and see the query memory grants. Let us run the query with 100 number of iterations and 10 number of threads.

This has been an interesting series so far and I look forward to seeing the rest of it.

Comments closed

Random Number Generation in T-SQL

Published 2021-02-04 by Kevin Feasel

Chad Callihan generates random numbers:

The first way to generate a random number is by using the SQL Server RAND function. This will return a float value. 

Both of the techniques Chad shows are examples of generating uniform distributions—distributions in which any value is just as likely as any other. There are plenty of places in which a uniform is great: drawing by lot is one of them. But when you’re generating artificial data, the results tend to look unrealistic because not many natural phenomena follow uniform distributions.

If you’re interested in generating numbers which tend to look more realistic when generating artificial data, I have a post on generating random numbers built on a normal distribution.

Comments closed

Getting SQL Agent Jobs and Job Steps

Published 2021-02-04 by Kevin Feasel

Anthony Nocentino takes the dbatools approach to a problem:

Recently I needed to take a look at all of the SQL Server Agent Jobs and their Jobs Steps for a customer. Specifically, I needed to review all of the Jobs and Job Steps for Ola Hallengren’s Maintenance Solution and look at the Backup, Index Maintenance and Integrity Jobs to ensure they’re configured properly and also account for any customizations and one-offs in the Job definitions. This customer has dozens of SQL Server instances and well, I wasn’t about to click through everything in SSMS…and writing this in TSQL would have been a good candidate for a Ph.D. dissertation. So let’s check out how I solved this problem using dbatools.

Click through for the script.

Comments closed

SQL Server Compression Q&A

Published 2021-02-04 by Kevin Feasel

Bob Pusateri has some follow-up Q&A after a session:

I was extremely fortunate to be able to present about data compression at the EightKB SQL Server internals conference last week. If you missed my talk in person, you can now view it, as well as the entire day, on YouTube!

I was able to answer many questions during the session, but there were a few still left after time ran out. I wanted to address them all, so here they are!

Click through for the video as well as some Q&A.

Comments closed

Launching Linux VMs with Firecracker

Published 2021-02-03 by Kevin Feasel

Julia Evans gives us an introduction to Firecracker:

Firecracker says this about performance in their specification:

It takes <= 125 ms to go from receiving the Firecracker InstanceStart API call to the start of the Linux guest user-space /sbin/init process.

So far I’ve been using Firecracker to start relatively large VMs – Ubuntu VMs running systemd as an init system – and it takes maybe 2-3 seconds for them to boot. I haven’t been measuring that closely because honestly 5 seconds is fast enough and I don’t mind too much about an extra 200ms either way.

That’s pretty fast. Click through for more info on installation and configuration.

Comments closed

Histograms versus Bar Charts

Published 2021-02-03 by Kevin Feasel

Alex Velez explains the difference between a histogram and a bar (or column) chart:

Consider the above illustration of two data visualizations. 

A histogram is on the left, and to the right is a bar chart (also known as a bar graph). Histograms and bar charts look almost identical, yet they are dramatically different. Understanding their differences is important, so you know when to use each one and accurately convey—or consume—the insights they contain. 

Let’s take a closer look. 

Click through for that closer look.

Comments closed

DEFINE TABLE in DAX Queries

Published 2021-02-03 by Kevin Feasel

Marco Russo and Alberto Ferrari takes us through the DEFINE TABLE statement in DAX:

Introduced in December 2020, the DEFINE TABLE statement lets you define a calculated table local to a query. The table is not persisted in the model, it exists only for the lifetime of the query. Apart from that, it is a calculated table in every sense of the term albeit with some limitations.

The extension of DAX with the capability to define calculated tables local to a query is needed in order to support composite models (DirectQuery for Power BI datasets and Azure Analysis Services). There are no limitations on the use of the feature, so you can take advantage of local tables in any DAX query. We refer to calculated tables defined in a query as query calculated tables, or query tables for short.

Click through for an example of how it works.

Comments closed

Powershell Editors and Environments

Published 2021-02-03 by Kevin Feasel

Greg Moore gives us an overview of the Powershell IDE landscape:

Before I go too deep into this article, I want to distinguish between editing a file and running it. I’m going to focus on editors here, but most development environments include a way to execute a PowerShell script or PowerShell commands. However, do not confuse the editor with the execution environment.

I used the Powershell ISE for a long while, but eventually stopped because its settings were just different enough from the shell’s settings that things which would work just fine in the ISE would fail when I set them up as automated tasks. I don’t remember what those things were, though, so further research may be required. Nowadays, I’ll use VS Code when I need a proper editor and just wing it on the shell for one-off stuff.

Comments closed