Author: Kevin Feasel

The steps to create a Bi-directional replication is simple, and similar to the steps for configuring transnational replication with extra step to enable the @loopback_detection parameter of sp_addsubscription to ensure that changes are only sent to the Subscriber and do not result in the change being sent back to the Publisher.
The most common issue for the Bi-directional replication is when the loop back detection is not working as expected; which results in data conflicts and Primary Key Violations.

Read on to see what is the cause of this problem and what you can do to solve it.

Leave a Comment

The DIFFERENCE() and SOUNDEX() Functions

Published 2022-01-20 by Kevin Feasel

Hadi Fadlallah looks at two methods of string distance:

Soundex is a phonetic algorithm developed by Robert C. Russell and Margaret King Odell in the early 1900s. This algorithm is used to index names as they are pronounced in English. The main goal of such an algorithm is to encode homophones to the same representation to be matched even if there are some slight spelling differences. As an example, consider the names “Smith” and “Smyth”, or “Mohamad” and “Mouhammad”. Soundex mainly encodes consonants and only encodes a vowel if it is the first letter of the name.
Being one of the most popular phonetic algorithms, Soundex was implemented in multiple database engines such as Oracle, SQL Server, MySQL, SQLite, and PostgreSQL.

These two methods are not perfect and they do really limit you to one word (or small word grouping), but they are useful.

Leave a Comment

Diving into Vertipaq Compression

Published 2022-01-20 by Kevin Feasel

Ed Pollack explains how Vertipaq compression works to make columnstore indexes so efficient:

Columnstore compression is an impressive array of algorithms that can take large analytic tables and significantly reduce their storage footprint. In doing so, IO is also reduced, and query performance dramatically improved.
This article dives into one aspect of columnstore compression that tends to get buried in all of the hoopla surrounding how awesome columnstore indexes are: Vertipaq optimization. This is a critical component of the columnstore compression process, and understanding how it works can significantly improve the performance of analytic workloads while reducing the computing resources required for the underlying data.

Click through for the steps of the process.

Leave a Comment

Page and Bookmark Navigation in Power BI

Published 2022-01-20 by Kevin Feasel

Kristi Cantor notes a Power BI update:

Hello P3 Adaptive Nation! Happy New Year, ring out the old and ring in the new! Speaking of ringing in the new, with all the hustle and bustle associated with the holiday season and the excitement of welcoming the new year, did anybody happen to notice the new feature quietly rolled out in Power Bi back in November to take the edge off creating and maintaining custom pages and bookmarks?

Read on to see what has changed.

Leave a Comment

Measuring Cost Threshold for Parallelism Effectiveness

Published 2022-01-20 by Kevin Feasel

Michael Swart gets out the ruler:

The configuration setting cost threshold for parallelism has a default value of 5. As a default value, it’s probably too low and should be raised. But what benefit are we hoping for? And how can we measure it?
The database that I work with is a busy OLTP system with lots of very frequent, very inexpensive queries and so I don’t like to see any query that needs to go parallel.
What I’d like to do is raise the configuration cost threshold to something larger and look at the queries that have gone from multi-threaded to single-threaded. I want to see that these queries become cheaper on average. By cheaper I mean consume less cpu. I expect the average duration of these queries to increase.

Read on for Michael’s results, and I appreciate somebody actually testing and measuring rather than pulling a number from a hat.

Leave a Comment

Using Query Hints in Query Store

Published 2022-01-20 by Kevin Feasel

Deepthi Goguri takes us through hint usage in Query Store:

Query Store hints is another amazing feature added and is available in the preview mode in Azure SQL Database, managed instances, elastic pools and hyperscale databases as well. Query Store hints are just like the hints you use in your queries but the difference is you do not have to change the query code to apply these hints using Query store specifically designed stored procedure- sys.sp_query_store_set_hints. Cool, right?

Yes. Yes it is.

Leave a Comment

Profiling Python Code

Published 2022-01-19 by Kevin Feasel

Adrian Tam shows how you can test the performance of calls in Python:

Profiling is a technique to figure out how time is spent in a program. With this statistics, we can find the “hot spot” of a program and think about ways of improvement. Sometimes, hot spot in unexpected location may hint a bug in the program as well.
In this tutorial, we will see how we can use the profiling facility in Python. Specifically, you will see
– How we can compare small code fragments using timeit module
– How we can profile the entire program using cProfile module
– How we can invoke a profiler inside an existing program
– What the profiler cannot do

Read on for those techniques.

Leave a Comment

Finding your SQL Server Product Key

Published 2022-01-19 by Kevin Feasel

I share a tale of woe:

I was working on an upgrade recently, trying to move from SQL Server 2016 to SQL Server 2019. I wanted to perform an upgrade in-place on an Azure VM, but needed to get the product key. There are a few places where you can find Powershell functions to get this product key, with Ryan @ Ryadel’s post being the most well-known. That method covers versions from SQL Server 2005 through 2014 (if you follow the notes in the blog post), but breaks on 2016.

Click through for a script which works for SQL Server 2016 and later.

Leave a Comment

Static versus Dynamic Partitioning in Hive

Published 2022-01-19 by Kevin Feasel

The Hadoop in Real World team explains the difference between two partitioning strategies:

The difference between static and dynamic partitioning only exists when the partition is being created based on how the partitions are added to the table. Once the partitions are created, the tables won’t have any difference like static and dynamic partitions. All partitions are treated and one and the same.

Click through for the difference.

Leave a Comment

Improving Managed Instance Load Performance

Published 2022-01-19 by Kevin Feasel

Niko Neugebauer provides some tips on improving data load performance when using SQL Managed Instances in the General Purpose tier:

In this blog post we shall consider some of the strategies for improving data loading performance in Azure SQL Managed Instance. These strategies apply to the repeatable ETL processes, meaning that if there a problem, data loading process can be repeated without loss of any bit of data or its consistency. Of course, these strategies do require a prepared design for the possibility of the repetition, but this is a basic requirement for any data loading process.
There are many great ways of ensuring high performance data loading and this blog post does not pretend to be exhaustive an ultimate resource, but it will provide a couple of known paths that can drastically improve the performance of the Log Throughput in Azure SQL MI.

Click through for some tips, including some which make sense on-prem and a couple which are specific to platform-as-a-service offerings.

Leave a Comment