Press "Enter" to skip to content

Day: January 23, 2024

Bootstrapping in TidyDensity

Published 2024-01-23 by Kevin Feasel

Steven Sanderson pulls us up by the bootstraps:

Imagine this: You have a dataset, say, car mileage (MPG) from the classic mtcars dataset. You want to understand the average MPG, but what if that average is just a mirage? What if it’s skewed by a few outliers or doesn’t capture the full story?

Enter bootstrapping, a statistical technique that’s like taking your data on a wild ride. It creates multiple copies of your data, each with a slight twist, and then calculates the statistic you’re interested in (e.g., average MPG) for each copy. This gives you a distribution of possible averages, revealing the variability and potential biases lurking beneath the surface.

Read on to learn more about bootstrapping in general and how to use the bootstrap_stat_plot() function in TidyDensity.

Comments closed

Preventing ASYNC_NETWORK_IO Waits in SQL Server

Published 2024-01-23 by Kevin Feasel

Vlad Drumea troubleshoots a pernicious wait type:

In this post I’ll go over what the ASYNC_NETWORK_IO wait type is, when it occurs, and how you can tell if the application is causing it.

Lately I’ve had to troubleshoot a few situations where the issue seemed like a poor performing query, but the apparent slowdown was due to applications causing excessive ASYNC_NETWORK_IO waits in SQL Server.

This particular wait doesn’t always happen because of slow apps, though that’s often the case. Vlad breaks out several reasons why you might see the wait and explains what you can do to fix the problem.

Comments closed

Changing the Width of Cells in Jupyter Notebooks

Published 2024-01-23 by Kevin Feasel

Brendan Tierney blows out the margins:

When working with Jupyter Notebook you might notice the cell width can vary from time to time, and mostly when you use different screens, with different resolutions.

This can make your code appear slightly odd on the screen with only a certain amount being used. You can of into the default settings to change the sizing, but this might not suit in most cases.

It would be good to be able to adjust this dynamically. In such a situation, you can use one of the following options.

Read on for two different ways of doing this.

Comments closed

Query Store in SQL Server 2022

Published 2024-01-23 by Kevin Feasel

Deepthi Goguri fills us in on where Query Store has gone as of SQL Server 2022:

In this post, you are going to learn about the advancements of the Query Store released in SQL Server 2022. This is the first blog post for this series. You can expect more posts in the coming weeks related to the Query store usage in the Intelligent Query processing features released in SQL Server 2022.

Click through for that overview.

Comments closed

The Tradeoffs of Azure SQL Managed Instance General Purpose

Published 2024-01-23 by Kevin Feasel

Kendra Little provides some guidance:

Whether or not you use Azure SQL Managed Instance, you will likely be asked for an opinion on it eventually if you’re a SQL Server person.

While the architecture is documented, it can be a bit of a long read and some of the gotchas are spread out over different pages – so I’m drawing up the architecture of each service tier along with notable implications for the design on performance and cost. Here’s the scoop on General Purpose.

Click through for Kendra’s thoughts.

Comments closed

Using Application Locking to Prevent Deadlocking

Published 2024-01-23 by Kevin Feasel

Andy Brownsword shares a tip:

Deadlocks are an enduring feature of SQL Server. They’ve been a source of pain for many over the years and there are various ways to diagnose, mitigate or resolve them. Here I want to demonstrate approach I haven’t seen discussed – using an application lock to segregate processes.

An example where I’ve used this effectively was for a queue table where work would be placed and a number of processes would nibble away at it throughout the day. Separate to this was a maintenance routine which ran each evening to manage partitions on the table. The maintenance job would kick in and would deadlock all those processes.

This was solved with an Application Lock.

Read on to learn more about application locks, how they work, and why they fixed the problem in this scenario. Generally, this happens when two operators access two resources in different orders. For example stored procedure A has a transaction which locks table 1 and then locks table 2 and then commits. Meanwhile, stored procedure B has a transaction which locks table 2 and then locks table 1 and then commits. The classical solution is to fix the ordering such that both are consistent. But not all deadlocking behavior is that straightforward or that simple, and so other solutions like app locks can be quite helpful to know about.

Comments closed