Press "Enter" to skip to content

Month: May 2022

Finding the Busiest Database

Published 2022-05-04 by Kevin Feasel

David Fowler looks for the biggest, meanest database on a SQL Server instance:

I’m sure we’ve all been there at some point, sometimes it’s easy to tell. We might only have a handful of databases on the server with one known to be the heaviest utilised. But sometimes things might not be so obvious, there could be a large numbers of databases or no obvious resource hog.

In those instances we need some way to figure out what how much time each database is spending on the CPU if that’s what we’re interested in or perhaps the total number of page reads or writes if IO is our problem.

Read on for one way to do this, assuming that the instance has been up long enough to give you reliable results.

Comments closed

Transparent Data Encryption in RDS

Published 2022-05-04 by Kevin Feasel

Tom Collins enables Transparent Data Encryption in AWS’s RDS:

Does AWS RDS SQL Server support built-in SQL Server Transparent Data Encryption (TDE)?  Yes , is the short answer , but there are some limitations  . 

Before I dive into the AWS SQL Server RDS TDW limitations , in this post  we’re discussing SQL Server TDE as opposed to RDS Encryption covering Aurora, MySQL, MariaDB, PostgreSQL, Oracle, SQL Server

Read on for the limitations Tom mentions.

Comments closed

Mistakes to Avoid when Creating Power BI Reports

Published 2022-05-04 by Kevin Feasel

Reza Rad shares some advice:

A relationship in Power BI is often for when we want a table to filter another table (based on the selection of items in the visuals). This filtering happens in the direction of the relationship. Meaning that if DimCustomer has a one-to-many relationship with the FactSales, and the relationship direction is single directional from the DimCustomer to FactSales, then DimCustomer can filter the FactSales, but not the other way around.

What we see a lot in the Power BI models is that the developer changes the relationship to both-directional so that each of the tables can filter the other table. This looks like a nice feature, but it will come at a big cost.

Click through for five good tips in blog format as well as written form.

Comments closed

Automated Partitioned Table Management

Published 2022-05-04 by Kevin Feasel

Eitan Blumin automates creation and deletion of partitions in SQL Server:

Before we begin, there are a few “ground rules” we should understand first:

1 – Partition Functions define the partition ranges

This means that whenever we want to eliminate an old partition range or add a new partition range, the PARTITION FUNCTION is the object that we actually need to modify.

Click through for Eitan’s entire process and a couple of scripts. This is an area that SQL Server could have made a lot easier, especially for periodic processes, by including options like “Daily” or “Monthly” or “Weekly(start on Monday)” for intervals rather than making people specify every partition separately.

Comments closed

Things Not to Include in Data Warehouses

Published 2022-05-04 by Kevin Feasel

Erik Darling compiles a list:

This is a list of things I see in data warehouses that make me physically ill:

– Unique constraints of any kind: Primary Keys, Indexes, etc. Make things unique during your staging process. Don’t make your indexes do that work.

Read on for the full list. I agree with everything except clustered row-store indexes. Those make a lot of sense on dimension tables, tied to the Kimball-style surrogate keys you create in the warehouse itself.

The other part I disagree with is non-clustered columnstore indexes, which I’ve rarely found good use for. Clustrered columnstore indexes are outstanding but the non-clustered variety…meh at best. This answer comes primarily because the pattern I tend to use for warehouse queries is to drive from the fact table, aggregate as much as I can there, and connect to the dimensions for further information at the end. If your warehouse access patterns differ radically from this, you might get more out of non-clustered columnstore indexes. Maybe.

Comments closed

The take Operator in KQL

Published 2022-05-03 by Kevin Feasel

Robert Cain continues a series on KQL:

In this example we took the Perf table, and piped the dataset it generated into the take operator. We indicated we wanted to get 10 rows, which it did as you can see.

It is important to understand that take grabs these rows at random. Further, there is no consistency between each execution of take. You are likely to get a different set of rows with each execution. Let me run the exact same query again, so you can see the different data being returned.

Take if you want a slice, if you want a piece, if it feels alright.

Comments closed

Separating Code from Presentation with Jupyter

Published 2022-05-03 by Kevin Feasel

John Mount disaggregates Jupyter notebook results:

As I switch back and forth between R and Python projects for various clients and partners, I got to thinking: “is there an easy way to separate code from presentations in Jupyter notebooks?”

The answer turns is yes. Jupyter itself exposes a rich application programming interface in Python. So it is very easy to organize Jupyter’s power into tools that give me a great data science and analysis workflow in Python.

Read on to see how.

Comments closed

Alerting on Azure Budget Thresholds

Published 2022-05-03 by Kevin Feasel

Daron Yondem makes a budget:

You can’t imagine how many of us forget to set up the proper alerting mechanisms for our cloud subscription consumption. Here is how to do it in Azure in under 2 minutes.

Read on for the answer. I do like Azure’s budgeting tools except for one big thing: you can’t set a cap. Alerting is great but I want to have a “break glass in case of emergency” capability to stop spend altogether if you hit a certain point. I wouldn’t use it in production but for personal or development accounts, that’s big. And you can do it but only when you have a subscription which uses Azure credits—as soon as dollars are involved, there are no caps.

Comments closed

Using Extended Events with AWS RDS

Published 2022-05-03 by Kevin Feasel

Grant Fritchey tries out extended events in Amazon’s RDS:

AWS has posted the documentation on what you have to do in order to enable the collection of Extended Events within RDS. Normallly, I’d follow along with the documentation. However, I’m going to approach this like I knew that Extended Events support was there, but I wasn’t aware of the docs. So, I’m starting in SSMS and I’m just going to try plugging in the Extended Events GUI to see what happens. Further, I’m going to use the simplest method for launching Extended Events, XEvent Profiler. 

Read on for Grant’s findings.

Comments closed

Empty Catch Blocks in T-SQL

Published 2022-05-03 by Kevin Feasel

Erik Darling looks disapprovingly:

So if you execute the above proc, all it returns is an empty result with no error message.

Kinda weird.

Like not having finger or toenails.

I’m dating myself here but I remember a comment in a TheDailyWTF thread in which somebody puts an ASCII art Pikachu in every empty catch block because hey, you gotta catch ’em all.

Comments closed