Press "Enter" to skip to content

Author: Kevin Feasel

Indexing and Parameter Sensitive Plan Optimization

Published 2022-07-07 by Kevin Feasel

Erik Darling continues a series on Parameter Sensitive Plan optimization. First up is a post on indexing:

Anyway, let’s use the example that I had started with here, to illustrate that the PSP optimization does work with a computed column, but… like any other column, indexes make all the difference.

I’m using the same example query over and over again, because a lot of the other great examples of parameter sensitivity that I have demo queries written for don’t seem to trigger it.

And sometimes there’s just nothing to do:

After seeing places where the Parameter Sensitive Plan (PSP) optimization quite stubbornly refuses to kick in, it’s somewhat amusing to see it kick in where it can’t possibly have any positive impact.

Even though some parameters are responsible for filtering on columns with highly skewed data, certain other factors may be present that don’t allow for the type of plan quality issues you might run into under normal parameter sensitivity scenarios:

This continues to be a very interesting look into one of the most-anticipated features in SQL Server 2022, as well as a necessary wet blanket for the hype.

Comments closed

Finding Unique Key Violations with Extended Events

Published 2022-07-06 by Kevin Feasel

Grant Fritchey points out another use for extended events:

Most of the time when I talk about or demo Extended Events, I spend more time talking about query tuning (I have a problem). However, there are tons of things that you can do with Extended Events. Here’s a little one that came up, auditing unique constraint violations.

It can also handle most other types of errors, making this a robust way of tracking issues. Back in the 2008 days, I built a little WPF program to watch for all of the errors on the couple of production SQL Server instances I managed. At one point, I saw one of the devs trying to write a query and getting an error. I IM’d the dev and said “You forgot the GROUP BY clause” (or whatever the problem was—it was something minor like that). He came over with a bit of panicked excitement, trying to figure out how exactly I was able to see what he wrote given that I was nowhere near his cube. Good times.

Comments closed

Finding and Documenting SQL Server Instances

Published 2022-07-06 by Kevin Feasel

Tracy Boggiano continues a series on things to do at a new job as a DBA:

In my previous post, I expounded on my first 30 days I had at four jobs in the last four years. and how to setup your jobs box. I commented and got quoted on the fact that if it’s documented I don’t support it. So, these are methods of getting things documented, some including just having to have meetings, others running code.

One I believe in having a Central Management Server (CMS) where you can register your servers. Put them in as many groups as you desire but have core group such as Dev, Test, QA, UAT, Prod, Prod Sec, etc. The rest could be by application name if needed. I always have a set of names that are for the DBAs to use to do our work, other teams can have theirs for their work, i.e., deploying code.

If you aren’t using dbatools yet you should be. While not every shop can use to manage everything it is works every well for most tasks and that includes scanning the network for SQL Instances. Because unless you could into a well oiled machine there will be instances they don’t know about and one day someone will come knocking asking to fix it. Warn your security team before you run this.

Read on for examples of how you can find instances (assuming the security team is okay with it!), some of the information you’d want to document, and more. I would also recommend the most recent episode of the SQL Data Partners podcast, in which we talk to Jen and Sean McCown about documenting and managing your SQL Server inventory.

Comments closed

The Performance Pain of User-Defined Functions

Published 2022-07-06 by Kevin Feasel

Tom Zika continues a series on why user-defined scalar functions are such a bad idea:

I’ll cover several test scenarios and analyze the performance using different monitoring tools.
The results will be for the second executions of the queries, so we have compiled and cached plans and all pages in the buffer pool.

Even if you already hate seeing scalar user-defined functions in code, the occasional reminder of how poorly they perform helps focus the mind.

Comments closed

Searching Database Metadata in SQL Server

Published 2022-07-06 by Kevin Feasel

Phil Factor has a lookup function:

Finding things in databases isn’t always straightforward. You might think that you can just search the database objects in SQL Server. No, because user types, for example are stored elsewhere, despite being schema-bound. You might want to search in the scheduled jobs too, but there is no way of working out whether the code in a job step relates to a particular database. You might think that the information_schema represented a consistent industry standard, providing a rational logical layer over the seething reality underneath. You might be wrong.

Click through for the script and explanation.

Comments closed

Object-Level Security in Power BI and Analysis Services

Published 2022-07-06 by Kevin Feasel

