Press "Enter" to skip to content

Curated SQL Posts

Hardcoding Calculation Groups in a Measure

Published 2020-08-13 by Kevin Feasel

Kasper de Jonge works around a limitation with the Power BI UI and calculation groups:

AS we have seen calculation groups are great :). It offers amazing flexibility and is extremely easy to maintain. But sometimes it doesn’t do what you want due the limitations of the visuals. Let’s say I want to have a visual that shows me the sales of current year and sales Previous year on different axis (let’s say as line).

You would create something like the visual below where you want to use the same measure but apply different calc groups for each measure. But unfortunately, below visual is not as we want it to be.

But Kasper has us covered with a bit of DAX, so check that out.

Comments closed

A Trillion-Row Operator

Published 2020-08-13 by Kevin Feasel

Joe Obbish sets up a challenge:

48 billion rows for a single operator is certainly a large number for most workloads. I ended up completely missing the point and started wondering how quickly a query could process a trillion rows through a single operator. Eventually that led to a SQL performance challenge: what is the fastest that you can get an actual plan with at least one operator processing a trillion rows? The following rules are in play:

1. Start with no user databases
2. Any query can run up to MAXDOP 8
3. Temp tables may be created and populated but all such work needs to finish in 30 seconds or less
4. A RECOMPILE query hint must be present in the final query
5. Undocumented features and behavior are fair game

Read on to see what Joe learned.

Comments closed

Dropping Unused Indexes in Azure SQL DB

Published 2020-08-13 by Kevin Feasel

Monica Rathbun gives an important lesson around tracking index utilization in Azure SQL Database:

If the index has not shown any utilization I investigate to determine if it is one that can be removed. However, this week something caught my attention. I was looking at a client’s indexes and noted the values for these were not as high as I would have expected. I know that these index statistics are reset upon every SQL Server Service restart, but in this case, I was working on an Azure SQL Database. which got me wondering exactly how that worked. With an Azure Virtual Machine or an on Prem SQL Server instance this is easy to figure out. But with an Azure SQL Database we do not have control over when restarts are done, and what about the Serverless offering (which pauses unutilized databases to reduce costs), how do those behave?  I really want to make sure before I remove any indexes from a database that I am examining the best data possible to make that decision. So, I did some digging.

Read on to see what Monica discovered.

Comments closed

New Features in Python 3.9

Published 2020-08-12 by Kevin Feasel

Harini Guptha walks us through some of what’s upcoming in Python 3.9:

The latest beta preview python 3.9.0b5 is released on July 20th, 2020. Yes, you heard it right. This is the last of the five planned beta release previews. This beta release gives an opportunity to the community to test the new features.

If you are planning to go for a python certification, make sure that you check out what functionalities are deprecated and what features are newly added in this release. Its always good to get yourself updated with the latest information. Without further ado, let’s go through the new features of Python 3.9.0b5.

Click through for the list.

Comments closed

Mentoring from Paul Randal

Published 2020-08-12 by Kevin Feasel

Paul Randal is offering up mentorship time:

If I remember correctly, I think I helped 8 or more people decide to change jobs for a better work environment suited to their goals, and several people go it alone as consultants. It was hugely satisfying to help so many people with their careers and lives, in a non-technical capacity.

Now it’s time to do it again, as I haven’t done any public mentoring since 2015, so this blog post serves as a call for prospective mentees!

Please read the rest of this post carefully, so you’re clear how this works. We’re making a time commitment to each other so I want to be up-front about a few things.

I was one of those 54 mentees back in 2015 and can recommend it. I will say, though, that you get out of it exactly what you put in—this isn’t some “I want to advance my career” easy mode.

Comments closed

Tracking Resource Utilization by User

Published 2020-08-12 by Kevin Feasel

Brent Ozar uses most of Resource Governor:

You’ve got a bunch of users on the same SQL Server, and you wanna know who’s using the most resources. SQL Server doesn’t make this easy. You can query some management views to see which queries or databases have been using the most resources, but you can’t intuitively tell which USERS are.

To solve this problem, we’re going to use Resource Governor.

Wait. Come back.

I’ve always liked the idea behind Resource Governor and since about SQL Server 2016, it has been quite a useful product because “make some queries slow down” can absolutely be the right answer when those queries are harming the performance of queries which matter more.

Comments closed

How a 60GB Database Backup Became 1TB

Published 2020-08-12 by Kevin Feasel

Garry Bargsley points out the importance of a tiny flag:

I put on my investigator hat and begin looking around. I started with the Windows File Server to see what was actually on the drive in question. Just as I thought, three SQL backup files were in there proper folder. Although there were only three files, something else caught my attention. One backup file was 800GB and another 1TB. That was strange as I don’t think the source databases are that big. Sure enough, I look and one database is 60GB and the other is 45GB.

Something is not right here!! So, next, I run a RESTORE HEADERONLY against one of the backup files. What did I see?

Read on to learn what Garry saw, and then what Garry didn’t see.

Comments closed

Transaction Modes in SQL Server

Published 2020-08-12 by Kevin Feasel

I have a video and blog post out:

What I want to do in today’s post is to cover the different sorts of transaction modes and get into the debate about whether you should use explicit transactions or rely on auto-committed transactions for data modification in SQL Server. This came from an interesting discussion at work, where some of the more recent database engineers were curious about our company policy around transaction modes and understanding the whys behind it. I didn’t come up with the policy, but my thinking isn’t too far off from the people who did.

But before I get too far off course, let’s briefly lay out some of the basics around transactions.

Read on for a good deal of info on the different transaction modes, including a bit on why implicit transactions (as opposed to autocommit transactions) are a bad thing in SQL Server.

Comments closed

Transaction Isolation Levels in SQL Server

Published 2020-08-12 by Kevin Feasel

Dan Jackson walks us through the different transaction isolation levels in SQL Server and what they mean for us:

We will start with a definition and then evolve it: the isolation level specifies how much one transaction must be protected from resource or data modifications made by other transactions.

Consider the case where user A is trying to read a list of products out of a table, meanwhile user B comes along and changes some of the product information in the table. As part of their same transaction, user A comes back to try and read the product table, but it has changed. Do you want user A to read the new information or not?

Isolation levels allow you to decide what would happen in scenarios like the one I’ve just described and so it should come as no surprise that they are described in terms of which concurrency side effects they allow.

Read on for a description of typically-undesirable side effects and the isolation levels which prevent them.

Comments closed

TOP and Ordering

Published 2020-08-12 by Kevin Feasel

Erik Darling is in the middle of a back-to-basics series on performance tuning:

And you see, once you set up a query to return the TOP N rows, there’s an expectation that users get to choose the order they start seeing rows in. As long as we stick to columns whose ordering is supported by an index, things will be pretty stable.

Once we go outside that, a TOP can be rough on a query.

Read on for an example of what happens when that type of thing goes wrong.

Comments closed