Press "Enter" to skip to content

Curated SQL Posts

Finding Outdated Powershell Modules

Published 2022-09-30 by Kevin Feasel

Jeff Hill has a script for us:

In the world of PowerShell, some things move super fast while others may stay the same for years. If the module you are using does what you want with no issues, you may not think to see if a newer version is available. The PSReadline module is a good example. The version I was on worked great and it did what I wanted. I had no idea there was so much more that had been done.

Click through for a script which checks the Powershell Gallery for updates, though it does not force install those updates.

Comments closed

SQL Server Backup and Restore Operations for S3

Published 2022-09-30 by Kevin Feasel

Hugo Queiroz shows off something new in SQL Server 2022:

Backup and restore to simple storage service (S3)–compatible object storage is a new feature introduced in SQL Server 2022 that grants the user the capability to back up or restore their databases using S3-compatible object storage, whether that be on-premises, or in the cloud.

There are some differences from other backup operations, so you should definitely read up on it before using it. One interesting side benefit I got to try out recently is that Pure Storage’s FlashBlade product has an S3 API, allowing you to use that interface for backup/restore operations as well as data virtualization.

Comments closed

Tracking Bulk Insert Usage with Extended Events

Published 2022-09-30 by Kevin Feasel

Grant Fritchey wants to see if you’re bulking up:

Wouldn’t it be great to be able to directly monitor specific behaviors within SQL Server, like, oh, I don’t know, knowing exactly when, and how, someone is using BULK INSERT? Well, you can, thanks to Extended Events through the bulk_insert_usage event.

Click through for an overview of what this event provides, as well as what it doesn’t provide.

Comments closed

High Memory Grants in SQL Server

Published 2022-09-30 by Kevin Feasel

Ajay Dwivedi tracks down memory grants:

Very often, specially on mixed workload enviroment where server deals with oltp & olap queries together, concurrent sessions allocated with High Memory Grants cause entire SQL Server workload to suffer.

To detect such a situation, I like to watch my critical metrics dashboards. For example, dashboard Monitoring – Live -All Servers of free SQLMonitor tool highlights all these metrics.

Click through for an example of the dashboard as well as a diagnostic query which helps find high-memory grant queries.

Comments closed

Using the Confluent Terraform Provider

Published 2022-09-29 by Kevin Feasel

Spencer Shumway has a tutorial:

As part of our recent Q3 Launch for Confluent Cloud we announced the general availability of the Confluent Terraform Provider. HashiCorp Terraform is an Infrastructure as Code tool that lets you define both cloud and on-prem resources in human-readable configuration files that you can version, reuse, and share, using standardized resource management tooling, pipelines, and processes.

Click through for a getting started video as well as the tutorial.

Comments closed

Azure Synapse Analytics September 2022 Update

Published 2022-09-29 by Kevin Feasel

Ryan Majidimehr has an update for us:

Serverless SQL pool relies on statistics to generate an optimal query execution plan and resource allocation. Previously, you had to manually create statistics for your CSV datasets when querying with OPENROWSET to get optimal performance due to the lack of CSV auto-statistics feature. With this improvement, serverless SQL pool will automatically create statistics for CSV datasets when needed to ensure an optimal query execution plan is generated. This feature is currently enabled for OPENROWSET only. 

How much performance improvement CSV statistics will bring depends on query complexity and the amount of data processed.

Click through for the full list of updates.

Comments closed

Non-Updating Updates

Published 2022-09-29 by Kevin Feasel

Michael J. Swart has an update for us:

According to Microsoft’s documentation, an UPDATE statement “changes existing data in a table or view”. But what if the values don’t actually change? What if affected rows are “updated” with the original values? Some call these updates non-updating. This leads to a philosophical question: “If an UPDATE statement doesn’t change any column to a different value, has the row been updated?”

I answer yes to that question. I consider all affected rows as “updated” regardless of whether the values are different. I think of the UPDATE statement as more of an OVERWRITE statement. I also think of “affected rows” rather than “changed rows”. In most cases SQL Server thinks along the same lines.

I list some features and areas of SQL Server and whether non-updating updates are treated the same or differently than other updates:

Click through for multiple scenarios and to see whether SQL Server is smart enough to ignore non-updating records in an UPDATE statement.

Comments closed

Using DaxDebugOutput when testing EvaluateAndLog()

Published 2022-09-29 by Kevin Feasel

Gilbert Quevauvilliers hooks us up:

I have seen a few great blog posts with regards to the new DAX function EvaluateAndLog which can be used to show/debug what happens with DAX Measures.

When I tried this out myself one of the challenges I had was where to download DaxDebugOutput, and then how to use it with Power BI Desktop.

In this blog post I will show you how I downloaded, installed, and used DaxDebugOutput application with Power BI Desktop.

Read on to see how the tool works, as well as where you can get it.

Comments closed

The Power of Bookmarks in Power BI

Published 2022-09-29 by Kevin Feasel

Mara Pereira likes bookmarks:

Yes, I know some of you are not a fan of this incredible feature, but hopefully I can change your mind with this blog post.

I feel that for you to like bookmarks, you really need to know all the ins and outs of it, otherwise it can be quite overwhelming, specially if you have to create loads of bookmarks in the same report.

Read on for more information about how you can take best advantage of bookmarks in Power BI. My main issue with them is that it’s difficult to keep bookmarks up to date, especially as you get more complicated combinations of actions (like hiding and displaying certain sets of visuals). But that is for the next post, apparently.

Comments closed