Press "Enter" to skip to content

Curated SQL Posts

External Tables and the Serverless SQL Pool

Published 2023-01-27 by Kevin Feasel

Ryan Adams continues a series on querying the serverless SQL pool in Azure Synapse Analytics:

There are two ways to read data inside Data Lake using the Synapse Serverless engine.  In this article, we’ll look at the second method which uses an external table to query a path within the lake.

Synapse is a collection of tools with four different analytical engines (Dedicated Pool, Spark Pool, Serverless Pool, Data Explorer Pool).  This gives you a lot of options for ingesting, transforming, storing, and querying your data.  Here you will use the Synapse Serverless Pool to query the data in your ADLS account.   

Read on for a demonstration.

Comments closed

Minimizing Calculated Column Usage in Power BI

Published 2023-01-27 by Kevin Feasel

Ed Hansberry wants us to slow down and think:

First off I want to make sure it is clear that I am not saying “never use calculated columns.” I am not. What I am saying is they should be avoided, and there are several reasons. I am going to focus on the model size, and thus performance. Note that the data I am going to show is a contrived set of data for demonstration purposes only to explain.

Read the whole thing, including a set of questions you might want to ask yourself before deploying that PBIX file with calculated columns in it.

Comments closed

SSMS 19.0 Released

Published 2023-01-27 by Kevin Feasel

Erin Stellato has a new version of Management Studio for us:

In addition, we received a lot of feedback about the dialog box, “Querying transaction count”, we introduced back in SSMS 18.11.   In 18.12, we added an option to cancel the check if it took more than two seconds, but user comments indicated that was not enough.  Therefore, in SSMS 19.0 you have the ability to disable to dialog box completely.  Within Tools > Options > Query Execution, simply uncheck the option “Check for open transactions before closing T-SQL query windows”.  

That alone might be worth it, especially if you work via VPN and have a bunch of SSMS tabs open.

Comments closed

TempDB Improvements in SQL Server Versions

Published 2023-01-26 by Kevin Feasel

Jared Poche looks at the recent past:

Tempdb contention has long been an issue in SQL Server, and there are many blogs on the issue already. But I wanted to add one more mainly to highlight the improvements in recent versions of SQL Server

Tempdb contention is most often discussed in as relating to the creation of temp tables (and other objects) in tempdb. If you are experiencing this you will see PAGELATCH_EX or PAGELATCH_SH waits, frequently with wait resources like 2:1:1 or 2:1:3. This indicates contention in database 2 (tempdb), page 1 (the first data file in tempdb), and one of the PFS, GAM, or SGAM pages (which are pages 1, 2, and 3 respectively). Tempdb files of sufficient size will have additional PFS, GAM, and SGAM pages at higher page numbers, but 1 and 3 are the pages most often referenced.

Read on to see what the SQL Server team has done over three of the past four versions of SQL Server to limit tempdb contention.

Comments closed

Power BI Publish to Web

Published 2023-01-26 by Kevin Feasel

Reza Rad takes us through the Publish to Web option in Power BI:

Publish to the web is an easy way of sharing public data. However, it has some disadvantages as well. In this article and video, you will learn about this feature in more detail, and you will learn this feature is, and how it is different from Power BI Embedded. It will be a very long article if you want to learn about both Power BI Embedded and Publish to the web and compare them here. So in this article, I’ll explain Publish to Web, and in another article, you’ll learn about Power BI embedded and their differences. In this article, you will learn how easy it is to share your report with the public through a web page which can be your blog post, an HTML page, or any other web page. Some frequently asked questions about this feature are answered through the content of this article. If you like to learn more about Power BI, read Power BI online book, from Rookie to Rock Star.

Read on for the video as well as a full-length article.

Comments closed

Notes on DBATools’s Get-DecryptedObject

Published 2023-01-26 by Kevin Feasel

Sean Gallardy explains an issue:

Let me start off by saying that DBATools is a great tool that is immensely helpful to the community, if you haven’t heard about it or tried it, please give it a shot! I personally believe that the SQL Server community is truly the best out there, with so many people helping each other and developing great, free, tools that benefit all. Having said that, I’ve been asked quite a few times to look into a “SQL Server Bug” decrypting data.

This is not an issue with SQL Server nor is it a bug/feature/whatever. It is, in fact, a bug in the source material for DBATools.

Read the whole thing.

Comments closed

Azure Data Studio 1.41 Now Available

Published 2023-01-26 by Kevin Feasel

Erin Stellato has a bundle of updates for us:

The query results window got a fair bit of attention this release as we work through the backlog of open issues. First, we introduced a new configuration option to show or hide the action bar in the query results view. The Query Editor > Results: Show Action Bar option can be found in the command palette (CTRL + , ) if you type Show Action Bar. By default, the action bar is shown in the query results pane, as seen in the screenshot below:

Check out the full set of changes in the article.

Comments closed

App-Consistent and Crash-Consistent Snapshots in SQL Server

Published 2023-01-26 by Kevin Feasel

Andrew Pruski talks snapshots:

When we talk about snapshots of SQL Server there are two types, application consistent snapshots and crash consistent snapshots.

Application consistent snapshots require freezing IO on a database allowing for a checkpoint to be performed in order to write all dirty pages to disk.

In the past, application consistent snapshots relied on the third party software to call the SQL Writer service but now with SQL Server 2022 T-SQL snapshot backups we have the ability to use T-SQL commands to freeze IO on a database in order for us to take an application consistent snapshot of the database.

Read on for more detail on the topic. With SQL Server 2022, I’ve softened a bit on using snapshots for data retention—prior to that, I was not a fan of the idea.

Comments closed

Tools for the DBA Jumpbox

Published 2023-01-25 by Kevin Feasel

Tracy Boggiano has a script:

I wrote a blog post a few months ago about the tools I use on my jumpbox you can read here.  Since then, I have automated most of the install with Chocately and so I’ll have the script later I’m going to blog it here.  First, we need to install Chocately on the jumpbox by opening PowerShell as the adminstrator.

Read on to see how to do that, the installation of a slew of tools via Chocolatey, and then several Powershell modules.

Comments closed