Press "Enter" to skip to content

Author: Kevin Feasel

Converting Docker Compose Files with Kompose

Published 2019-07-11 by Kevin Feasel

Andrew Pruski shows how you can convert a Docker compose file to something which Kubernetes can read using Kompose:

This will spin up one container running SQL Server 2019 CTP 3.1, accept the EULA, set the SA password, and set the default location for the database data/log/backup files using named volumes created on the fly.

Let’s convert this using Kompose and deploy to a Kubernetes cluster.

To get started with Kompose first install by following the instructions here. I installed on my Windows 10 laptop so I downloaded the binary and added to my PATH environment variable.

It looks pretty straightforward to use; check it out.

Comments closed

Creating a Columnstore Index

Published 2019-07-11 by Kevin Feasel

Monica Rathbun shows a scenario where creating a clustered columnstore index can make data retrieval much faster:

Using AdventureworksDW2016CTP3 we will work with the FactResellerSalesXL table which has 11.6 million rows in it. The simple query we will use as a demo just selects the ProductKey and returns some aggregations grouping them by the different product keys.

First, we will run the query with no existing columnstore index and only using the current clustered rowstore (normal) index. Note that I turned on SET STATISTICS IO and TIME on. These two SET statements will help us better illustrate the improvements provided by the columnstore index. SET STATISTICS IO displays statistics on the amount of page activity generated by the query. It gives you important details such as page logical reads, physical reads, scans, and lob reads both physical and logical. SET STATISTICS TIME displays the amount of time needed to parse, compile, and execute each statement in the query. The output shows the time in milliseconds for each operation to complete. This allows you to really see, in numbers, the differences.

Click through for the example.

Comments closed

SUMX() in Power BI

Published 2019-07-11 by Kevin Feasel

Rob Collie explains the power of SUMX() in DAX:

Have you ever written an array formula in Excel?  (Don’t worry, most people haven’t).  Have you ever written a FOR loop in a programming language?  (Again, don’t worry, there’s another question coming).  Have you every repeated something over and over again, slowly building up to a final result?

That’s what SUMX() does.  It loops through a list, performs a calc at each step, and then adds up the results of each step.  That’s a pretty simple explanation, but it produces some results in pivots that are nothing short of spectacular.

Read on for a few examples.

Comments closed

Amazon Elasticsearch Alerts

Published 2019-07-10 by Kevin Feasel

Jon Handler shows how to create alerts for Amazon Elasticsearch Service:

On April 8, Amazon ES launched support for event monitoring and alerting. To use this feature, you work with monitors—scheduled jobs—that have triggers, which are specific conditions that you set, telling the monitor when it should send an alert. An alert is a notification that the triggering condition occurred. When a trigger fires, the monitor takes action, sending a message to your destination.

This post uses a simulated IoT device farm to generate and send data to Amazon ES.

Click through for a demo.

Comments closed

R 3.6.1 Available

Published 2019-07-10 by Kevin Feasel

David Smith notes a new version of R is available:

On July 5, the R Core Group released the source code for the latest update to R, R 3.6.1, and binaries are now available to download for Windows, Linux and Mac from your local CRAN mirror.

R 3.6.1 is a minor update to R that fixes a few bugs. As usual with a minor release, this version is backwards-compatible with R 3.6.0 and remains compatible with your installed packages. 

Click through for the changes. There is one nice addition around writeClipboard but otherwise it’s a release where you probably update if you’re bothered by a bug it fixes and otherwise skip.

Comments closed

Order of Execution on SELECT Expressions

Published 2019-07-10 by Kevin Feasel

Bert Wagner digs into an interesting topic:

Success! But as I was celebrating my dynamic SQL victory, I realized I was making an assumption about SQL Server that I had never thought about before:

The above query only works because SQL Server is executing the variables in the SELECT list sequentially. I’m incrementing @CurrentRow only after processing my @RowQuery variable, and this logic only works correctly if SQL Server executes the variable expressions in the order they appear in the SELECT list. If SQL Server was executing items in the SELECT list in reverse or random order, @CurrentRow could potentially get set BEFORE @RowQuery was evaluated, causing the logic of adding “UNION ALL SELECT” in the right location to fail.

I have a vague recollection that you couldn’t always count on this, though I admit to never having seen a counter-example. I don’t believe expression execution order is defined in the ANSI SQL standard—the idea is that everything is typically resolved “at once.”

Comments closed

Nested Window Functions in ANSI SQL

Published 2019-07-10 by Kevin Feasel

Itzik Ben-Gan covers a hypothetical feature in the ANSI standard:

The standard nested window functions seem like a very powerful concept that enables a lot of flexibility in interacting with different points in windowing elements. I’m quite surprised that I cannot find any coverage of the concept other than in the standard itself, and that I don’t see many platforms implementing it. Hopefully this article will increase awareness for this feature. If you feel that it could be useful for you to have it available in T-SQL, make sure to cast your vote!

Check it out and upvote if you’re interested in seeing this functionality in SQL Server.

Comments closed

Scheduled Refresh on Power BI Custom Connectors

Published 2019-07-10 by Kevin Feasel

Kasper de Jonge shows how to enable scheduled refresh on custom connectors in Power BI:

Here is a post that is long overdue as the functionality has been available for almost a year now. Today we will enable scheduled refresh to the Strava custom connector I build before.

The trick to this is that we need to extend the code of the Strava connector with a “TestConnection” function as is described here. This will allow the Power BI gateway to know how to test the connection.

There’s just a little bit of code involved.

Comments closed

Stats Time Versus Plan Operator Time

Published 2019-07-10 by Kevin Feasel

Erik Darling explains why SET STATISTICS TIME ON can give you different timing results from what the execution plan states:

Here are the relevant details:

SQL Server Execution Times:
CPU time = 3516 ms,  elapsed time = 3273 ms.

What looks odd here is that CPU and elapsed time are near-equal, but the plan shows parallelism.

Thankfully, with operator times, the actual plan helps us out.

The query itself ran for <900ms.

The answer makes perfect sense.

Comments closed

Security Update for SQL Server

Published 2019-07-10 by Kevin Feasel

K. Brian Kelley notes a slew of patches for July:

CVE-2019-1068 | Microsoft SQL Server Remote Code Execution Vulnerability

It’s a remote code exploit, but the attacker has to be connected to SQL Server because the vulnerability can only be exploited using a specially crafted query. The code would execute in the context of the database engine service account (hopefully not configured to run with administrative rights on the server or elevated rights in Active Directory).

Check this out and get it patched.

Comments closed