Press "Enter" to skip to content

Curated SQL Posts

Monitoring Power BI Data Gateway Performance

Published 2020-02-17 by Kevin Feasel

Olya Musokhranova shows how we can track throughput and utilization of the Power BI On-Premises Data Gateway:

In June 2019, Microsoft made Gateway Monitoring much easier via the addition of structured logs that can be enabled in the configuration file, and by providing a starter Gateway Performance Power BI report template to visualize the results.

These logs give full access to the information, minimizing the need to pull counter data from IT management systems, like the Microsoft System Center Operations Manager (SCOM); or run the Windows Performance Toolkit and dig through dust-covered gateway info and error log files; or pull refresh error messages from the Power BI Service. Enabling structured Gateway Monitoring logging doesn’t add any significant load to the server, so that’s another win.

Read on for a schema and inspiration on how to set up and configure your own dashboard.

Comments closed

NULL Handling Features not in T-SQL

Published 2020-02-17 by Kevin Feasel

Itzik Ben-Gan continues a series complexity around NULL:

When using the offset window functions LAG, LEAD, FIRST_VALUE and LAST_VALUE, sometimes you need to control the NULL treatment behavior. By default, these functions return the result of the requested expression in the requested position, irrespective of whether the result of the expression is an actual value or a NULL. However, sometimes you want to continue moving in the relevant direction, (backward for LAG and LAST_VALUE, forward for LEAD and FIRST_VALUE), and return the first non-NULL value if present, and NULL otherwise. The standard gives you control over this behavior using a NULL treatment clause with the following syntax:

offset_function(<expression>) IGNORE_NULLS | RESPECT NULLS OVER(<window specification>)

There are three good examples of functionality around handling NULL which the current implementation of T-SQL is missing.

Comments closed

T-SQL Checker ADS Extension

Published 2020-02-17 by Kevin Feasel

Daniel Janik has a new Azure Data Studio extension for us:

I’ve created a sample ads extension that checks TSQL syntax in real-time for potential bad practice.

Right now the extension is using regex, which isn’t the best for parsing SQL but it works for the few test cases I’ve added.

I’m hoping that the community can help evolve the project into something really cool; so, I’m asking for your help in making that happen.

Click through for a demo, and check out the GitHub repo.

Comments closed

Power BI Query Diagnostics

Published 2020-02-17 by Kevin Feasel

Paul Turley has a video covering Power BI query performance:

This post demonstrates how the order of steps added to a query can make a big performance difference and drastically effect the number of steps generated by the designer. I’ll demonstrate how to use the new query Diagnostics tools to compare and understand query performance.

The Power Query Editor for Power BI simplifies data transformation processing by generating query steps for each action you perform in the query designer. This whiteboard diagram shows the high-level flow of information through a Power BI solution. Every query has a source (“SRC” in the diagram) followed by a connection. The query consists of a series of transformations (“XForm”) prior to populating a table in the data model.

Read on for a high-level explanation followed by a video which covers the Query Diagnostics feature.

Comments closed

Storing Power BI Audit Logs in Blob Storage

Published 2020-02-17 by Kevin Feasel

Gilbert Quevauvilliers works around a built-in constraint with Power BI Audit Logs:

With the new Power BI Get-PowerBIActivityEvent I wanted to find a way where I could automate the entire process where it all runs in the cloud.

One of the current challenges with the Audit logs is that they only store 90 days, so if you want to do analysis for longer than 90 days the log files have to be stored somewhere. Why not use Azure Blob Storage?

Whilst these steps might appear to be rather technical if you follow them and you have access to an Azure Subscription you can do this too.

Gilbert warns us up-front that this will be a lengthy post and that is quite true. But if you need to hold those audit logs more than 90 days, this is a great way of doing so.

Comments closed

Explaining the HA/DR Licensing Changes

Published 2020-02-17 by Kevin Feasel

Kevin Chant goes into the fairly recent licensing changes for SQL Server:

Which surprised me a bit because these licensing changes have been in-place for a while now. With this in mind, I thought I would discuss them here to raise awareness about the changes.

To clarify, in SQL Server 2019 there have been some big licensing changes about what you can and can’t do on a passive fail-over instance. Especially if you have Software Assurance.

Which I have to admit I am really excited about. Because it opens up some new possibilities which I will explain below. Of course, there are other significant updates in the licensing guide as well.

Read on for the details.

Comments closed

Flink 1.10.0 Released

Published 2020-02-14 by Kevin Feasel

Marta Paes announces the release of Apache Flink 1.10.0:

The Apache Flink community is excited to hit the double digits and announce the release of Flink 1.10.0! As a result of the biggest community effort to date, with over 1.2k issues implemented and more than 200 contributors, this release introduces significant improvements to the overall performance and stability of Flink jobs, a preview of native Kubernetes integration and great advances in Python support (PyFlink).

Flink 1.10 also marks the completion of the Blink integration, hardening streaming SQL and bringing mature batch processing to Flink with production-ready Hive integration and TPC-DS coverage. This blog post describes all major new features and improvements, important changes to be aware of and what to expect moving forward.

Read on for the improvements and let me once more point out the validation of Feasel’s Law.

Comments closed

Installing Spark on Windows 10

Published 2020-02-14 by Kevin Feasel

Gopal Tiwari shows how you can install Apache Spark on Windows 10:

By default, Spark SQL projects do not run on Windows OS and require us to perform some basic setup first; that’s all we are going to discuss in this article, as I didn’t find it well documented anywhere over the internet or in books.

This article can also be used for setting up a Spark development environment on Mac or Linux as well. Just make sure you’ll downloading the correct OS-version from Spark’s website. 

You can refer to the Scala project used in this article from GitHub here: https://github.com/gopal-tiwari/LocalSparkSql.

I’ve seen (and written) installation guides for Spark. This is a good one, as it goes beyond installation and into kicking off a project and ensuring that it works.

Comments closed

Finding the Query Used in DirectQuery Mode

Published 2020-02-14 by Kevin Feasel

Kasper de Jonge shows us how we can find which query ran in DirectQuery mode to populate a Power BI data set:

When you are optimizing your DirectQuery model and you have done all the optimizations on the model already, you might want to run the queries generated by Power BI by your DBA. He then might be able to do some index tuning or even suggest some model changes. But how do you capture them? There are a few simple ways that I will describe here.

Read on for 3 1/2 such methods.

Comments closed

When Transactional Replication Makes Sense

Published 2020-02-14 by Kevin Feasel

Jonathan Kehayias has some good use cases for transactional replication:

Why in the world would you want to use Transactional Replication?  Isn’t that the thing that is impossible to manage, difficult to configure, and always prone to problems? As a consultant, I see a lot of scenarios where every problem becomes a nail because all you have is a hammer. Sometimes another technology can provide a solution to problems that exist but little is known about the technology, and Transactional Replication tends to fall into this scenario in my experience. In this post we are going to take a look some of the more common Transactional Replication use cases as well as some scenarios where it can be used to solve a business problem in a different way. As with anything, Transactional Replication is just another tool to have in your toolbox.

To be fair, merge replication is much more impossible to manage, difficult to configure, and prone to problems…

Comments closed