Press "Enter" to skip to content

Author: Kevin Feasel

Caching Strategies with Redis

Published 2021-10-27 by Kevin Feasel

Camilo Reyes shares performance data from four Redis caching strategies:

Redis is a cache database that stores documents in memory. The data store has a key-value pair lookup with O(1) time complexity. This makes the cache fast and convenient because it does not have to deal with complex execution plans to get data. The cache service can be trusted to find a cache entry with a value in almost no time.

When datasets in cache begin to grow, it can be surprising to realize that any latency is not a Redis issue. In this take, I will show you several strategies to cache Redis data structures then show what you can do to pick the best approach.

Read on for the contenders and how they do. ProtoBuf’s results on small datasets surprised me.

Comments closed

Connection Leaks with MARS

Published 2021-10-27 by Kevin Feasel

Josh Darnell warns that, if you go to MARS, a doctor will warn you that you have a schizoid embolism and it will be up to you to determine whether the doctor is lying or not:

I recently looked at a SQL Server instance that had a large number of MARS connections under a single “parent” connection. Most of these “child” connections had been idle for quite a while, but they were still hanging around.

Read the whole thing. Because I’ve used MARS so little, I’ll instead add a follow-up point to my Total Recall reference above. In the commentary track for the movie, director Paul Verhoeven notes that Douglas Quaid actually did die and that it really was just a fantasy concocted in his mind and he really did die at the end. You can tell because, instead of a fade to black like normal movies, he fades to white, indicating that this wasn’t a proper ending. But then again, considering the follow-on media which happened (and was slated to happen but didn’t make it to the finish line), I don’t think the studios would have let Verhoeven keep his unhappy ending.

Comments closed

Sorting a List with Powershell

Published 2021-10-27 by Kevin Feasel

Kenneth Fisher lines ’em up and knocks ’em down:

In my last post I grabbed a file list but I really need it sorted alphabetically. Now, I’ve been a DBA my entire career and one of the things that gets hammered into you is that unless you specifically sort a list, there is no guarantee that that list will be sorted. So how do I sort my list?

To learn how and see a few examples of it in action, check out Kenneth’s post.

Comments closed

Serverless SQL Pool CI/CD

Published 2021-10-27 by Kevin Feasel

Kevin Chant doesn’t have time for manual deployments:

I want to cover one way you can do CI/CD for Azure Synapse Analytics serverless SQL pools using Azure DevOps in this post. Because I know it is a popular topic.

It’s related to my post about how you can create a dacpac for an Azure Synapse Analytics dedicated SQL pool using Azure DevOps. Since they are both based in the same service.

Plus, a while ago I wrote about the increase in demand for Data Platform automation. So, I really wanted to do a post about how you can do CI/CD for Azure Synapse Analytics serverless SQL pools.

Read on to learn how.

Comments closed

Lessons Learned in Migrating to .NET 5 or 6

Published 2021-10-27 by Kevin Feasel

Patrick Smacchia has a few tips for migrating code from .NET Framework to .NET 5 or even 6:

In January 2020 I wrote the post Not planning now to migrate your .NET 4.8 legacy, is certainly a mistake. Hopefully we followed our own advice and have been migrated most of our non-UI code. This way latest NDepend version 2021.2 can now run analysis, reporting, power tools and API against .NET 5 on Windows, Linux and MacOS.

We learn a few things during this migration journey. Let me expose those in five points:

My most positive experiences with this have come in migrating projects with relatively few third party dependencies. The big problem there is that a fair percentage of older libraries never made the leap to Standard, so you may be stuck with a re-write (or just stuck in general) as a result.

Comments closed

Dynamic Parameter Code in Powershell

Published 2021-10-27 by Kevin Feasel

Jeffrey Hicks shows off some Powershell 7 functionality:

One of the topics we’ve discussed in the PowerShell Cmdlet Working Group is a request to make it easier to insert dynamic parameters. I am a bit torn on this. On one hand, I see the value in dynamic parameters. These are parameters that only exist if some condition is met, such as if the current location is in the Windows registry. The downside is that these parameters are difficult to discover and awkward to document. On top of that, the PowerShell code necessary to define a dynamic parameter is complicated and definitely not beginner-level. This is what I think the issue is really all about. So I decided to write my own tooling to make it easier to insert dynamic parameters.

Some of those examples go from “This looks reasonable” to “That’s a lot of code” pretty quickly. In fairness, though, this isn’t the type of thing you’ll write every day.

Comments closed

The Architecture of Apache Hive

Published 2021-10-26 by Kevin Feasel

The Hadoop in Real World team explains what the Apache Hive architecture looks like:

Metastore database

Metastore database is not part of HiveServer2 (and it is not shown in the picture). Every Hive installation needs to have an RDBMS like Derby (good for dev environments only), Oracle or MySQL.

Hive stores the metadata of the tables and database that is managed by Hive in the metastore database. Note that this database doesn’t hold the actual data. The data will reside in HDFS.

Click through for the full architecture.

Comments closed

The Joys and Pains of Filtered Indexes

Published 2021-10-26 by Kevin Feasel

Chad Callihan takes us through filtered indexes:

We would all be happy if tables in our databases only contained the data we need. In reality, we tend to have data that needs kept for legal purposes or “just in case” something happens. Are you querying a table where queries only care about a fraction of the data?

When optimizing a query that is written to return a specific set of data, you may improve performance by creating a filtered index. Let’s look at an example of a filtered index and a warning that you may come across when using it.

I have a distinct love-hate relationship with filtered indexes. They’re so useful but so much of that utility comes with significant strings attached.

Comments closed

Sixth Normal Form to Avoid NULLs

Published 2021-10-26 by Kevin Feasel

I have a response to an article:

I linked to this on Curated SQL, where I’d started to write out a response. After about four paragraphs of that, I decided that maybe it’d make sense to turn this into a full blog post rather than a mini-commentary, as I think it deserves the lengthier treatment. I’m going to assume that you’ve read Aaron’s post first, and it’s a well-done apologia in support of using NULLs pragmatically. I’ll start my response with a point of agreement, but then move to differences and alternatives before laying out where I see additional common ground between Aaron’s and my thoughts on the matter.

One explicit assumption in here is that you don’t really have a large number of nullable (or NULLable, as long-form blogging me wants to write) columns on a given table. 6NF-style tables for nullable attributes is a lot less tenable when you have 15 or 20 distinct nullable columns on a table, but at that point I have to ask, is your data model actually correct if you have that many missing attributes?

Comments closed

All about Synchronous Stats Updates

Published 2021-10-26 by Kevin Feasel

Paul Randal shares some thoughts about synchronous stats updates:

The SQL Server query optimizer makes use of statistics during query compilation to help determine the optimal query plan. By default, if the optimizer notices a statistic is out-of-date because of too many changes to a table, it will update the statistic immediately before query compilation can continue (only the statistics it needs, not all the statistics for the table).

Note that “too many” is non-specific because it varies by version and whether trace flag 2371 is enabled – see the AUTO_UPDATE_STATISTICS section of this page for details.

Read on to learn more, including the problems that synchronous stats updates can cause, what you can do to avoid them, and ways you can tell that synchronous stats updates are a problem in your environment.

Comments closed