Press "Enter" to skip to content

Month: March 2024

Recovering Deleted Oracle Connections in Visual Studio Code

Published 2024-03-06 by Kevin Feasel

Brendan Tierney goes on a search and rescue mission:

With the current early release, there is no way to organise your Database connections like you can in the full Oracle SQL Developer. We are told this will/might be possible in a future release but it might be later this year (or longer) before that feature will be available.

In a previous post, I showed how to import your connections from the full SQL Developer into SQL Dev VS Code. While this is a bit of a fudge, yet relatively straight forward to do, you may or may not want all those connections in your SQL Dev VS Code environment. Typically, you will use different tools, such as SQLcl, SQL*Plus, SQL Developer, etc to perform different tasks, and will only want those connections set up in one of those tools.

Click through to see what it takes to recover these.

Comments closed

Comparing TOP(1) + ORDER BY vs MAX() Performance in SQL Server

Published 2024-03-06 by Kevin Feasel

Andy Brownsword breaks out the stopwatch:

The TOP clause limits the number of results which are returned from a query, in this instance we’re focussing on a single result. In contrast, when using MAX we’re applying a function to our data to select the largest value from our data.

Let’s dive into some examples with the StackOverflow data, specifically the Votes table.

Read on for several scenarios and how the two perform. Things get a bit more complicated as you introduce other tables in joins and similar additional factors, but this gives you a good foundation for comparison.

Comments closed

Microsoft Fabric Governance & Administration: Tenant Settings

Published 2024-03-06 by Kevin Feasel

Nicky van Vroenhoven has a pair of posts on Microsoft Fabric administration, specifically around tenant settings. First up is a post on APIs:

Obviously, to use the Get Tenant Settings API you need to have at least Tenant.Read.All permissions, or have the Fabric Administrator role (or higher) in Azure.

There are a few use cases I see for getting these settings exported with this API:

  • Documentation purposes when you have multiple Fabric Administrators
  • Distributing tenant settings to users, and explaining why we (as a team of Administrators/within the Center of Excellence) made certain choices
  • Get notified of the changes in the tenant settings, without having to use Microsoft Defender or M365 Security & Compliance center like mentioned here

Nicky has a follow-up post on visual cues in the Tenant Settings page:

Today I want to talk about a new little addition Microsoft made to the Fabric Admin portal.

This change has actually been here for quite a while now, but I still think it’s worth mentioning because (1) I really like it, and (2) it’s also an important change that the community, and MVP’s in specific, has been requesting for quite some time.

Radhakrishnan Srinivasan and (members of) his team added visual cues to the Admin portal of Fabric.

Check out both posts for good information.

Comments closed

Query Compilation Timeouts and Query Store

Published 2024-03-06 by Kevin Feasel

Kendra Little diagnoses a problem:

Last November, a puzzle was really bothering me. Some queries from an application were timing out frequently after running for 30 seconds, but they were halfway invisible in the SQL Server.

I say “halfway invisible” because I could see the queries while they were running in SQL Server’s dynamic management views using free tools (sp_WhoIsActive and sp_BlitzWho).

But the queries had some odd characteristics:

Through the power of communication with other humans (eew, that idea sounds icky), Kendra was able to learn what the problem was and how you can track such issues outside of Query Store.

Comments closed

Improving Data Labels with Format Strings

Published 2024-03-06 by Kevin Feasel

Kurt Buhler has some new digs:

Format strings greatly improve the usefulness of your model, particularly when you apply custom and dynamic format strings creatively. However, there are considerations to keep in mind.

It is important that the values in a report display for users as they expect. Effective formatting of DAX measures and table columns improves your model, as values are easier to read and interpret. While formatting may seem simple at first glance, the flexibility of DAX and format strings in Power BI can create many opportunities for more creative and efficient tables or visuals.

Click through to understand why proper formatting of measures is so important on dashboards, and also congratulate Kurt on becoming an Italian. At least, I assume citizenship conveys when you do work for Marco & Alberto.

Comments closed

An Overview of Data Partitioning Strategies

Published 2024-03-05 by Kevin Feasel

thanhdoancong (there are spaces in there somewhere but I’d probably guess wrong) talks partitions:

Data partitioning is the magic wand that divides your massive dataset into smaller, organized subsets called partitions. These partitions are based on specific criteria, like date ranges, customer segments, or product categories.

It’s like organizing your overflowing closet by color, season, or type of clothing. Each section becomes easier to browse and manage, making life (and data analysis) much easier.

Read on for a few varieties of partitioning and how they could improve your data estate. There’s no guarantee that partitioning will definitely improve performance—and in SQL Server’s case, the partitioning feature often does not improve performance at all because that isn’t its intent—but this is a good read to get an idea of what strategies are available.

Comments closed

Implicit Conversion Works in Both Directions

Published 2024-03-05 by Kevin Feasel

Deborah Melkin shares a lesson:

I’ve been working on a project where I have to do a lot of interesting data manipulation. Unfortunately, dealing with implicit conversions has been part of the norm. So naturally, I managed to run across an interesting scenario that had me stumped, thinking that I ran into a strange edge case.

The tl;dr version is that this wasn’t an edge case or some undocumented issue or a bug or anything other than the db engine doing its job. I was looking at one scenario and missed the rest of the clues. But it’s worth sharing how these things are easily missed.

Click through for the full story.

Comments closed

SSMS 20 and Default Security

Published 2024-03-05 by Kevin Feasel

Brent Ozar notes a change:

SQL Server Management Studio 20 Preview 1 is out, and the new connection dialog has a big change:

When you click Connect, you’re likely going to get an error:

Read on for the quick-and-easy solution, which brings behavior back to the pre-SSMS 20 default, as well as the long-term solution to prevent it from being an issue at all.

This brings SSMS in line with Azure Data Studio, which has defaulted to requiring certificates for quite some time. Note that you will need to select “Trust server certificate” if you are using a self-signed cert, though self-signed certs remove one of the two benefits of using certificates in the first place. The first is that certificates allow for encrypting the Tabular Data Stream (TDS) packets SQL Server sends over the network. Self-signed certs do just as good a job of that task as certificates you get from a trusted authority.

The second use case of certificates, however, is ensuring that this is definitely the machine and service you intend to connect to. If an attacker takes over the machine and swaps out the certificate with their own, your client should panic a bit because that’s your early-warning indicator that something is wrong.

Comments closed

Measuring Query Times in Power BI DirectQuery Mode

Published 2024-03-05 by Kevin Feasel

Chris Webb breaks out the stopwatch:

If you’re tuning a DirectQuery semantic model in Power BI one of the most important things you need to measure is the total amount of time spent querying your data source(s). Now that the queries Power BI generates to get data from your source can be run in parallel it means you can’t just sum up the durations of the individual queries sent to get the end-to-end duration. The good news is that there are new traces event available in Log Analytics (though not in Profiler at the time of writing) which solves this problem.

Read on to learn more about this event.

Comments closed