Press "Enter" to skip to content

Curated SQL Posts

Copying Azure SQL Managed Instance Databases

Published 2024-04-25 by Kevin Feasel

Scott Klein performs a migration:

So, back to our customer. They essentially lifted and shifted their on-premises databases to Azure SQL Managed Instance and have been using it successfully for nearly two years. Again, this is awesome.

Last week they came to us and asked about reporting with Managed Instance. They were looking at data marts and data warehouses, but we needed more information. It turns out they have some people that just want the ability to query the databases, and potentially hook up Excel to these databases for data analysis.

The caveat is that the people I was talking to didn’t want to give the other group direct access to the production environment. Toootally get that. Yeah, like 100% get it. So, what are the options?

Read on for the solution Scott came up with.

Leave a Comment

Troubleshooting sp_getapplock Blocking

Published 2024-04-25 by Kevin Feasel

Brent Ozar has an epiphany:

I’ll give you an example. A client came to me because they were struggling with sporadic performance problems in Azure SQL DB, and nothing seemed to make sense:

  • sp_BlitzFirst @SinceStartup = 1 showed very clearly that their top wait, by a long shot, was blocking. Hundreds of hours of it in a week.
  • sp_BlitzIndex showed the “Aggressive Indexes” warning on a single table, but… only tens of minutes of locking, nowhere near the level the database was seeing overall.
  • sp_BlitzCache @SortOrder = ‘duration’ showed a couple queries with the “Long Running, Low CPU” warning, and they did indeed have blocking involved, but … pretty minor stuff. Plus, their plan cache was nearly useless due to a ton of unparameterized queries pouring through constantly, overwhelming Azure SQL DB’s limited plan cache.
  • sp_Blitz wasn’t reporting any deadlocks, either. (sp_BlitzLock doesn’t work in Azure SQL DB at the moment because Microsoft’s no longer running the default system health XE session up there. They turned that off in order to save money on hosting costs, and passed the savings on to… wait… hmm)
  • As a last-ditch hail-Mary, I ran sp_BlitzWho repeatedly, trying to catch the blocking happening in action. No dice – the odds that I’d catch it live weren’t great anyway.

Click through for the story and how all the pieces ultimately fit together.

Leave a Comment

Exporting and Sharing Power BI Reports in Fabric

Published 2024-04-25 by Kevin Feasel

Sandeep Pawar distributes PDFs like candy:

With the proposed solution below, you will be able to :

  • Export a Power BI report, or a page of a report or a specific visual from any page as a PDF, PNG, PPTX or other supported file formats
  • Apply report level filters before exporting
  • Automate the extracts on a schedule
  • Save the exported reports to specific folders
  • Grant access to individual folders in the Lakehouse

Click through for the solution.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment