Press "Enter" to skip to content

Author: Kevin Feasel

Index Rebuilds versus Reorganizations

Published 2024-09-05 by Kevin Feasel

Sergey Gigoyan opens a can of worms:

When data is modified in a Microsoft SQL database, the corresponding indexes are also modified. These modifications cause indexes to become fragmented. Fragmentation means that the logical order of the data inside the index pages does not match its physical order. The more the fragmentation level is increased, the more performance is affected for SELECT statements. With fragmented indexes, the information is not spread logically, making the index’s data retrieving operations more time-consuming resulting in query performance issues. Thus, fragmentation of the indexes should be fixed periodically in order to maintain high performance. Rebuild and Reorganize index operations are aimed at defragmenting indexes.

Click through to learn a bit about why we may need index maintenance, followed by a comparison between index rebuilding versus index reorganization. All that said, I do not have room in my indexing philosophy (or my heart) for reorganization. Reorganization simply doesn’t provide enough value as it is. If you want a really detailed dive into the topic, Jeff Moden put together an amazing talk that we were lucky enough to be able to record. Specifically, about an hour and a quarter in (link to timestamp), Jeff gets to the topic of reorganization and how little it actually does in practice.

Comments closed

Granting Entra ID Guest Users Access to a Semantic Model via Excel

Published 2024-09-05 by Kevin Feasel

Gilbert Quevauvilliers notes an implication:

Yes, you read that right, it is possible where you have shared Power BI semantic model with an external (guest user in Entra ID) they can now connect to the semantic model using Excel.

This has been around for quite a while I just did not know about it, here is the Microsoft documentation: Semantic model connectivity and management with the XMLA endpoint in Power BI – Power BI | Microsoft Learn

The first thing to note is that this currently only works in the Excel Desktop App.

I did try using Excel on the Web and unfortunately that did not work.

I then created my Excel file, uploaded to SharePoint Online and tried to interact with the Excel Pivot table and that too did not work.

Ok enough of the limitations let me show you how to get it working.

Click through to see how. Gilbert also has an important note about row-level security, co read the whole thing.

Comments closed

T-SQL Snapshot Point-in-Time Recovery to Azure VM

Published 2024-09-05 by Kevin Feasel

Anthony Nocentino continues a series on T-SQL snapshot backups:

In this post, the third in our series on using T-SQL Snapshot Backup, I will guide you through using the new T-SQL Snapshot Backup feature in SQL Server 2022 to take a snapshot backup and then perform point-in-time database restores using that snapshot backup as the base, but this time using an Azure Virtual Machine. We will explore how to manage Azure storage-level operations, such as taking snapshots, cloning snapshots, and executing an instantaneous point-in-time database restore from the snapshot with minimal impact on your infrastructure. Additionally, I will demonstrate a PowerShell script that utilizes dbatools and Azure Az modules to automate the process.

Read on for the script and plenty of details.

Comments closed

Sampling without Replacement and Unequal Probabilities

Published 2024-09-04 by Kevin Feasel

Peter Ellis finds interesting results with sampling in R:

A week ago I was surprised to read on Thomas Lumley’s Biased and Inefficient blog that when using R’s sample() function without replacement and with unequal probabilities of individual units being sampled:

“What R currently has is sequential sampling: if you give it a set of priorities w it will sample an element with probability proportional to w from the population, remove it from the population, then sample with probability proportional to w from the remaining elements, and so on. This is useful, but a lot of people don’t realise that the probability of element i being sampled is not proportional to w_i”

Read on for a demonstration. H/T R-Bloggers.

Comments closed

Building an App to Use Fabric AI Skills Locally

Published 2024-09-04 by Kevin Feasel

Sandeep Pawar takes us on-premises:

If you are a regular reader of this blog, you probably know I have been testing Fabric AI Skills extensively. I have written three blogs so far on various ways the AI Skills endpoint can be used. The feature is still in preview but I am excited to see how it can be used to create new solutions as it matures.

I was curious to test if the AI Skills endpoint can be used locally and in other applications. This will open many opportunities to integrate it in different tools, inside and outside of Fabric ecosystem. So, I built an app using Gradio to make API calls to the endpoint and show the results in a local browser along with interactive plots.

Click through for a link to the code and some instructions on how to build it yourself.

Comments closed

Core Concepts of Vector Databases

Published 2024-09-04 by Kevin Feasel

Brendan Tierney continues a series on vector databases:

In this post on Vector Databases, I’ll look at the main components:

  • Vector Embedding Models. What they do and what they create.
  • Vectors. What they represent, and why they have different sizes.
  • Vector Search. An overview of what a Vector Search will do. A more detailed version of this is in a separate post.
  • Vector Search Process. It’s a multi-step process and some care is needed.

Read on for more about these terms and ideas.

Comments closed

Connecting Snowflake to Microsoft Fabric

Published 2024-09-04 by Kevin Feasel

Stephanie Bruno makes a connection:

If you’re new to Snowflake and you need to mirror a Snowflake database in Microsoft Fabric, where do you begin? The steps are straightforward enough, but when trying something new, I often get tripped up by the basics. In this case, the configuration screen for mirroring. The documentation tells us to simply enter the server and warehouse, and provides some helpful information on where to find the details, but I prefer step by step instructions with pictures. If you do, too, then this post is for you.

Click through for a walkthrough.

Comments closed

Missing Columns in the Extended Events Live Data Explorer

Published 2024-09-04 by Kevin Feasel

Grant Fritchey explains a UI oddity:

Let me be extremely clear up front, this is not my original work. I saw this post on DBA.StackExchange.com and I wanted to share and promote it. Nice work FevziKartal.

The rest of this post is just me replicating work already done by others. I just want to see it in action.

Read on for the example and what happens when you don’t have any events in the live data explorer.

Comments closed

Thoughts on T-SQL Snapshot Backups

Published 2024-09-04 by Kevin Feasel

Anthony Nocentino has a two-parter for us. First is the idea of how T-SQL snapshot backups work:

Traditional SQL Server backups can struggle with large databases, resulting in longer backup times and resource contention. T-SQL Snapshot Backup, a new feature in SQL Server 2022, addresses these challenges by allowing storage-based snapshots to be coordinated through T-SQL. This feature delivers faster, more efficient backups, especially for large-scale environments with the most aggressive of recovery objectives.

The follow-up is for point-in-time recovery from a T-SQL snapshot backup:

In this post, the second in our series, I will guide you through using the new T-SQL Snapshot Backup feature in SQL Server 2022 to take a snapshot backup and perform point-in-time database restores using a snapshot backup as the base of the restore. We will explore how to manage storage-level operations, such as cloning snapshots and executing an instantaneous point-in-time restore of a database from the snapshot with minimal impact on your infrastructure. Additionally, I will demonstrate a PowerShell script that utilizes dbatools and the PureStoragePowerShellSDK2 modules to automate the process.

Check out both posts and be on the lookout for subsequent entries in the series.

Comments closed