Press "Enter" to skip to content

Day: July 22, 2020

The Basics of Randomized Response

Holger von Jouanne-Diedrich explains how randomized response can protect any single person’s opinion from a pollster while providing insight on the whole population:

So, is there a method to find the respective proportion of people without putting them on the spot? Actually, there is! If you want to learn about randomized response (and how to create flowcharts in R along the way) read on!

The question is how can you get a truthful result overall without being able to attribute a certain answer to any single individual. As it turns out, there is a very elegant and ingenious method, called randomized response. The big idea is to, as the name suggests, add noise to every answer without compromising the overall proportion too much, i.e. add noise to every answer so that it cancels out overall!

Click through for the process. It’s definitely a clever idea.

Comments closed

Neural Network Model Deployment with ONNX

Terry McCann gives us a primer on ONNX:

Let me introduce you to ONNX. ONNX or the Open Neural Network eXchange is a runtime which can take a model, that you have trained in PyTorch or Tensorflow and encapsulate it in an ONNX format which is executed on something running the ONNX runtime. This new model can be trained in Python and deployed on an application, with no need for integration coding.

We have spent a huge amount of time creating different Docker containers for different types of models, the Tensorflow container or the PyTorch container or a container running in our model in Spark, the list goes on. That way of working is kind of becoming defunct. ONNX really breaks that down into a simple standard runtime that you can start working with and you can deploy your model into multiple different environments and ensure that is runs on your database, on your website, on your mobile device and also at the edge.

Terry has a video as well. I like the fact that ONNX exists and also that it’s available in Azure SQL Edge (in part because I want it available on-premises as well).

Comments closed

Disk Caching with SQL Server VM Disks in Azure

Niko Neugebauer performs some tests:

Microsoft has been extremely clear in the best practices recommendation for the SQL Server workloads on Azure VMs:
– use read caching for the data drives/storage pools
– use no caching for the log drives/storage pools
– use read caching for the temp db drives/storage pools

Sounds simple and direct, isn’t it ?
Let me borrow your attention for the next couple of minutes pointing to some situations where you might want to reconsider the best practices.

But do read on for some important notes.

Comments closed

Python in Power BI Desktop

David Eldersveld dives into using Python as an external tool in Power BI:

Why use Python as an external “tool”? Even though Python isn’t a “tool” in the same sense as the “Big 3” community tools focused this month, I want to show how versatile the External Tools feature is. I also want to encourage people to use imagination and also explore how Power BI isn’t really as closed as some people think–at least the data model…

Some of these ideas are not exclusive to Python, but there’s enough variety in the Power BI and data science communities for people to possibly figure out if some of this might be useful within the context of their own environments, skills, and organizations.

David also follows up with a series of sample ideas.

Comments closed

Finding and Downloading SQL Server Updates

Andy Levy combines dbatools and KBUpdate:

Another of Chrissy LeMaire’s (blog | twitter) projects is KBUpdate. Compared to dbatools it’s a pretty compact module, but it’s incredibly useful – it’ll seek out information about KB updates and even download them for you! She’s also rolled these functions into dbatools for convenience, so we don’t need to install or import that module separately.

Read on to see how Andy ties it all together.

Comments closed

Making a Heap Molehill out of a Heap Mountain

Bert Wagner needed to move a large subset of a heap into its own heap:

Recently I had to filter out 1.2 billion records from a 3.5 billion row heap. Don’t ask me why this 3.5 billion row table is stored as a heap.

If the lack of a clustered index wasn’t bad enough, I also had some other restrictions:

I couldn’t add a clustered index (or any index for that matter) sorted on the key I needed to filter on. It wasn’t my system, and and I needed to access the 1.2 billion records sooner than it would take to get a clustered index approved and added. Even then, maybe the lack of a clustered index on this table is a feature and my request would be denied. Who knows.
I didn’t have a server that could store all of the data. At first I thought of copying all 3.5 billion rows to my own server and indexing it how I needed, but I didn’t have enough storage space anywhere to do that.
My connection to the server had a relatively short timeout set on it. This also couldn’t be changed. If I couldn’t copy all 3.5 billion rows because of storage, I also couldn’t copy all 1.2 billion records in one fell swoop because the connection would timeout.

I’m also glad to see that Bert is back in action and look forward to seeing these posts and videos pop up again.

Comments closed

Latching in SQL Server

Dan Jackson explains the concept of latching in SQL Server:

To start with, a basic definition: ‘Latches are lightweight synchronization objects, that are used by the storage engine of SQL Server to protect the internal memory structures’. Compare this with locks in SQL server, which are a transaction level construct to manage concurrency, latches work at the thread level to maintain data integrity within the internal memory structures. They are not exposed outside of the SQL Server Operating System (SQLOS). They are only managed by SQL Server itself, not by users (unlike locks that can be overridden via lock hints or changing isolation level). It is useful to keep in mind that a single transaction can use multiple threads at the same time.

Latching is a funny concept to me, in that I think people say “This must be a latching problem” far more than there actually is, but when there is a proper latching problem, it usually winds up being a pretty big deal.

Comments closed