Press "Enter" to skip to content

Curated SQL Posts

More MSDB Cleaning

Published 2021-03-23 by Kevin Feasel

Just when you thought you were done with cleaning MSDB, Eitan Blumin brings you back in for more:

As part of its regular, ongoing, day-to-day activities, your SQL Server instance would naturally collect historical data about its automated operations. If left unchecked, this historical data could pile up, leading to wasted storage space, performance hits, and even worse issues.

MSDB would obviously be collecting data about the SQL Agent job executions. But there are also a few other types of historical data that needs to be cleaned up once in a while.

Click through for part 2 in the series, which covers a half-dozen more things.

Comments closed

Using Query Store to Track Regressions after Upgrades

Published 2021-03-23 by Kevin Feasel

Grant Fritchey has another use for Query Store:

There are a lot of uses for Query Store, but one of the most interesting is as an upgrade tool. We all know that upgrades in SQL Server can be more than a little bit nerve wracking. No matter how much you tested stuff in lower environments, deploying an update to production might result in performance issues as your code hits a regression. This is even more true when upgrading from versions of SQL Server prior to 2014 to anything 2014 and above. That’s because of the new cardinality estimation engine introduced in 2014. Most queries won’t notice it. Some queries will benefit from the better estimates. A few, problematic, queries will suffer. This is where Query Store can be used as an upgrade tool.

Read on to learn how.

Comments closed

Building a P&L Statement in Power BI

Published 2021-03-23 by Kevin Feasel

Matt Allington has a follow-up from a prior post:

I blogged about how to Build a P&L With Power BI back in April 2020, and the response from my readers was great. Several people asked how to add percentages such as % of net sales revenue, % gross profit, etc. into the P&L. I decided therefore to do a follow up article to show how you could add percentages to the P&L and also to further demonstrate how I go about solving such problems. Keep in mind, there is rarely one way to solve a problem in DAX; I’m simply sharing the way I solved this problem.

Click through for a video as well as a demo in screenshots.

Comments closed

Configurable Retry in Microsoft.Data.SqlClient

Published 2021-03-23 by Kevin Feasel

Hasan Savran notes an improvement to the Microsoft.Data.SqlClient library:

You need to watch for Transient errors if you use SQL Server in Azure. Transient errors or Retriable errors can occur any time and your application should be smart enough to retry these failed operations. Azure might quickly shift hardware resources of your database to give you a better load-balance, when this happens your application might not be able to connect to the database. Since these reconfiguration events completes quickly, your application needs to be designed to handle these faults.This adds more complexity to your code because you need to write code to handle this manually. 

      Preview version of  Microsoft.Data.SqlClient library now supports RetryLogic function, you do not need to write any manual code to handle Transient or retriable errors anymore. 

Click through for more details as well as a demonstration. I’m surprised it took this long, to be honest—useful retry logic is exactly the type of thing which should be in the bowels of a library rather than littered throughout business code (or worse, not even in business code).

Comments closed

Parameterization with TOP PERCENT

Published 2021-03-23 by Kevin Feasel

Erik Darling has a bone to pick with TOP PERCENT:

There was a three-part series of posts where I talked about a weird performance issue you can hit with parameterized top. While doing some query tuning for a client recently, I ran across a funny scenario where they were using TOP PERCENT to control the number of rows coming back from queries.

With a parameter.

So uh. Let’s talk about that.

Read on to see the mess of an execution plan TOP PERCENT creates and a more complex query which performs considerably better under the circumstances.

Comments closed

Parquet File Performance in Power Query

Published 2021-03-22 by Kevin Feasel

Chris Webb troubleshoots a performance issue:

There has been a lot of excitement around the newly-added support for reading from Parquet files in Power BI. However I have to admit that I was disappointed not to see any big improvements in performance when reading data from Parquet compared to reading data from CSV (for example, see here) when I first started testing it. So, is Power Query able to take advantage of Parquet’s columnar storage when reading data?

The answer is yes, but you may need to make some changes to your Power Query queries to ensure you get the best possible performance. Using the same data that I have been using in my recent series of posts on importing data from ADLSgen2, I took a single 10.1MB Parquet file and downloaded it to my PC.

It seem like an area of future growth for Power Query, but Chris does show how to eke out some gains right now.

Comments closed

Actions with Edge and Node Tables in SQL Server

Published 2021-03-22 by Kevin Feasel

Louis Davidson is a man of action:

One of the interesting things about working with many-to-many relationships in SQL Server with graph tables instead of a relational table is that unlike a relational many-to-many table, by default an edge may can implement relationships from lots of different tables (nodes). You can also limit what nodes can be related using which edges.

For example, say you have 4 nodes and 2 edges, both of the edges, by default, each edge would allow relationships from each node to itself, or each node to each other node. It can all get a bit complicated to figure out if you have a lot of objects (and to be fair, you probably also want to be able to check to make sure your objects are configured as you expect.

In this blog, I will demonstrate how to determine, given a given edge or node, what operations are possible. 

Click through to learn more.

Comments closed

Building a Friendship Lamp

Published 2021-03-22 by Kevin Feasel

Drew Furgiuele is looking for mood lighting tips:

It did get me thinking, though: what if I could take this idea and change it up a bit to where people could send me messages WITHOUT the need for them to have a lamp (and thereby give them plausible deniability of being, in fact, my friend). How would that work? In absence of a lamp, would a web application work? And what if we could let people pick a color in lieu of an actual message? You could send a whole mood!

And just like that, my motivation was restored. Time to get to work.

Click through for the build process, which includes 3D printing components, wiring and soldering to circuit boards, writing software for the IoT device, building the front-end web app, and more. Also, I sent red but now I’m not sure if I regret that color choice based on re-reading the first paragraph above.

Comments closed

Restoring a Database Formerly in an Availability Group

Published 2021-03-22 by Kevin Feasel

Jack Vamvas has a process for us:

Steps to restore a database from a backup device that was part of an Always On Availability Group, and now needs to be restored 

Recovery Scenario : Requesting an older database copy previously backed up 

Name of Always on Availability Group = MyAG1

Name of Always On Availability Group db = MyAGDB1

Note: this is a workflow – and there may be some slight variations depending your Availability Group set up 

Read on for rest of the workflow.

Comments closed

Disabling the Powershell Update Nag

Published 2021-03-22 by Kevin Feasel

Constantine Kokkinos hides an annoyance:

To be clear – I think you should be updating your PowerShell regularly, however the HUGE WHITE BLOCK ACROSS MY ENTIRE SCREEN EVERY TIME I LAUNCH VISUAL STUDIO CODE ISN’T GREAT.

Hated that caps? Yeah, that’s basically my eyes every time I see this nag window inverting the colors across my ennntiiirrreee screen.

Read on for the one-liner which gets rid of this message.

Comments closed