Press "Enter" to skip to content

Curated SQL Posts

Updates to Intelligent Query Processing in SQL Server 2022

Published 2022-09-16 by Kevin Feasel

Derek Wilson and Kate Smith dive into IQP updates:

SQL Server 2022 introduces a handful of new members to the growing Intelligent Query Processing (IQP) family. These additions range from expanding and improving various query performance feedback mechanisms to adding two new features—parameter sensitive plans (PSPs) and optimized plan forcing. In this blog, we give a general overview of these additions, with detailed feature-specific blogs to follow.

Read on to see what they have in store.

Comments closed

Pinging All Day in Powershell

Published 2022-09-16 by Kevin Feasel

Patrick Gruenauer wants to know if a service is up:

Recently, I have created a script that returns a beep sound when the connection is re-established. I would like to share this script here on my website.

Assume your network connectivity is broken. You wait until the connection is restored. In addition, you want to hear a sound when the connection is re-established. For this task, just use my script.

I’ve done some ping -t checks in my day, though never with a beep.

Comments closed

Deleting Rows in Order

Published 2022-09-16 by Kevin Feasel

David Fowler understand the order of things:

This was an interesting question that I was asked yesterday and something that I’d never really thought of before. Can you delete the top x number of rows based on an ORDER BY?

Why would you want to do that? Well let’s just assume that we have a ‘people’ table and some strange bug in the application has cause the need to delete the top 10 oldest females for whatever reason (I know, it’s very contrived example and not the situation that my colleague was facing but it’ll do to illustrate the point).

Click through for one way which doesn’t work and two ways which do.

Comments closed

Lack of Training on the Edge

Published 2022-09-15 by Kevin Feasel

Pete Warden explains a phenomenon:

One of the most frequent questions I get asked from people exploring machine learning beyond cloud and desktop machines is “What about training?”. If you look around at the popular frameworks and use cases of edge ML, most of them seem focused on inference. It isn’t obvious why this is the case though, so I decided to collect my notes in a post here, so I can have something to refer to when this comes up (and organize my own thoughts too!).

Pete’s reasons make sense. I think the last one is the most important.

Comments closed

Declaring a Minimum R Version in Packages

Published 2022-09-15 by Kevin Feasel

Hugo Gruson and Maelle Salmon show how to set a minimum version of R itself in a package:

There have been much talk and many blog posts about R package dependencies. Yet, one special dependency is more rarely mentioned, even though all packages include it: the dependency on R itself. The same way you can specify a dependency on a package, and optionally on a specific version, you can add a dependency to a minimum R version in the DESCRIPTION file of your package. In this post we shall explain why and how.

Read on for that explanation, as well as a lot of depth on why you might choose a particular R version, popular R versions (at least by number of packages), and what some of the largest maintainers do.

Comments closed

SQL Server and the Slow Registry

Published 2022-09-15 by Kevin Feasel

Michael J. Swart diagnoses issues when the Windows registry slows down operations:

I want to describe some symptoms that SQL Server may display when its Windows Registry is non-responsive or slow. From the symptoms, it’s hard to know that it’s a slow registry and so if a web search brought you here, hopefully this helps.

Read on for some of the various operations which request data from the registry, as well as thoughts from Michael on some of the effects of a slow registry. It sounds like there’s not a whole lot we can do about it and this is rare.

1 Comment

Date Calculation (and Calendar Tables) in SQL Server

Published 2022-09-15 by Kevin Feasel

Aaron Bertrand makes the case for calendar tables:

In a previous tip, Simplify Date Period Calculations in SQL Server, I described how to easily calculate certain dates, like the first day of a given month, quarter, or year. Another common scenario is trying to find the last weekday of the month, or the nth Monday or Wednesday. In this tip, I will show ways to simplify these calculations, both with and without a calendar table.

Click through for Aaron’s approach to the problem. I have a blunter approach in creating an expansive calendar table and using it. You do the heavy lifting one time and are good for life on that server.

Comments closed

Getting User Access Lists to All Power BI Reports

Published 2022-09-15 by Kevin Feasel

Tomaz Kastrun wants to know what you can see:

This way, you will have a better view of users, and their access to data and reports (if these are containing sensible data). You can always retrieve the list of workspaces and access the list of all users with the PowerShell cmdlet Get-PowerBIWorkspace.

I have also added the Join-Object module. It can join two objects or two arrays, based on the given matching columns.

Click through for a Powershell script which does the job.

Comments closed

Contained SQL Agent Jobs in SQL Server 2022

Published 2022-09-15 by Kevin Feasel

Allan Hirt looks at contained SQL Server Agent Jobs:

I previously wrote about Contained AGs in SQL Server 2022 and demonstrated how to create a contained login. In this blog post, I’m going to talk about contained SQL Server Agent jobs because just like logins, they are a bit confusing from an administative standpoint in their current pre-release implementation (this blog post was written using SQL Server 2022 RC0 using SSMS 19 Preview 3).

It sounds like there’s still a ways to go on the tooling side of things.

Comments closed

Creating Multiple Audiences in a Power BI App Workspace

Published 2022-09-15 by Kevin Feasel

Gilbert Quevauvilliers plays to the audience:

With the recent announcement (Announcing Public Preview of Multiple Audiences for Power BI Apps | Microsoft Power BI Blog | Microsoft Power BI) it is now possible to create multiple audiences in a single App Workspace.

What this means you can now have a single app workspace but create a view for specific users (each view is known as an Audience)

This blog post will detail how to manage multiple audiences with AAD Security Groups, where the only requirement will be to update the app when new reports get created and to which audience to make them available.

There’s also a big warning on Gilbert’s post that you will not want to miss.

Comments closed