Press "Enter" to skip to content

Month: August 2020

Finding Heaps in a Database

Published 2020-08-24 by Kevin Feasel

David Fowler has a quick one for us:

This is just going to be a quick post but I was asked the question the other day, how can I find all the tables in the database that don’t have a clustered index?

Tables without clustered indexes, known as heaps can give you quite a few different performance problems for various reasons. I don’t really want to go into those here, that could be a subject for another post but finding these heaps is actually very simple.

And David has a short script to show just how simple it is.

Comments closed

Generating Stored Procedure Mappings for Entity Framework Core

Published 2020-08-24 by Kevin Feasel

Erik Ejlskov Jensen takes us through stored procedure mapping with Entity Framework Core Power Tools:

In my previous post I showed how you can map and use stored procedures manually from EF Core, a process which involved quite a bit of code, and some changes to your derived DbContext.

With the latest release of EF Core Power Tools, you can opt-in to have any SQL Server stored procedures in your database made available to you.

Click through to learn how to do this.

Comments closed

Creating KPIs in Power BI

Published 2020-08-24 by Kevin Feasel

Alberto Ferrari takes us through key performance indicator creation in Power BI Desktop:

Starting from the July 2020 version, Power BI Desktop offers the possibility of using external tools to modify its internal Tabular model. With a tool like Tabular Editor, you can create a KPI directly in Power BI Desktop so that it can be used in any Power BI report and also by using the Analyze in Excel feature. The KPI feature was previously available only in Tabular models created in Analysis Services or Power BI Premium. This introductive article shows you how to create and consume KPIs in Power BI Desktop. A more detailed description of the available KPI graphics and the corresponding state values is the topic for an upcoming article.

Let us see the feature with a practical – though fictitious – example. Say Contoso needs to analyze the Margin % of its products. The yardstick is the overall margin, which is the Margin % over time and products with a tolerance of 2%. The overall margin of Contoso is 53%. Therefore, a category with a Margin % less than 51% is considered bad (red), over 55% is considered good (green), in between 51% and 55% is considered average (yellow). Moreover, Contoso wants to analyze the trend of Margin % compared with the previous year. For example, the margin might be red but Contoso can evaluate which action to take depending on whether it is improving or not over time.

Read on for the demonstration.

Comments closed

Semantic Search in SQL Server

Published 2020-08-24 by Kevin Feasel

Haroon Ashraf wraps up a series:

Being the final part of the article, it is going to take you to the next level of analyzing word documents stored in Windows folders, managed by File Table, and consumed by Semantic Search.

Additionally, the readers are going to gain more understanding of Semantic Search and how to make it work with MS Word documents for analysis.

This article provides a name-based analysis of the documents with equal attention to both theory and practice.

Click through for the culmination of all of this filestream work.

Comments closed

ADF Data Flows and Joins Failing During Debugging

Published 2020-08-20 by Kevin Feasel

Mark Kromer clears up some issues around debugging in Azure Data Factory:

One of the important features built into ADF is the ability to quickly preview your data while designing your data flows and to execute the finished product against a sampling of data prior to finalizing and operationalizing your pipelines.

However, there are a few fundamentals relative to working with Joins that you should keep in mind and a few details below are important to understand at design time and while debugging / testing.

The answer makes sense but it would not have been the first thing to come to mind for me.

Comments closed

Using Perspectives in Power BI

Published 2020-08-20 by Kevin Feasel

Mark Lelijveld walks us through something new in Power BI Desktop’s August 2020 update:

If you work or used to work with Analysis Services, you might know the perspectives functionality. It is a feature inside tabular modelling that allows you to define viewable subsets of a data model.

Each tabular model can include multiple perspectives, where each perspective can include a subset of tables, columns a measures. Especially with large enterprise models, perspectives can be very useful.

With perspectives, you can define specific perspectives to be defines for a specific target audience. For example, the author can create logical subsets of the model for each audience of the dataset. (e.g. Sales, Finance, Marketing, etc.) One thing must clear, perspectives are not object level security or any other kind of security! It is just a better way to view it.

Read on to see how you can create and work with these in Power BI Desktop.

Comments closed

When Altering a Table Blocks the Log Reader

Published 2020-08-20 by Kevin Feasel

Aaron Bertrand walks us through a painful scenario:

We recently performed a DDL operation against a SQL Server table – simply increasing the size of a varchar column – which should have been instantaneous. Instead, we killed the SQL Server process after observing 20 minutes of HARD_SYNC_COMMIT waits and a blocked replication log reader. Could this issue have been avoided? What went wrong?

I spotted the issue pretty quickly, but it’s easy to miss in a code review. Read the whole thing.

Comments closed

Using the Ring Buffer for Monitoring

Published 2020-08-20 by Kevin Feasel

Eitan Blumin uses Extended Events to track activity:

Extended events provides a solution similar to client side trace. It basically can capture all events that a trace can capture (and more), and it also supports a wider choice of target types. And that, is where its true power lies.

It just so happens that Extended Events has a target type called “ring buffer“, and it gives us exactly what we need.

The ring buffer is easy to set up and if you don’t need permanence, works great.

Comments closed