Press "Enter" to skip to content

Curated SQL Posts

Logical Reads and Query Tuning

Published 2024-04-25 by Kevin Feasel

Erik Darling doesn’t focus on logical reads:

To summarize the video a little bit:

  • High average or total logical reads isn’t a guarantee that a query is slow
  • Looking for high average CPU and duration queries is a better metric
  • You may see logical reads go up or down as you make queries faster
  • For I/O bound workloads, you’re better off looking for queries with a lot of physical reads

I agree with Erik. Disk has gotten so much faster, especially if you’re on all-flash arrays or (even better) direct attached nVME storage. Even relatively old SSDs are still a couple orders of magnitude faster than the spinning rust we typically dealt with 15 years ago.

Sometimes, the faster query is one that requires more logical reads. APPLY-based queries typically fall into that category: I might have 10x as many logical reads but the query takes half the time (or less) to finish using a similar percentage reduction of CPU time.

Comments closed

Thoughts on Multi-Platform Visual Studio

Published 2024-04-24 by Kevin Feasel

The NDepend Blog has an interesting post on Visual Studio:

Notice that there are two distinct questions:

  • Will Visual Studio Be Migrated to .NET Core? (but still remain Windows-centric).
  • Will Visual Studio Be Multi-Platform?

Disclaimer: For nearly two decades, I’ve been the creator and lead developer of NDepend, a tool that functions both as a Visual Studio extension and a standalone app that can collaborate with VSCode and JetBrains Rider. NDepend also integrates into CI/CD pipelines to deliver interactive web reports on .NET code quality and security. We’ve collaborated with many Visual Studio engineers over the years, who have provided invaluable assistance, though we have no insider information. This post is based purely on observable facts and speculations derived from them.

Read on for those thoughts. My money’s on “No” for the latter, as that’s the purview of Visual Studio Code, and just thinking about the sheer amount of work necessary to make Visual Studio itself cross-platform, I can’t see it happening.

Comments closed

Three Layers of Azure Data Factory Framework Components

Published 2024-04-24 by Kevin Feasel

Martin Schoombee continues a series on orchestration in Azure Data Factory:

Before we dive into the details of the Data Factory pipelines, it is worth explaining the conceptual structure of my framework and its components. How it all fits together is important, and after reading the post on the metadata as well the pieces of the puzzle will hopefully start falling into place.

When I started thinking about what I’d like the framework to do, three conceptual layers started to emerge and we’ll review them from the bottom up:

Click through for the description of each layer.

Comments closed

Issues around Power Apps in Source Control

Published 2024-04-24 by Kevin Feasel

Deborah Melkin continues a series on Power Apps:

As a developer, I have two things I have to do: 1. Check code into source control and 2. Make sure I can use that code to deploy to any and all environments repeatably and successfully. The question then becomes, how do you do this when the development environment is in a portal?

Read on for the answer, as well as a tricky situation you might run into along the way.

Comments closed

Query Store Size-Based Cleanup Performance Issues

Published 2024-04-24 by Kevin Feasel

Kendra Little has a public service announcement:

I’m a huge fan of SQL Server’s Query Store feature. Query Store collects query execution plans and aggregate query performance metrics, including wait stats. Having Query Store enabled makes troubleshooting performance issues such as bad parameter sniffing, much, much easier. Because Query Store is integrated into SQL Server itself, it also can catch query plans in a lightweight way that an external monitoring system will often miss.

When performance matters, it’s important to ensure that you’re managing Query Store so that Query Store cleanup does not run during high volume times. Query Store cleanup could slow your workload down significantly.

Read on for more information. I’d also like to plug qdstoolbox, an open-source solution some of my former colleagues worked on. This includes QDSCacheCleanup, which works considerably better than the built-in cleanup process.

Comments closed

Checking SSIS Package Performance

Published 2024-04-24 by Kevin Feasel

Andy Brownsword digs into SSISDB:

I’ve recently been reviewing SSIS packages to make some performance fixes and needed a way to validate the results of those changes. I thought I’d share the scripts as they may be useful for others.

Rather than relying on run times from the SQL Agent running the packages I wanted to dive deeper into the packages so took another approach.

Read on for a query that gets the information. You can also eyeball it in the SSIS reports if you have a small or consistent set of packages to run.

Comments closed

Comparing Cumulative Values for Events across Different Periods

Published 2024-04-23 by Kevin Feasel

Kurt Buhler performs comparative analytics:

In a previous article about format strings, we showed an example of how format strings can improve visualizations. The visualizations in that article compared the performance of a company’s marketing videos on a streaming platform to the average of all their videos released that year. In this article, we explain how to conduct this analysis yourself in DAX, where you compare series that occur in different periods.

Click through for an explanation, both in video and in blog form.

Comments closed