Press "Enter" to skip to content

Curated SQL Posts

Working with SIDs in Powershell

Jeffery Hicks translates security identifiers:

As usually happens during my day, I get sidetracked to another issue, and before you know it, I have a new PowerShell tool. In this instance, I was looking at event logs using Get-WinEvent. One of the event record properties is a UserID.

That’s very nice, but who is this? In this particular instance, the UserID property is SecurityIdentifier object.

Read on to see how to translate a SecurityIdentifier into something human-understandable.

Comments closed

Returning Blank instead of Zero in Power BI

Marco Russo does a bit of transformation:

In matrix visuals, Power BI usually hides rows where all the measures return a blank value. To leverage this behavior or simply to change the visualization of a measure depending on its result, you might want to achieve one of the following:

– Transforming a blank result to zero: this is covered in the article, How to return 0 instead of BLANK in DAX.

– Transforming a zero result to blank: this is the scenario described in this article.

Click through to see what the process looks like.

Comments closed

First Operator Execution Plan Properties

Grant Fritchey implores us not to forget the SELECT/INSERT/UPDATE/whatever operator:

I’ve watched several people recently go straight to XML when reading execution plans because they didn’t know about the execution plan properties in the first operator. Now, don’t get me wrong. If going straight to the XML is working for you, that’s fine. Keep doing it. I’m absolutely not questioning how anyone does things. I just want people to know that “hidden” information isn’t so much hidden as much as it’s not too obvious.

Read on to see what this contains.

Comments closed

WEEKNUM() in Power Query

Imke Feldmann translates another Excel function:

If you are looking for the Excel equivalent of the WEEKNUM function for Power Query this article is for you.

As this is also a function that has many regional options, I was lucky to find an algorithm that I could use for its main part here: M functions to convert between ISO 8601 Week & Year ⇄ dates (e.g., 2014-12-29 ⇄ "2015-W01-1") (github.com)

Read on for the code and details on function parameters.

Comments closed

Azure SQL News

Anna Hoffman brings us tidings:

Emily Lisa kicked it off by telling us about the latest Azure SQL Database product updates including Azure SQL maintenance windowszone redundancy for SQL Hyperscale, and configurable backup storage redundancy for SQL Hyperscale.

Dani Ljepava from the Azure SQL Managed Instance product team came on to share a range of new features announced at SQL Bits now available in public preview: Link feature for Managed Instancedata virtualization with ADLSAdvanced notifications for planned eventsHybrid Service BrokerWindows authentication for Azure AD Principals, and the general availability of Maintenance windows. Watch the episode to see a special look at Link feature for Managed Instance!

Pam Lahoud also came on to talk about about the latest improvements that help you manage your SQL Server in Azure Virtual Machines, including the newly released SQL best practices assessment, all available to you by installing the free SQL IaaS Agent extension!

Read on for plenty more links and updates.

Comments closed

Azure Functions and Azure SQL Database

Rajendra Gupta builds a simple Azure Function:

As a Platform as a Service (PaaS) service, Azure SQL Database enables developers to deploy SQL Database in Azure Cloud without managing the infrastructure. We use SQL Server Agent to schedule jobs to run at a specific schedule in an on-prem SQL instance. However, Azure DB does not have agent functionality.

There are multiple ways to schedule job or batch processes in the Cloud. You can explore the Azure automation series for executing scripts using Azure Logic apps and automation runbooks.

This article focuses on the Azure functions for scheduling a job for Azure SQL Database.

Read on for the process.

Comments closed

Finding Query Timeouts with Query Store

Kendra Little searches for timeouts:

Here are some basics regarding query timeouts for SQL Server, Azure SQL Database, and Azure SQL Managed Instance:

– Execution timeouts are NOT set or enforced by the database engine. It is happy to wait for your query to run or for your transaction to be open for hours, days, or years, until you commit, cancel, or get disconnected.

– Timeouts are set by the calling application. If you don’t explicitly set a timeout on your connection string, there is generally a default timeout that is used. Often, this is 30 seconds for query execution timeouts.

– Connection timeouts are different than execution timeouts. If you see a connection timeout, often this is a firewall or network issue preventing you from connecting to the database – or perhaps it’s even offline. Execution timeouts are when the calling application doesn’t want to wait any more for a query to complete.

Kendra shows how to generate an execution timeout and how you can use Query Store to find them. And Kendra’s post got Mark Freeman to write a blog post so that’s also worth something.

Comments closed

When Compute Scalars Attack

Erik Darling goes scalar hunting:

This is awful. It really is. It’s so awful. These operators skated by undetected for years. Acting so innocent with their 0% cost.

Subprime operators, or something.

In this post, I’m going to show you how compute scalars hide work, and how interpreting them in actual execution plans can even be tricky.

I’m now imagining Erik as the Steve Irwin of the query tuning world. And it’s kind of working for me.

Comments closed

Finding Zero-Record Tables Taking Space in SQL Server

Jeff Iannucci has a follow-up for us:

This is a follow up post to the previous one about tables with no records using data space, with the idea coming from twitter of all places. Sometimes on twitter the conversations kill, but after reading that post my friend Chris Wood suggested writing something to FIND those tables.

Which is a solid idea, so let’s get to it. No time to wait too long.

Click through for the process.

Comments closed