Marco Russo and Alberto Ferrari take advantage of object-level security:

To be more technical, object-level security can be applied to tables and columns, but it cannot be applied to measures directly. If a measure – whether in a direct or indirect way – references a column or a table that is not accessible under the current security context, the measure becomes invisible as well. This way, it is guaranteed that if a column must be invisible to a group of users, its content cannot be inferred by looking at the result of measures based on said column.

Concretely, there may be cases where a measure should be hidden from a group of users without removing the visibility of existing data structures. Let us look at a simple example first. We define the Sales Amount measure as the product of Sales[Quantity] by Sales[Price]. You also have a Discounted Sales measure that applies a set discount Sales Amount; now how can you hide Discounted Sales from a group of users without hiding the initial Sales Amount measure? By hiding either Sales[Quantity] or Sales[Price], you would hide both measures. Because the discount is set inside the Discounted Sales measure and not stored in the model, it looks as though you cannot hide just the measure. However, it we create a dependency in Discounted Sales on an empty hidden table specifically created to generate that dependency, we can hide Discounted Sales by hiding that table.

Read on to see how.

Comments closed

Measuring Power Query CPU Utilization

Published 2022-07-06 by Kevin Feasel

Chris Webb does a refresh and tracks the damage done:

Some time ago I wrote a post about how optimising for CPU Time is almost as important as optimising for Duration in Power BI, especially if you’re working with Power BI Premium Gen2. This is fairly straightforward if you’re optimising DAX queries or optimising Analysis Services engine-related activity for refreshes. But what about Power Query-related activity? You may have a small dataset but if you’re doing a lot of complex transformations in Power Query that could end up using a lot of CPU, even once the CPU smoothing for background activity that happens with Premium Gen2 has happened. How can you measure how expensive your Power Query queries are in terms of CPU? In this post I’ll show you how.

Read on to see how you can see how much CPU is required to do that dataset refresh.

Comments closed

Summarizing Data & AI Summit Announcements

Published 2022-07-05 by Kevin Feasel

Zach Stagers hits the high notes:

One of the biggest cheers of the keynote was that Delta is being fully open sourced! Databricks continue to share their incredible work to help drive our industry forward. Delta already has wide adoption, but with the open sourced version now being levelled up to the same standard as the ‘proprietary’ one, this should help cement it as the default choice for lake-based storage.

There were some announcements of things to come with Delta too, such as a optimised deletes and updates by removing single rows instead of having to completely rewrite the file. It’ll be really interesting to see how this works, and just how much it boosts performance.

Read on for more notes on several big announcements.

Comments closed

Building a “Solution Summary” Card for Developers

Published 2022-07-05 by Kevin Feasel

Ben Brown gets the new developers up to speed:

Solution Summary cards are the name I have given to simple 1-page documents we provide to developers to get up-to-speed on a particular solution.

The primary use-case for these cards is to provide better on-boarding for new team members.

An additional benefit is that crafting these short summaries forces people to review how your teams are working and you will often start seeing areas which would benefit from standardization or simplification.   

Click through for an example. I like this idea a lot for a few reasons, which I’ll enumerate here because I’m feeling it:

  1. Developers often won’t read really long documents. They might scan through the documents, Ctrl+F a few words, and refer back to it on occasion but rarely will they read through a document in its entirety.
  2. Long documents get out of date too quickly, as developers are typically bad at keeping documentation up to date, especially as deadlines loom.
  3. An artificial constraint like “Your summary must fit on one printed page” (either front-only or front-and-back) helps focus the mind. It makes you think about what the most important things to include are.
  4. If you are a consultant or just work on a lot of different projects, it can be painful trying to remember the details. Which jump box do you need to go to? Which were the most important bits of code? How do you run the unit tests and deploy the app locally? Even if nobody else ever sees the card, it can help you in the future.
Comments closed

Azure Data Explorer Web Updates

Published 2022-07-05 by Kevin Feasel

Michal Bar has a few updates to the Azure Data Explorer web tool:

We are focused on continuously improving the results exploration experience in ADX web UI, to make it easy and intuitive. Our goal is to provide an easy-to-use UI so that you will not be required to re-write KQL queries in order to perform light-weight data exploration.

Click through to see how you can search and filter within the results pane (something I’d like to see in other Microsoft data platform tools like SSMS), create series panels on charts from KQL, and more.

Comments closed