Press "Enter" to skip to content

Month: August 2021

Filtered Indexes in SQL Server

Published 2021-08-17 by Kevin Feasel

Esat Erkec takes us through a frustratingly near-useful feature in SQL Server:

Indexes are the special data structures that help to improve the performance of the queries in SQL Server. Against this great benefit of the indexes, they occupy space on hard drives and can slow down the data modification operations (update, insert, delete) performance. When any query modifies the data in a table the database engine needs to update all of the related indexes where data has changed. In certain instances, to minimize these disadvantages of indexes, using SQL Server filtered indexes might be the appropriate approach. Assume that, we frequently query a small subset of a table with the same conditions and the rest of the table contains too many rows. In this scenario, we can use a SQL Server filtered index to access this small data set faster so that we can reduce storage and index maintenance costs.

When you can get them to work, such as when the part of your query which is supposed to access that index is not parameterized, filtered indexes are great. But Esat does get into what happens when that condition doesn’t quite hold.

Comments closed

Working around Unparameterized IN Clauses with EF

Published 2021-08-17 by Kevin Feasel

Erik Darling bears the bad news:

If you’re using an Entity Framework, and sending in queries that build up IN clauses, they won’t end up getting parameterized.

Even Forced Parameterization won’t help you if you’re sending in other parameters. One limitation is that it doesn’t kick in for partially parameterized queries.

Even if they did get parameterized, well, what’s one funny thing about IN clauses? You don’t know how many values will be in them.

Read on for a couple of work-arounds for this.

Comments closed

Creating Functions in Kusto Queries

Published 2021-08-17 by Kevin Feasel

Dennes Torres continues a series on working with the Kusto language:

In the previous blog, I illustrated how to create sub-queries in Kusto.

However, sometimes we may face even more complex situations and we may need to create not only a sub-query, but a function.

Another way to think about a function inside a Kusto query is like a parameterized sub-query.

Read on for an example of creating a Kusto function.

Comments closed

Using Power BI Goals to Drive OKRs

Published 2021-08-17 by Kevin Feasel

Treb Gatte has an interesting use case for Power BI Goals:

OKRs are but one of three emergent usage scenarios for Power BI Goals. I see some companies using Goals for Metric Aggregation and some for Balanced Scorecards. This post focuses on OKRs as I see the scenario offering broader benefits to the entire organization. OKRs are critical to creating an Action-centric culture.

Read on to understand what an OKR is and how Power BI Goals can help with tracking them.

Comments closed

Fixing Hadoop Namenodes in Safe Mode

Published 2021-08-16 by Kevin Feasel

The Hadoop in Real World team doesn’t need to play it safe:

When namenode is started or restarted, namenode will be in safemode for a period of time. At this time you will not be able to use your Hadoop cluster fully. Write operations to HDFS will fail and because of that your MapReduce jobs will also fail.

Read on for other reasons why the namenode might be stuck in safe mode and what you can do to fix it.

Comments closed

Fun with GO and Preventing SQL Batches from Parsing

Published 2021-08-16 by Kevin Feasel

Solomon Rutzky has an apropos post for Friday the 13th:

In a previous post, Prevent Full Script Execution (Understanding and Using PARSEONLY and NOEXEC), I explained how to easily disable a script using the PARSEONLY session setting. That same method can be used to disable one or more sections within a script instead of the entire script. But in either case, “disabling” doesn’t mean that the script, or section of code, will be skipped entirely as if it wasn’t there. It will still be parsed by SQL Server as that is not something that can be turned off. This means that you could still see parsing errors related to undeclared variables, syntax errors, etc.

Then one day I tried something silly that I figured wouldn’t work but wanted to try anyway (because quite often you don’t know until you know), and it actually did work (for the most part). I found a way to fully disable an entire T-SQL batch, and there really isn’t any indication that it happened. However, this “technique” is more limited than PARSEONLY as it only works on individual batches, and it only works in some environments.

Read on to see how you can use the GO operator to prevent many SQL Server client tools from even noticing a block of text.

Comments closed

Migrating a Replicated Database without Reinitializing

Published 2021-08-16 by Kevin Feasel

Jonathan Kehayias provides some tips on migrating (transactional) replicated databases when the cost of reinitializing is just too high:

The first part of building any plan is to know what specifically is being migrated or upgraded. This is important because the steps will be slightly different if you are only migrating or upgrading one part of the environment vs. the entire environment. It is also important to know where the distributor is for the configuration and whether or not the distribution database is going to be affected by the changes being made. In most cases it is a full environment migration to newer hardware or upgrade to newer release of SQL Server and the steps below will work. This is not the only way of migrating/upgrading large replicated databases but this is the one I have used for over 15 years at the date this post was written and it has been flawless.

Read on for more tips and a step-by-step process. With merge replication, of course, things are 40% more difficult.

Comments closed

The Cost of Measures in Power BI Live Connection Reports

Published 2021-08-16 by Kevin Feasel

Chris Webb explains the cost side of the ledger when it comes to measure creation:

You probably know that it’s a best practice to build your Power BI datasets in a separate .pbix file from your reports – among other things it means that different people can develop the dataset and reports. You may also know that if you are building a report in Power BI Desktop with a Live connection to a published dataset or Azure Analysis Services you can define your own measures inside the report. While this is very convenient, if you create too many measures there’s a price to pay in terms of query performance.

Click through for a demonstration of this.

Comments closed

Using the DAC from SSMS

Published 2021-08-16 by Kevin Feasel

Chad Callihan shows how you can configure the dedicated administrator connection and connect to it via SSMS in a time of need:

Have you heard of SQL Server’s dedicated administrator connection? The dedicated administrator connection (DAC) can come in handy in an emergency scenario so you should have it enabled and know how to use it…just in case. I haven’t needed it too often in my career but it was helpful in instances when SQL Server wasn’t being very responsive. If there are problems connecting to a server, the DAC can be used to connect and troubleshoot issues.

Read on to learn more.

Comments closed

Reviewing Azure Purview Data Catalog Features

Published 2021-08-16 by Kevin Feasel

Angela Henry continues a series on Azure Purview:

The Data Catalog portion of Purview is where most people will spend their time. It provides the information about your organizations data assets in a searchable format. Depending on which level of Data Catalog you choose; you can also access a business glossary, lineage visualization, catalog insights, and sensitive data identification insights. This article will focus on the three different levels available within Data Catalog and offers scenarios demonstrating when you would use each offering.

Read on for the three tiers, all of which are currently free but that won’t stay the case.

Comments closed