Press "Enter" to skip to content

Author: Kevin Feasel

Date Ranges and Merge Interval

Published 2022-10-17 by Kevin Feasel

Daniel Hutmacher notes a performance killer:

In my last post, I found that DATEDIFF, DATEADD and the other date functions in SQL Server are not as datatype agnostic as the documentation would have you believe. Those functions would perform an implicit datatype conversion to either datetimeoffset or datetime (!), which would noticeably affect the CPU time of a query.

Well, today I was building a query on an indexed date range, and the execution plan contained a Merge Interval operator. Turns out, this operator brings a few unexpected surprises to your query performance. The good news is, it’s a relatively simple fix.

Click through for an example and some information on a fix. Hugo Kornelis also adds some good insights in the comments.

Comments closed

Code is for Humans

Published 2022-10-14 by Kevin Feasel

Shane O’Neill covers an important topic:

There are a myriad number of uses for Dynamic SQL – I’ve already read some of the published posts and I’m impressed with the range. (Yeah, I’m writing this late; I’m aware).

I’m aiming for something different. I want to talk about the things I believe Dynamic SQL should have. You can disagree with me if you’d like; I’d welcome it (as long as you can justify it) but here are my thoughts on writing Dynamic SQL.

Improving the readability of code is probably the most important important things we could do at the margin. There are certainly trade-offs here: some patterns of code can be significantly more efficient at the cost of a minor readability loss and, if you need that to reach some expected performance threshold, fine. But ceteris paribus, the more human-readable code is the better code.

Comments closed

Monitoring Azure SQL Backup History

Published 2022-10-14 by Kevin Feasel

Mustafa Ashour wants you to check your backups:

Database backups are an essential part of any business continuity and disaster recovery strategy, because they help protect your data from corruption or deletion. These backups enable database restore to a point in time within the configured retention period. By default, Azure SQL Database & Azure SQL Managed Instance stores data in geo-redundant storage blobs that are replicated to a paired region. Geo-redundancy helps protect against outages that affect backup storage in the primary region. It also allows you to restore your databases/instance to a different region in the event of a regional outage/disaster.

Read on to learn more about how Azure SQL DB and Azure SQL Managed Instance perform backups, their cadence, and how you can find information on backup history.

Comments closed

Another Problem with Nullable Columns

Published 2022-10-14 by Kevin Feasel

Erik Darling tells the unpleasant truth about NULL:

Table definitions have a similar effect on developers. In today’s post, I’m going to use temp tables as an example, but the same thing can happen with regular tables, too.

The issue isn’t with NULL values themselves, of course. The table definition  we’re going to use will allow NULLs, but no NULLs will be present in the data.

The issue is with how you query NULLable columns, even when no NULLs are present.

As a card-carrying member of the League of No-Null Workers, I am happy to tell you all about how much I hate NULL in databases. And this isn’t even the big reason.

Comments closed

Dataset Changes while Deploying in Power BI

Published 2022-10-14 by Kevin Feasel

Marc Lelijveld investigates a what-if scenario:

One of the topics discussed during the session, is the effect of deployments on datasets by using native deployment pipelines in the Power BI service. Deployment Pipelines only deploy meta data from the data model, however specific changes might have an unwanted effect on the data in the dataset in the target stage.

In this blog post, I will further elaborate on several specific use cases and the effect on your dataset in the target stage.

Read on for the results of three separate tests.

Comments closed

Specifying Multiple Indexes in a Table Hint

Published 2022-10-14 by Kevin Feasel

Michael J. Swart is not satisfied with just one index:

My team wondered if this could be used as to help with a concurrency problem. We recently considered using it to resolve a particular deadlock but we had little success.

It’s useful to think that SQL Server takes locks on index rows instead of table rows. And so the idea we had was that perhaps taking key locks on multiple indexes can help control the order that locks are taken. But after some effort, it didn’t work at avoiding deadlocks. For me, I’ve had better luck using the simpler sp_getapplock.

I now would be curious what the maximum number of such index hints would be so we could apply Swart’s 10% Rule. Though I suppose, no matter the number, this would be a degenerate case of the rule, always leading you down to “one or zero, and stop messing with my database!”

Comments closed

Finding DAX Dependencies

Published 2022-10-14 by Kevin Feasel

Chris Webb tries out a DMV:

If you’re monitoring activity in Power BI, for example using Log Analytics, you’ll know that you can capture the DAX queries generated by your published Power BI reports. How do you make sense of them though? You may want to know which tables, columns or measures are being used by a query – maybe so you can work out which ones aren’t being used and can be deleted. I always thought the only way to do this would be to parse the DAX query, which would be incredibly difficult to do. Yesterday, though, Igor Cotruta pointed out on Twitter that there’s a really easy way to do this that I didn’t know about, using the DISCOVER_CALC_DEPENDENCY DMV. I’ve used this DMV before and blogged about it here, but what was new to me is that you can pass a DAX query into the QUERY restriction (it is all documented here). To see how it works let’s look at a simple example.

This looks pretty cool.

Comments closed

Training a Language Transformer Model

Published 2022-10-13 by Kevin Feasel

Stefania Cristina continues a series on building a language transformer:

We have put together the complete Transformer model, and now we are ready to train it for neural machine translation. We shall be making use of a training dataset for this purpose, which contains short English and German sentence pairs. We will also be revisiting the role of masking in computing the accuracy and loss metrics during the training process. 

In this tutorial, you will discover how to train the Transformer model for neural machine translation. 

Read on for the process, including a lot of code.

Comments closed

Azure Stream Analytics Job Diagram Simulator

Published 2022-10-13 by Kevin Feasel

Alex Lin points out some functionality to understand Azure Stream Analytics jobs:

Azure Stream Analytics (ASA) uses a SQL query language that has been augmented with powerful temporal operators to analyze data streams. One way to optimize a Stream Analytics job’s performance is to leverage parallelism in query. A parallel job divides the workload of queries into multiple streaming nodes and reduces the overall execution time.  

We strongly recommend that you use the Azure Stream Analytics Tools extension in Visual Studio Code (VS Code) for testing and debugging queries locally. For a job to be parallel, you need to align partition keys between all inputs, query steps, and outputs. The Job Diagram Simulator in VS Code allows you to simulate a job running topology with different streaming units (SUs) and provides suggestions for improving your query parallelism. 

Click through to see how it works and the kinds of information you can get from it.

Comments closed

Updates with Nested REPLACE Functions

Published 2022-10-13 by Kevin Feasel

Chad Callihan neeeds to make an update:

Have you ever ran into a situation where you needed to replace both ends of a string? Maybe it was tags in a set of XML values or phrases at the beginning and end. I came across that situation recently and was able to show if two statements were needed or if REPLACE could be used against the same column in the same statement. Let’s look at a quick example and see what’s possible.

Chad’s solution is one I use fairly frequently. I agree that it’s not pretty, though one thing I like to do is tokenize code, so I might have something that looks like:

-- Procedure input parameters here
@FirstName NVARCHAR(150),
@FavoriteColor NVARCHAR(30)

-- Guts of procedure here
DECLARE @msg NVARCHAR(4000) = N'Hello, {FIRST_NAME}!  Your favorite color is {FAVORITE_COLOR}!';

SELECT
    REPLACE(REPLACE(@msg,
        '{FIRST_NAME}', @FirstName),
        '{FAVORITE_COLOR}, @FavoriteColor);

It’s definitely not the prettiest but when you have several tokens to replace, it’s a lot easier to read than building the string all at once.

Comments closed