Press "Enter" to skip to content

Curated SQL Posts

Quick Tips for SQL Server Performance Troubleshooting

Published 2023-05-17 by Kevin Feasel

Matthew McGiffen has a two-parter for us. Part 1 covers useful tools for performance troubleshooters:

When you’ve got the symptoms of a database issue you can run a series of diagnostic queries to try and drill down on the problem and then start figuring out solutions. There are a number of free tools out there though that can speed up that process immensely. In this post we’re going to look at my favourites.

Part 2 takes us through the mechanics of measuring query performance:

Attendees have come up with a range of answers from “With a stopwatch” (which I like a lot) to the slightly more technical “Using Profiler”. I like the first answer just because that’s where we all start. We run something from an application and we literally time how long it takes, or we run something in SSMS and we use the counter near the bottom right of our query window to tell us how long it took. We may even run something 100 times in a loop and capture the overall time so we can take an average.

Incidentally, if you are using SET STATISTICS IO ON and are sick of the way it writes out results, Richie Rump’s Statistics Parser tool is great for converting the blob of text into something humans can easily parse.

Comments closed

Trying NTILE

Published 2023-05-17 by Kevin Feasel

Chad Callihan looks at the fourth ranking window function:

Have you ever used the NTILE function? Or have you even heard of the NTILE function? It seems to be one of the lesser known, lesser used window functions in SQL Server. I’ve never come across it in the wild but maybe there are those that use it all the time. Either way, let’s have a look at what it does and how it can be used.

Click through for a demo. I definitely use it a lot less than ROW_NUMBER(), RANK(), and DENSE_RANK(), but I have used it to some good effect in the past, mostly in cases where I’ve wanted to focus on the top X% of data for an analysis.

Comments closed

Dates and Times in R

Published 2023-05-16 by Kevin Feasel

Steven Sanderson talks dates and times. First up is an overview of how built-in date functionality works in R:

In this post, we will cover the basics of handling dates and times in R using the as.Date, as.POSIXct, and as.POSIXlt functions. We will use the example code below to explain each line in simple terms. Let’s get started!

The second post covers the Date type, as well as date plus time:

In the first part of our code, we use the as.Date() function to find the next Mother’s Day. Since we don’t need to consider specific times, we can simply use the Date class, which simplifies the process. We create a vector with two elements: startMothersDay and endMothersDay, both set to “2024-05-14”. This represents the range of Mother’s Day for the year 2024. Finally, we store the result in the variable NextMothersDay and print it to the console. Voilà! We have the next Mother’s Day date.

Note that this isn’t a post about date calculation, such as finding the Nth Sunday in the month of May

Comments closed

Elapsed Timers in Powershell

Published 2023-05-16 by Kevin Feasel

Robert Cain keeps track of time:

I’m still working on my documentation for my ArcaneBooks project, but wanted to have something for you to read this week, so decided to show you how to create an elapsed timer in PowerShell.

It can be helpful to determine how long a process runs in PowerShell. You can use it to determine what parts of code may need to be optimized, or gather metrics around your functions.

This is the same stopwatch operation which is available in .NET generally, so if you’re using C# or F#, it’s the same basic process.

Comments closed

Instant File Initialization for Log Files in SQL Server 2022

Published 2023-05-16 by Kevin Feasel

Aaron Bertrand tries something new:

When I first saw a bullet item stating SQL Server 2022 would support instant file initialization for log file growth, I was excited. When I later learned it only applies to automatic growths, and only those of 64 MB or less, I was a little less excited. With those limitations, I was skeptical this enhancement could supplant my long-standing practice of using 1 GB autogrowth for log files – at least ever since SSDs and other modern storage became more commonplace.

But after playing with it, I’m a believer.

I happened to be chatting with Thomas Grohser as he presented for our user group last week, and he also mentioned how good this feature is, as well as how VLF rules changed a bit so you don’t get as heavily penalized by growing in 64MB increments. Aaron does some testing to give us an idea of how much of a benefit you might achieve in making this change.

Comments closed

TDE and Database Backups

Published 2023-05-16 by Kevin Feasel

Matthew McGiffen shares some advice:

Database backups continue to work without change when you have TDE enabled. The only difference is that the backups contain encrypted data that cannot be read without the certificate and private key. There are a couple of points that are worth discussing though.

Click through for several notes, including a warning to those still on SQL Server 2016 and woefully under-patched.

Comments closed

Testing a Power BI Theme on Existing Reports

Published 2023-05-16 by Kevin Feasel

Erik Svensen does a wardrobe upgrade:

But I want to see how my new theme might look on some old report where I might have used another theme or perhaps some reports other users have created where they have done some individual formatting instead of using the theme.

And did you know – we can do it and see the result for existing report in your workspaces – and we can directly modify the theme and see if we should change a color or a font size or a….

Read on for the process. This is a clever use of the embedding service, that’s for sure.

Comments closed

Rounded Corners in Power BI Error Bars

Published 2023-05-16 by Kevin Feasel

Mara Pereira trims off the edges:

If you’ve been following my posts, you probably know by now that one of the things that really annoys me in Power BI is not being able to build bar charts with rounded corners, unless you use a custom visual.

However, recently I found out about a trick to make this happen without needing to use custom visuals (special shout out to Federico Pastor who shared this trick with me!).

Read on to see how you can do this. It’s definitely not ideal, but if you absolutely need rounded corners for your bar chart, you can get them this way.

Comments closed

Poisson Hidden Markov Models in SAS

Published 2023-05-15 by Kevin Feasel

Ji Shen shows off how to perform discrete time series in SAS:

The HMM procedure in SAS Viya supports hidden Markov models (HMMs) and other models embedded with HMM. PROC HMM supports finite HMM, Poisson HMM, Gaussian HMM, Gaussian mixture HMM, the regime-switching regression model, and the regime-switching autoregression model. This post introduces Poisson HMM, the latest addition to PROC HMM in the SAS Viya 2023.03 release.

Count time series is ill-suited for most traditional time series analysis techniques, which assume that the time series values are continuously distributed. This can present unique challenges for organizations that need to model and forecast them. As a popular discrete probability distribution to handle the count time series, the Poisson distribution or the mixed Poisson distribution might not always be suitable. This is because both assume that the events occur independently of each other and at a constant rate. In time series data, however, the occurrence of an event at one point in time might be related to the occurrence of an event at another point in time, and the rates at which events occur might vary over time.

HMM is a valuable tool that can handle overdispersion and serial dependence in the data. This makes it an effective solution for modeling and forecasting count time series. We will explain how the Poisson HMM can handle count time series by modeling different states by using distinct Poisson distributions while considering the probability of transitioning between them.

Read on for an overview of Hidden Markov Models (in general and the Poisson variation in particular) and some of the challenges you can run into when performing this test.

Comments closed

An Introduction to ggflowchart

Published 2023-05-15 by Kevin Feasel

Nicola Rennie shows off a new package:

Flowcharts can be a useful way to visualise complex processes. However, I couldn’t find an easy way to create a flowchart in R. There are a few packages for either drawing basic components of flowcharts (like {grid}), packages that are great for visualising complex network data where order doesn’t really matter (like {ggnetwork} and {igraph}), but none of them gave me the control over customisation I was used to with {ggplot2}.

{ggflowchart} tries to fill that gap. The aim of {ggflowchart} is to help R users make simple, good-looking flowcharts, with as little code as possible. It computes a layout, then uses existing {ggplot2} functions to stitch together rectangles, text, and arrows.

It does remind me a bit of Mermaid, though quite early in the process. H/T R-Bloggers.

Comments closed