Press "Enter" to skip to content

Curated SQL Posts

An Introduction to Latches

Published 2021-05-03 by Kevin Feasel

Paul Randal starts a series on latches:

In some of my previous articles here on performance tuning, I’ve discussed multiple wait types and how they are indicative of various resource bottlenecks. I’m starting a new series on scenarios where a synchronization mechanism called a latch is a performance bottleneck, and specifically non-page latches. In this initial post I’m going to explain why latches are required, what they actually are, and how they can be a bottleneck.

Read on to learn what a latch is, why it is useful, and how latches work at a high level.

Comments closed

Turning On and Off Calculations in Power BI Visuals

Published 2021-05-03 by Kevin Feasel

Phil Seamark has a clever workaround:

Power BI doesn’t yet have a feature that allows end-users to turn on/off the ability to process calculations for visuals on a report page until they are ready. Most of the time, this is perfectly fine – however, in some instances, it can be handy to disable long-running and heavy calculations from running. At the same time filters/slicers are get selected.

The scenario you most likely want to have this control is when your model uses Direct Query mode against large tables in data sources that charge you for query processing. Even if your Direct Query data source does not charge per query, having a user make quick-fire selections over several slicers can potentially saturate a back-end data-source and unnecessarily chew up resources.

Read on to understand how to use calculation groups to do this, as well as the limitations around this solution.

Comments closed

Measuring DirectQuery Performance

Published 2021-05-03 by Kevin Feasel

Chris Webb shows how you can use the Performance analyzer in Power BI to measure DirectQuery performance:

If you have a slow DirectQuery report in Power BI one of the first questions you need to ask is how long the SQL queries that Power BI generates take to run. This is a more complicated question to answer than you might think, though, and in this post I’ll explain why.

I happen to have access to some of the famous New York taxi data in a Snowflake database, and in there is a table with trip data that has 173 million rows that I have a built a Power BI dataset from. The data and the database used are not really important here though – what is important is that it’s DirectQuery and a large-ish amount of data.

Read on for more information on how it all works.

Comments closed

Plotting XGBoost Trees with R

Published 2021-04-30 by Kevin Feasel

Andrew Treadway shows off a method to visualize the results of training an XGBoost model:

In this post, we’re going to cover how to plot XGBoost trees in R. XGBoost is a very popular machine learning algorithm, which is frequently used in Kaggle competitions and has many practical use cases.

Let’s start by loading the packages we’ll need. Note that plotting XGBoost trees requires the DiagrammeR package to be installed, so even if you have xgboost installed already, you’ll need to make sure you have DiagrammeR also.

Click through for the process. H/T R-Bloggers.

Comments closed

Validating IP Addresses using Powershell

Published 2021-04-30 by Kevin Feasel

Joey D’Antoni needs a good IP address:

I had a client ask me to write a PowerShell script (for an Azure Automation runbook) to automate the creation of firewall rules for an Azure SQL Database. As part of this process, I was planning on having to validate the IP addresses (a valid IP address is of the format x.x.x.x where x is an integer between 0 and 255) by having to write some code to split the IP address string into individual octets and figuring out if each value was a valid number. However, in doing research (searching Stack Overflow), I discovered PowerShell has a built-in IP address data type, which made my life way easier.

Read on to see how you can use this to your advantage.

Comments closed

Just Take My Money: Paying for Power BI Premium per User

Published 2021-04-30 by Kevin Feasel

Wolfgang Strasser shows how hard it can be to let someone take your money in return for goods or services:

Initially I told my customers, purchase the PPU license in the Microsoft 365 portal and thought that it should be an easy deal.. but hey – it’s licensing, Microsoft licensing involved…

The place where to buy the PPU add-on is not that obvious as it looks like.

Read on to learn how to upgrade to Premium per User if you already have a Professional license.

Comments closed

User-Defined Roles in SQL Server

Published 2021-04-30 by Kevin Feasel

Greg Larsen shows us how to create and use user-defined security roles in SQL Server:

When developing an application that accesses SQL Server, you need to set up a security model, so each user gets the access they need based on their job duties. Some applications require the same access for all users, while other application might require different security access rights based on the role a user has in the organization. The best practice for providing user access is to use role-based security. SQL Server provides user-defined Server and Database roles for defining security rules for different user security needs. This article will discuss how to use user-defined server and database roles to provide secure access to SQL Server resources.

This is something I think DBAs tend to under-utilize. Granted, a big part of why I think DBAs under-utilize it is that we often have Windows groups that we can use as the base for roles, but even so, it’s a good way to secure assets in the database.

Comments closed