Press "Enter" to skip to content

Day: June 13, 2023

Table Clustering and Search Optimization in Snowflake

Kedhar Natekar continues a series on Snowflake performance:

Clustering does not guarantee improved performance on non-clustered columns. 

If you have frequent queries on non-clustered columns and performance is the key irrespective of cost, then opt for a search optimization service over the entire table of specific columns.

It’s similar to enabling indexing on RDBMS databases like Oracle on specific columns.

Read on to see how these work and get a few tips along the way.

Comments closed

Creating a Spark UDF

The Big Data in Real World team creates a Spark user-defined function in Scala:

In this post we are going to create a Spark UDF which converts temperature from Fahrenheit to Celsius.

Here is our data. We have day and temperature in Fahrenheit.

And, of course, it’s roughly the same in PySpark. Also, note that user-defined function performance will take a hit, and that answers are fairly consistent through the years, so save these for when you need them.

Comments closed

Calculating Moving Averages with DAX

Leila Etaati smooths the curve:

The calculation will be done for all rows, till we don’t have the anymore data ahead ( 3 rows ahead) as you can see it will finished on row 2018 as 2018 is the average of 2018, 2019 and 2020.

so in the moving average we will miss some data point but replacing with average of data.

Less data point but help us to see a clear trend.

Click through to see how to do this. Moving averages is really good for the reports in which you want to get a rough magnitude but don’t care about exact values and don’t want oscillations to throw you off. For example, if I need to know how many seats to reserve for an event, I might use a moving average of the last several events as my baseline, rather than the prior event’s number.

Comments closed

Creating a Microsoft Fabric Environment

Kevin Chant gets at it:

In reality, there are a few different ways to join the Microsoft Fabric (Preview) trial.

For example, you might be lucky enough to have it enabled in the workplace already. However, there are ways that you can create your own Microsoft Fabric environment as well.

Click through for the process, and note that the trial is 60 days, though Microsoft will let you renew the trial until the product goes GA.

Comments closed

Missing Index Hints and Index Rebuilds

Forrest McDaniel abuses that poor server:

The prod version of this issue manifested as 1-3 minutes of SCH-M blocking for index ops, yet only on a single server; I couldn’t replicate it anywhere else. Of course, bad blocking meant nasty timeouts, and that in turn meant I was reluctant to run live diagnostics. Better to just stop index rebuilds. One does not take down production in order to test weird SQL behavior.

An important clue came from a tiny table with a rebuild, and is visible in the whoisactive screenshot above. Why would such a small rebuild need so much CPU? That worker was definitely doing something – the question was what.

Read on to find out the answer, a repro script (that you should not run in your own production environment!), and what you can do about it.

Comments closed

SSMS 19.1 Updates

Erin Stellato has a two-parter around SSMS 19.1. The first includes a small but important change:

In SSMS 19.1, the default value for Use system default web browser was changed to True.  This only applies to new installations; anyone upgrading from an earlier version of SSMS 19 will need to change the option to True manually.

This means that, for new installations, clicking on a link in SSMS will no longer open in the built-in SSMS browser, which doesn’t work for a lot of newer sites.

Part 2 picks up with some additional changes:

We have heard complaints, for months, about the startup time for SSMS.  Between 18.12.1 and 19.0 we made a few changes, and in 19.1 we delayed initializing the output window to also reduce startup time.  Some of you noticed!  Glenn Berry completed a round of testing and documented findings in his SSMS 19.1 Performance Improvements blog post.  We are hoping to also gain improvement when we get to SSMS 20, so know that our work is not yet done.

Click through for the full set of chnages.