Press "Enter" to skip to content

Author: Kevin Feasel

Finding SQL Agent Jobs with Invalid Job Owners

Published 2022-03-22 by Kevin Feasel

Chad Callihan is trying to clean up this town:

Do you know which account is the owner for your SQL Server Agent jobs? Some jobs might be owned by user accounts which can become a problem. What happens if that job owner isn’t around forever and goes away? Will that job just keep chugging along?

Without an existing owner, a SQL Server Agent job will not run. Once a user gets disabled or removed from Active Directory, a job is still going to try running under that user but will begin failing.

Click through to see what kind of errors you might find.

Comments closed

An Overview of Azure Redis Cache

Published 2022-03-22 by Kevin Feasel

Arun Sirpal lays out the use case of Azure Redis Cache:

Redis Cache is a well know caching technology and you can run it in Azure as a fully managed service. A common requirement (the most basic one) is doing a workflow like:

1. When an application needs to retrieve data, it will first search to see if it exists in Azure Cache for Redis.

2. If the data is found in Azure Cache for Redis (cache hit) use it

3. If the data is not found in Azure Cache for Redis (cache miss), then the application will need to retrieve the data from Azure SQL (or whatever cloud db back end you use)

4. For cache miss scenarios, the requesting application should add the data retrieved from the Azure Database to Azure Cache for Redis.

This is also known as the cache-aside pattern. If you’re feeling really cheeky, you can combine cache-aside with the decorator pattern to “hide” the cache in your code.

Comments closed

Running SQL Scripts on Snowflake from Azure Data Factory

Published 2022-03-22 by Kevin Feasel

Koen Verbeeck shows off the Script activity in Azure Data Factory:

Azure Data Factory has a new activity introduced this week (around the 10th of March 2022 for you future readers): the Script activity! This is not to be confused with the script task/component of SSIS, which allows you to execute .NET script (C# for most people, or VB if you’re Ben Weissman). No, this task executes SQL, so it’s more akin to the Execute SQL Task of SSIS.

Click through to see how it works while I lament the fact that SSIS never supported the best .NET language.

Comments closed

An Overview of Simple Parameterization

Published 2022-03-22 by Kevin Feasel

Paul White begins a new series:

This is the first part of a series about simple parameterization and trivial plans. These two compilation features are closely connected and have similar goals. Both target performance and efficiency for workloads frequently submitting simple statements.

Despite the “simple” and “trivial” names, both have subtle behaviours and implementation details that can make how they work difficult to understand. This series doesn’t dwell too long on the basics but concentrates on less well-known aspects likely to trip up even the most experienced database professionals.

In this first part, after a quick introduction, I look at the effects of simple parameterization on the plan cache.

Read on for more detail.

Comments closed

Linear Data Structures

Published 2022-03-21 by Kevin Feasel

Dave Saunders describes the concept of linear data structures:

As we said in the introduction, a data structure is ‘linear’ if the elements form a sequence.

That means that the data structure has a first and last element, and each element is connected to its previous and next element.

– An ‘array’ is a linear data structure; the items are stores sequentially.

– A ‘graph’ is not a linear data structure; any node can be linked to any other node in the graph – there is no fixed ‘sequence’.

Click through for several examples of linear data structures.

Comments closed

The Upsert Pattern and Unique Indexes in SQL Server

Published 2022-03-21 by Kevin Feasel

Michael J Swart recommends a unique index:

To avoid deadlocks when implementing the upsert pattern, make sure the index on the key column is unique. It’s not sufficient that all the values in that particular column happen to be unique. The index must be defined to be unique, otherwise concurrent queries can still produce deadlocks.

Read on for Michael’s thoughts as well as some smart commenters adding on.

Comments closed

Setting Powershell’s TLS Version

Published 2022-03-21 by Kevin Feasel

Frank Gill updates TLS:

I was recently working on a client server and need to install the Az PowerShell module. I opened and administrator window and typed

Install-Module Az

and received this error:

WARNING: Unable to find module repositories.

Turns out that was not the real answer. Click through to see what Frank ended up needing to do.

Comments closed

Tuning Non-Clustered Indexes via Missing Index Suggestions

Published 2022-03-21 by Kevin Feasel

Kendra Little announces a new article:

We’ve just published a new article in the SQL docs, Tune nonclustered indexes with missing index suggestions . The article explains what the missing index feature is, limitations of the feature, and how to use missing index DMVs and missing index suggestions in Query Store to tune indexes.

Read on to understand the impetus behind this article and then go check it out yourself.

Comments closed