Press "Enter" to skip to content

Day: June 8, 2022

Reviewing Power BI Field Parameters

Teo Lachev is pleased:

Coming back from a long vacation and I almost missed this new Power BI killer feature: Field Parameters! Not to be confused with Dynamic M Query Parameters that I ranted about here, field parameters solve a long-standing limitation of Power BI that prevents you to bind dynamically dimension members to a visual. Dynamic binding wasn’t issue with measures because they are dynamic and can evaluate runtime conditions, such as slicer selection. But dimensions were a different story. Once you have bound them to a category bucket in a visual, you couldn’t change them on the fly.

Read on for more information on a common scenario in which field parameters can be quite helpful.

Comments closed

Monitoring Open Connections in the Serverless SQL Pool

Liliam Leme has a pair of queries for us:

Consider a scenario where you are trying to monitor the connections from other applications to serverless SQL. I hit this need while trying to understand how many connections opened I had coming from an application. Had I hit some kind of limitation on serverless SQL or not. Spoiler: There is no limit for connections on Synapse serverless SQL  as you would find with a dedicated SQL pool (formerly SQL DW).

Alternatively, the limit to the number of serverless SQL pool connections is how much cash you have in your bank account…though given that it’s $5 per TB processed, if you’re writing good queries, that’s a lot of queries and connections.

Comments closed

Query Store Queries with Missing Index Requests

Erik Darling makes a query purchase at the Query Store:

I’ve said quite a bit about missing index request utility generally in SQL Server, even as recently as last week!

But then I got a user question about using Query Store to do something similar, so here goes.

If you need a pre-2019 way to do this with Query Store, Kendra Little has a blog post about that here.

For the 2019 version, check out Erik’s query and then sp_QuickieStore to make it easier.

Comments closed

Backups to S3 in SQL Server 2022

Anthony Nocentino tries out backup to S3 in SQL Server 2022:

In s3 object storage, a file is broken up into as many as 10,000 parts. In SQL Server, the each part’s size is based on the parameter MAXTRANSFERSIZE since this is the size of the write operation performed into the backup file. The default used for backups to s3 compatible storage is 10MB. So 10,000 * 10MB means the largest file size for a single file is about 100GB. And for many databases, that’s just not big enough. So what can you do…first you can use compression. That will get more of your data into a single file.

This right here is the pain. Anthony shows a few ways to extend this number but there’s still a hard cap on maximum backup size, one we don’t have on-premises.

Comments closed

Changing Default Powershell Behavior via Commands or Proxies

Jeff Hicks gives us a choice:

I’ve often told people that I spend my day in a PowerShell prompt. I run almost my entire day with PowerShell. I’ve shared many of the tools I use daily on Github. Today, I want to share another way I have PowerShell work the way I need it, with minimal effort. This specific task centers on files and folders.

As you might expect, I am constantly creating, editing, and managing files. I do all of this from a PowerShell prompt. I rarely use the start menu to find a program to launch. My challenge has always been finding the files and folders I’ve recently been using. Get-ChildItem is naturally the PowerShell tool of choice, but I’ve finally gotten around to making it work the way I need.

Not having done either of these before, I’m not sure which would be my preference, as I’d like to make sure it’s easy for me to remember later how I got to this non-standard state in case I need to replicate it elsewhere or if somebody else is at my keyboard. That’s one nice thing about the .bashrc file: it’s just there and well-known enough that people can look for changes there.

Comments closed