Press "Enter" to skip to content

Curated SQL Posts

The Basics of KQL

Published 2022-02-24 by Kevin Feasel

I take a look at some basic KQL statements:

In order to query data, we need to use the Kusto Query Language, KQL. If you’re familiar with Splunk’s language, KQL is pretty similar. It’s just enough like SQL that it feels like you should understand it but not SQL-like enough that you’ll actually have an intuitive understanding of the language.

One quick note is that all KQL statements are case-sensitive. I personally consider this a mistake in a query language, but they didn’t ask me, I suppose. With that said, let’s get digging.

Seriously, case sensitivity in programming languages is an annoyance at best.

Comments closed

Top Value per Group: Window Function or APPLY

Published 2022-02-24 by Kevin Feasel

Erik Darling hits one of my favorite topics:

The first rule of rewrites is that they have to produce the same results, of course. Logical equivalency is tough.

In today and tomorrow’s posts I’m going to compare a couple different scenarios to get the top value.

There are additional ways to rewrite queries like this, of course, but I’m going to show you the most common anti-pattern I see, and the most common solution that tends to work better.

Click through to see when each works better.

Comments closed

Index Design Guide Updates

Published 2022-02-24 by Kevin Feasel

Kendra Little has a guide for us:

We’ve recently updated the SQL Server and Azure SQL index architecture and design guide. This article is an in-depth guide to indexing in databases using the SQL Server engine, including SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics.

Our recent update adds a table to categorize the types of indexes discussed in the article, clarifies B-trees vs B+ trees, and describes how row locators (aka “secret columns”) are used in nonclustered indexes.

Read on for more information and do check out the guide.

Comments closed

Writing Extended Events to a Table

Published 2022-02-24 by Kevin Feasel

Gianluca Sartori continues a series on XESmartTarget:

The first approach is extremely painful (XML shredding – enough said).

The second approach requires a file target, which is not always available. It also requires to stop the session to read all the data, or write extremely complicated code to read the data incrementally while the session is running (believe me, I did it and my brain still hurts).

This is where XESmartTarget jumps in to help you! All you have to do is write some configuration in a json file and let XESmartTarget do its magic.

Read on to see how XESmartTarget can help you write Extended Events output to a table without the pain.

Comments closed

Restarting SQL Server during TDE Encryption

Published 2022-02-24 by Kevin Feasel

Tom Collins takes a risk:

I am planning on TDE encrypting a bunch of  large SQL Server databases. Due to potential IO contention issues during the work day , the plan is to encrypt these databases during the out of hrs period. If the encryption is triggered , but there is a SQL Server service restart during the encryption process , will  the encryption process be impacted when the SQL Server service restarts?

Click through to see what happens.

Comments closed

Stress Testing Power BI Embedded

Published 2022-02-24 by Kevin Feasel

Kristyna Hughes puts Power BI to the test:

For example, one instance may have a very large data model that takes a lot of memory and CPU time to refresh, 20 users at peak viewing times, hourly refreshes, and the queries are all very simple and allow for query folding. Another business may have six smaller data models, 950 users at peak viewing times, daily refreshes, and the queries populating the data model are all very very complex. All of these elements impact the usage at any given time, making predicting overall CPU needs nearly impossible. Thankfully, stress testing your capacity gives us an option that is not purely reactionary.

This blog will walk through how to stress test your capacity, the elements of capacity planning, and how to understand the results of the stress test.

Read on to see how, using a step-by-step guide.

Comments closed

Deploying SQL Scripts via Azure Release Pipelines

Published 2022-02-24 by Kevin Feasel

Meagan Longoria solves a problem:

We chose release pipelines over the YAML pipelines because it was easier to manage for the client and pretty quick to set up. While I had done this before, I had a couple of new challenges:

– I was deploying to an Azure SQL managed instance that had no public endpoint.

– There were multiple databases for which there may or may not be a change script to execute in each release.

This took a bit longer than I expected, and I enlisted my coworker Bill to help me work through the final details.

Read on to see how Meagan and Bill solved the problem.

Comments closed

De-Scalafication in Flink

Published 2022-02-23 by Kevin Feasel

Seth Wiesman has a post leaving me feeling a little bittersweet:

If you have worked with a JVM-based application, you have probably heard the term classpath. The classpath defines where the JVM will search for a given classfile when it needs to be loaded. There may only be one instance of a classfile on each classpath, forcing any dependency Flink exposes onto users. That is why the Flink community works hard to keep our classpath “clean” – or free of unnecessary dependencies. We achieve this through a combination of shaded dependencies, child first class loading, and a plugins abstraction for optional components.

The Apache Flink runtime is primarily written in Java but contains critical components that forced Scala on the default classpath. And because Scala does not maintain binary compatibility across minor releases, this historically required cross-building components for all versions of Scala. But due to many reasons – breaking changes in the compiler, a new standard library, and a reworked macro system – this was easier said than done.

They did it, which means less Scala in the code base. But it also means that you aren’t tied to a particular version of Scala in your own code. I’m happy about it on the whole but it does expose a frustrating pain point with Scala.

Comments closed

Using KQL to Extract JSON Data in Power BI

Published 2022-02-23 by Kevin Feasel

Dany Hoter shreds some JSON:

In Kusto (aka Azure Data Explorer aka ADX) you can have columns in a table that contain JSON structures.

In KQL it is very easy to extract elements from these columns and use them as regular columns.

It requires more resources but overall, it is standard.

An example can be found in the table TransformedMetrics in the SampleMetrics databases in the help cluster.

Click through for that process.

Comments closed