Press "Enter" to skip to content

Curated SQL Posts

Maximizing Availability Group Performance

Published 2021-06-10 by Kevin Feasel

Jonathan Kehayias has a few tips for improving performance of your Availability Groups:

Since Microsoft first introduced the Always On Availability Groups (AGs) feature in SQL Server 2012, there’s been a lot of interest in using AGs for both high availability and disaster recovery (HADR), as well as for offloading read-only workloads. The combination of the best features for failover clustering, the simplicity of data movement and synchronization from database mirroring, and the ability to offload read-only workloads to secondaries has given businesses a compelling reason to upgrade to leverage AGs.

But, as the saying goes, there’s no such thing as a free lunch, and there are several performance implications and considerations you must be aware of to have a successful deployment using AGs. This blog post will explore some of the considerations and look at how to plan, architect, and implement an AG with minimal latency and performance impact on the production workload.

Click through for those tips.

Leave a Comment

Limitations in Power BI Aggregations

Published 2021-06-10 by Kevin Feasel

Teo Lachev looks at a couple of limitations in Power BI aggregations, as well as workarounds for those limitations:

Power BI aggregations are meant to speed up queries to large DirectQuery tables, as a DBA would create summarized tables to speed up queries to large tables. The most appealing aspect of telling Power BI about these aggregations is that Power BI will automatically redirect the query to the aggregation cache if it determines that its dimensionality matches the dimensionality of the aggregated table, as explained in the documentation. However, there are a couple of limitations worth emphasizing that will prevent this from happening:

Click through for those limitations and what Teo & co did to move forward despite them.

Leave a Comment

Clarifying Confusion around Power BI Goals

Published 2021-06-10 by Kevin Feasel

Treb Gatte continues a series on Power BI Goals:

Power BI Goals enables you to present the status of a key outcome that can optionally be tied to data. Treating Power BI Goals as a glorified hierarchy of metrics may lead you to miss a more valuable use value of Goals.

Note, Goals do not roll up. The hierarchy is there to provide a context for the goal and subordinate goals. If you need data rollup, you may want to look at alternatives.

Part 4 of our blog series covers the ability to support OKRs (Objectives and Key Results) with Power BI Goals. OKRs are a very powerful mechanism for remote workers to stay in sync and focused on the most important work.

Read the whole thing.

Leave a Comment

Creating a dacpac for a Dedicated SQL Pool

Published 2021-06-10 by Kevin Feasel

Kevin Chant shows how to use Azure DevOps to create a dacpac for an Azure Synapse Analytics dedicated SQL pool:

By the end of this post, you will know how to create a dacpac for a dedicated SQL Pool within Azure Pipelines for your CI/CD deployments. Plus, how you can synchronize a database project created in Azure Data Studio with a Git repository in Azure DevOps.

In a previous post I covered how you can create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure Data Studio. In that post I stated that you could create a dacpac for the database project using Azure DevOps.

With this in mind, I will use the same database project that I created in that post.

Click through for the process.

Leave a Comment

The End of the NFT Bubble(?)

Published 2021-06-09 by Kevin Feasel

Stephanie Glen has music to my ears:

Non-fungible tokens (NFTs), tradable digital certificates that verify ownership of digital assets using blockchain technology, have dominated headlines in the last several months. The media mania hit a high with the $69 million sale of Beeple’s Everydays:The First 5000 Days. A few months after Beeple’s historic sale at Christie’s auction house, the crypo-art bubble has officially burst.

These sorts of things are a bit too volatile for me to cheer just yet. The blockchain bubble is something I look at and say, this is incredibly dumb. The whole premise of it makes zero sense: you’re wasting resources (and don’t get me started on Chia, the grim reaper for residential SSDs) for nothing. The end product has extremely little to no subjective value—how much would you pay for blockchain outputs?—but burns up resources in the form of energy, increased prices for computer components, and time that could have been spent doing something more productive, like repeatedly turning your computer off and on again: at least there, you gain valuable skills in figuring out how to power down and power up a machine.

I can kinda-sorta get the idea of using blockchain for certain types of auditing trails, but there are still two big problems with it. First is the 50% problem: whoever controls 50% of the compute controls the past, present, and future of the blockchain and can make whatever arbitrary changes are desired. Beyond that, the other problem is, how much better is this than a digest hash of activities written to a WORM drive? Considering how many orders of magnitude less expensive the latter is to the former, there has to be an enormous benefit for it to make any sense. And there’s really not.

Leave a Comment

The Value of a Working Dev Environment

Published 2021-06-09 by Kevin Feasel

Tim Mitchell wants to talk about dev environments:

Let’s talk about your development environment.

Specifically, I’d like to chat with you about the virtual space where your data architecture team, software developers, and information curators do their development and testing work. A proper development environment is logically separated from the production environment, and is often further partitioned into different realms for initial development, data or functional validation, and user acceptance testing. For mature enterprise-ready environments, there is also usually a build and deployment process that automates the movement of code from one environment to the next, reducing the chance for human error when moving code through its paces and ultimately into the production environment.

I’d like optimistically to say that Tim is using strawmen here, but I’ve worked in (and sometimes created) pretty much each one of these.

Leave a Comment

Comparing Views to Derived Tables and CTEs

Published 2021-06-09 by Kevin Feasel

Itzik Ben-Gan takes us through views:

As usual when discussing relational theory, we SQL practitioners are often told that the terminology we’re using is wrong. So, in this spirit, right off the bat, I’ll start by saying that when you use the term tables and views, it’s wrong. I’ve learned this from Chris Date.

Recall that a table is SQL’s counterpart to a relation (oversimplifying the discussion around values and variables a bit). A table could be a base table defined as an object in the database, or it could be a table returned by an expression—more specifically, a table expression. That’s similar to the fact that a relation could be one that is returned from a relational expression. A table expression could be a query.

Now, what is a view? It’s a named table expression, much like a CTE is a named table expression. It’s just that like I said, a view is a reusable named table expression that is created as an object in the database, and is accessible to those who have the right permissions. This is all to say, a view is a table. It’s not a base table, but a table nonetheless. So just like saying “a rectangle and a square” or “a whisky and a Lagavulin” would seem strange (unless you had too much Lagavulin!), using “tables and views” is as improper.

Yeah, if we’re going to push our glasses up the bridges of our noses and get all relational here, we’d have the relvar (which is a name and collection of attributes + data types) and the relation (which you can think of as a relvar at a particular point in time—that’s where we get tuples of data). And as Itzik points out, what the RDBMS world calls a view quite neatly fits the definition of a relvar, as we define a name and collection of attributes + data types.

If all of this could not mean less to you, still read the article for Itzik’s view on views.

Leave a Comment

Converting from XML in Powershell

Published 2021-06-09 by Kevin Feasel

Phil Factor has unleashed the full power of XML:

I want to convert reasonably small XML files to hash tables and PowerShell objects. PowerShell never had a ConvertFrom-XML Cmdlet because gulping a large XML file into a PowerShell data object is expensive in resources. It is the sheer time it takes to consume a large XML file. Instead, you have to use the XMLDocument object to navigate to the data you want or use an Xpath query. It is all well and good to handle XML in this way, but it is inconsistent to have no ConvertFrom-XML cmdlet. After all, there is a ConvertFrom cmdlet for CSV, JSON, and a variety of text-based data. It would be good to have one for XML as well. Usually, I just want to consume relatively small XML files and just pick out the data I want. I hoped that one that worked would turn up but somehow it never did. So I wrote my own.

Click through for that script, as well as some considerations about using it.

Leave a Comment