Press "Enter" to skip to content

Month: October 2022

Data Virtualization in SQL Server 2022

Published 2022-10-06 by Kevin Feasel

Hugo Queiroz provides an overview of data virtualization options in SQL Server 2022:

SQL Server 2022 now supports CSV, Parquet, and Deltafiles stored on Azure Storage Account v2, Azure Data Lake Storage Gen2, or any simple storage service (S3)–compliant object storage—the last as an on-premises offering or in the cloud. Finally, SQL Server 2022 can now use Create External Table as Select (CETAS), together with commands like OPENROWSET, Create External Table (CET), and all the new T-SQL enhancements. SQL Server 2022 is a powerful data hub.

The post doesn’t get too deep into the topic, though a search here will find you links to articles with concrete examples.

Comments closed

Lock Escalation Thresholds and Parallelism

Published 2022-10-06 by Kevin Feasel

Paul White complicates matters:

In part one of this series, I explained how the lock manager maintains a count of held locks per transaction, and individual access methods keep a separate lock count per HoBt for each plan operator performing data access.

Parallelism adds an extra layer of complexity because both lock counts are maintained per thread.

There are two underlying reasons for this:

Paul explains the reasons and shows off plenty of tests along the way.

Comments closed

Tracking Power BI Desktop Activity in SQL Server

Published 2022-10-06 by Kevin Feasel

Chris Webb looks in on things:

Something I do all the time when performance tuning Power BI is use SQL Server Profiler to monitor query and refresh activity. There’s a handy external tool that lets you open up Profiler with a connection to Power BI Desktop; if you’re using Power BI Premium you can also connect Profiler up to a published dataset using the XMLA Endpoint. Profiler is a bit old-school though and likely to lead to snarky comments from SQL Server DBAs (it’s not deprecated for Analysis Services though!) who’ll say you should be using Extended Events (aka xEvents) instead. And guess what – did you know you can use Analysis Services xEvents to monitor activity in Power BI Desktop?

It’s the power of Extended Events.

Comments closed

IIF in KQL

Published 2022-10-05 by Kevin Feasel

Robert Cain didn’t stutter:

Pretty much every query language, indeed any coding language, has a form of an if statement. An if statement evaluates a condition for true or false, then takes an action based on that result.

In KQL, this is manifested using the iif function. In this post we’ll see two examples of how an iif can be used in your Kusto queries.

Check it out, iif you dare.

Comments closed

Storytelling and the Brain

Published 2022-10-05 by Kevin Feasel

Kristyna Hughes recaps a presentation:

To turn our millions of dollars worth of data into actionable insights, we need to tell a story with it. Why a story? Storytelling has been and is a vital way humans understand and share the world around them. If numbers were as interesting and memorable as stories, our classic works of art would be the Pythagorean theorem instead of the Iliad and Odyssey. Listening to a story engages multiple parts of the brain, which is why listening to your great aunt’s story for hours is exhausting, but also why stories are more memorable. Here are the parts of the brain engaged when you hear a story:

There’s a lot to digest in this post, including ways to convert storytelling into data-driven insights.

Comments closed

Monitoring Blocked Processes in Azure SQL DB

Published 2022-10-05 by Kevin Feasel

Etienne Lopes wants to see what the hold-up is:

Blocked processes (and deadlocks) are often one of the main factors responsible for performance issues in the databases, as such, it’s really important to monitor them effectively and if they exist then understand where, what, why, how often, duration, etc. Having that information will greatly help in the following fine tuning process.

In this post I’ll show one way to easily monitor blocked processes in an Azure SQL database.

Click through to learn how.

Comments closed

Incremental Refresh in Power BI

Published 2022-10-05 by Kevin Feasel

Reza Rad doesn’t have time to wait for a full dataset reload:

The default configuration for the Power BI dataset is to wipe out the entire data and reload it again. This can be a long process if you have a big dataset. Hybrid tables in Power BI keep part of the data in DirectQuery, and the rest is imported for data freshness and performance. In this article, I explain how you can set up an incremental refresh in Power BI and its requirements. You will also learn about Hybrid tables in Power BI. Incremental Refresh is not just in Power BI datasets but also in Dataflows and Datamarts. In this article, you learn to load only part of the changed data instead of loading the entire data each time. To learn more about Power BI, read the Power BI book from Rookie to Rock Star.

Click through for the article.

Comments closed

Architecting a Data Lake

Published 2022-10-05 by Kevin Feasel

James Serra provides some guidance:

I have had a lot of conversations with customers to help them understand how to design a data lake. I touched on this in my blog Data lake details, but that was written a long time ago so I wanted to update it. I often find customers do not spend enough time in designing a data lake and many times have to go back and redo their design and data lake build-out because they did not think through all their use cases for data. So make sure you think through all the sources of data you will use now and in the future, understanding the size, type, and speed of the data. Then absorb all the information you can find on data lake architecture and choose the appropriate design for your situation.

The concepts are simple but there are some interesting implications to what James includes as well as additional resources, so check it out.

Comments closed

Finding the Resource Database

Published 2022-10-05 by Kevin Feasel

Chad Callihan confirms that you can see the resource database but only in your peripheral vision:

Can you name each of the system databases in SQL Server?

You might be able to name master, msdb, model, and tempdb. What about the fifth system database? Don’t feel bad if you’re drawing a blank.

The fifth system database is the Resource database. Even if you’ve never heard of the Resource database, chances are that you’ve used it. Let’s discuss how that can be.

Click through to learn what the resource DB is and why you typically won’t see it.

Comments closed