Press "Enter" to skip to content

Curated SQL Posts

July Azure Data Studio Update

Published 2019-07-12 by Kevin Feasel

Alan Yu announces some great things in the July update to Azure Data Studio:

One of the most requested features from customers around the world is enhanced execution plan support. Although we have basic query plan support in Azure Data Studio, it’s not as robust as similar functionality built into SQL Server Management Studio and what other vendors provide.

Today, we’re pleased to announce that one of our valued Microsoft partners, SentryOne is shipping their SentryOne Plan Explorer extension for Azure Data Studio. This is a free extension that provides enhanced plan diagrams for queries that are run in Azure Data Studio, with optimized layout algorithms and intuitive color-coding to help quickly identify the most expensive operators affecting query performance.

The other big thing I like is that notebooks have keyboard shortcuts. These were two of the things keeping me from using ADS as much as I’d wanted. Now I’m that much closer to full-on migration.

Comments closed

Notifications when Admins Connect to a SQL Server

Published 2019-07-12 by Kevin Feasel

Jon Shaulis builds a logon trigger to notify when sysadmins log into his systems:

I was helping someone set up some monitoring in their database and they were asking about being notified when someone with administrative privileges logs into SQL Server. While many of you know that I try to use the right tool for the right job, some may cringe when I say triggers can help out in this scenario.

Now, there are other methods here but the goal was to be notified when someone logs into your SQL Server with administrative privileges. Triggers will consistently fire when this event occurs and allows you to perform an action based on this event.

Just make sure you get the trigger right and don’t block everybody from logging in. That’s an awkward situation.

Comments closed

Adding Line Breaks on DAX Measures

Published 2019-07-12 by Kevin Feasel

Gilbert Quevauvilliers shows how you can add line breaks to DAX measures for formatting results in Power BI:

I was at a customer and they were drilling through between pages, and I wanted to put in what the filter selections were when they drilled through to the new page.

This would allow them to easily see what had been on the previous page, and to avoid going backwards and forwards.

I quickly created the DAX measure to show the filters, but one thing that bugged me was that it looked a bit clunky and not clear in terms of what the filters were.

I knew that I wanted to use a Line Break to put each filter on a new line. 

Read on to see how you can do this.

Comments closed

Using Show-Command in Powershell

Published 2019-07-12 by Kevin Feasel

Kenneth Fisher walks through the Show-Command cmdlet in Powershell:

Years ago I blogged about how I like to use the SSMS scripting feature to learn how to do things. Well now I’m starting to learn Powershell and it turns out there is a GUI here as well that will help me learn to script. At least in a very basic way. For example, if I want to see what parameters are actually available for Get-Help and maybe script out a template to work with then I can do this:

Show-Command Get-Help

Read on to see how you can put this into action.

Comments closed

Importing Biml Metadata from Excel

Published 2019-07-12 by Kevin Feasel

David Stein wraps up a series on using Biml to load flat files:

Make sure you read and understand the concepts in each of the following articles before tackling this one.

– Import Biml Metadata with GetQuerySchema
– BimlScript Code Nuggets and Mad Libs
– Import Biml Metadata Directly from Excel

One of the primary issues I’ve mentioned in each of the preceding short cut steps is a lack of real automation. Since we didn’t have our metadata externalized, we needed to copy/paste some object, T-SQL file, Biml file, or Excel Spreadsheet in order to create new extracts.

The final post in the series contains quite a bit of code, too.

Comments closed

Kafka Docker on Kubernetes

Published 2019-07-11 by Kevin Feasel

Bill Ward gives us a step-by-step set of instructions for installing Kafka Docker on Kubernetes:

In this ultimate guide I will give you a simple step-by-step tutorial on installing Kafka Docker on Kubernetes. This post includes a complete video walk-through.

There has been a lot of interest lately about deploying Kafka to a Kubernetes cluster. If you are wanting to take the deep dive yourself then you found the right article. Now that we have Kafka Docker, deploying a Kafka cluster to Kubernetes is a snap.

This makes it even easier to get started with Kafka in a development environment.

Comments closed

Spark and dotnet in a Single Container

Published 2019-07-11 by Kevin Feasel

Ed Elliott shows how you can combine Spark and .NET Core in a single Docker container:

This is quite new syntax in docker and you need at least docker 17.05 (client and daemon), after the images “FROM blah” you can specify a name “core” in this case, then later you can copy from the first image to the second using “–from=” on the “COPY” command.

In this dockerfile I have added Spark 2.4.3 and the default environment variables we need to get spark running, if you grab this dockerfile and run “docker build -t dotnet-spark .” you should get an images you can then run which includes the dependencies for dotnet as well as spark.

Ed includes all of the scripts needed to test this out, too.

Comments closed

Memory Defaults in SQL Server 2019

Published 2019-07-11 by Kevin Feasel

Randolph West looks at a new settings tab in the SQL Server 2019 installation:

In 2016 I created the Max Server Memory Matrix as a guide for configuring the maximum amount of memory that should be assigned to SQL Server, using an algorithm developed by Jonathan Kehayias.

SQL Server 2019 is still in preview as I write this, but I wanted to point out a new feature that Microsoft has added to SQL Server Setup, on the Windows version.

On the Database Engine Configuration screen are two new tabs, called MaxDOP and Memory. These are both new configuration options for SQL Server 2019. Last week we looked at MaxDOP, and this post will specifically look at the Memory tab.

It’s a small change but a nice one.

Comments closed

Extended Events Files on Linux

Published 2019-07-11 by Kevin Feasel

Jason Brimhall looks at an error when trying to set up an Extended Events session on Linux:

This will fail before the query really even gets out of the gate. Why? The proc xp_create_subdir cannot create the directory because it requires elevated permissions. The fix for that is easy enough – grant permissions to write to the Database directory after creating it while in sudo mode. I will get to that in just a bit. Let’s see what the errors would look like for now.

Msg 22048, Level 16, State 1, Line 15
xp_create_subdir() returned error 5, ‘Access is denied.’
Msg 25602, Level 17, State 23, Line 36
The target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”, encountered a configuration error during initialization. Object cannot be added to the event session. The operating system returned error 5: ‘Access is denied.
‘ while creating the file ‘C:\Database\XE\PREEMPTIVE_OS_PIPEOPS_0_132072025269680000.xel’

Read on for the solution.

Comments closed