Press "Enter" to skip to content

Curated SQL Posts

When to Have Multiple Azure Data Factories

Published 2020-05-26 by Kevin Feasel

Paul Andrew explains how to become a factory mogul:

The obvious and easy reason for having multiple Data Factory’s could be that you simply want to separate your business processes. Maybe they all have separate data delivery requirements and it just makes management of data flows easier to handle. For example:

– Sales
– Finance
– HR

They could have different data delivery deadlines, they process on different schedules and don’t share any underlying connections.

You may also have multiple projects underway that mean you want to keep teams isolated.

But that’s not the only reason, so click through to learn several other reasons why you might have multiple Azure Data Factory instances running.

Comments closed

Reviewing SSMS Client Statistics

Published 2020-05-26 by Kevin Feasel

Reitse Eskens learns about SQL Server Management Studio’s client statistics:

In my case, i was looking for the amount of bytes received from server to determine the network speed. The number of rows is one thing, but i can’t easily tell if a row is 1 or 1000 kilobytes. By checking out the bytes received i can get some feel for the datasize. If there’s a huge amount of data coming towards me, that explains why i’ve got to wait for minutes. If there’s only a few kilobytes in the end, maybe something else is going wrong.

Reitse also takes some time to figure out how the client statistics tool works.

Comments closed

Blocking Inbound Connections to SQL Server

Published 2020-05-26 by Kevin Feasel

John Morehouse shows one quick way of preventing anybody else from connecting to your SQL Server instance:

We even tried to restart the instance into single user mode, however, every time that happened something else would take the connection before we could get into the instance.  We eventually restarted the SQL Server instance to normal operation so that we could investigate why we could not get a connection when in single user mode.

Turns out that with the production nature of the instance, the clients large farm of application servers was connecting to it faster than we could.   This was discovered by using sp_who2, however, you could use the DMV sys.dm_exec_connections to see what is connecting to the instance if you desired.  So, we needed a way to block incoming connections while not being evasive like shutting down the application servers or a large network change.

This is where the brilliance comes in.

Click through for the idea. This is the type of thing you keep in your back pocket in a real pinch, but hope never to need to use.

Comments closed

Using D3 to Visualize Data in Cube.js

Published 2020-05-22 by Kevin Feasel

Artyom Keydunov takes us through integrating D3.js within Cube.js:

You can check the online demo of this dashboard here and the complete source code of the example app is available on Github.

We are going to use Postgres to store our data. Cube.js will connect to it and act as a middleware between the database and the client, providing API, abstraction, caching, and a lot more. On the frontend, we’ll have React with Material UI and D3 for chart rendering. Below, you can find a schema of the whole architecture of the example app.

D3 is a powerful visualization library in Javascript, though I’ve found that it’s a complex visualization library.

Comments closed

Neural Networks and a Reproducibility Problem

Published 2020-05-22 by Kevin Feasel

William Vorhies looks at a recent paper on attempts at reproducing results from various types of neural networks:

Your trusted lead on recommenders rushes up with a new paper in hand.  Just back from the RecSys conference where the paper was presented he shows you the results.  It appears your top-N recommender could be made several percentage points better using the new technique.  The downside is that it would require you to adopt one of the new DNN collaborative filtering models which would be much more compute intensive and mean a deep reskilling dive for some of your team.

Would you be surprised to find out that the results in that paper are not reproducible?  Or more, that the baseline techniques to which it was compared to show improvements were not properly optimized.  And, if they had been, the much simpler techniques would be shown to be superior.

In a recent paper “Are We Really Making Much Progress”, researchers Maurizio Ferrari Dacrema, Paolo Cremonesi, Dietmar Jannach raise a major red flag.  Houston, we have a reproducibility problem.

Having worked through some of these papers for a different algorithm, I’m not that surprised. Sometimes it seems like improvements are limited solely to the data set and scenario the authors came up with, though that may just be the cynic in me.

This article is a good reason for looking at several types of models during the research phase, and even trying to keep several models up to date. It’s also a reminder that if you’re looking at papers and hot algorithms, make sure they include a way to get the data used in testing (and source code, if you can).

Comments closed

Planning a Power BI Enterprise Deployment: In Whitepaper Form

Published 2020-05-22 by Kevin Feasel

Melissa Coates has an updated whitepaper for us:

I’m really excited to announce that a new version of the Microsoft whitepaper “Planning a Power BI Enterprise Deployment” is now available.

This is version 3 of the whitepaper that I co-authored with Chris Webb. The previous version was from July 2018, so this update includes quite a lot of changes throughout.

Huge high-five to Meagan Longoria who was our tech reviewer again. She never fails to make my writing better.

That’s three very sharp people, so you can bet it’s going to be good.

Comments closed

Understanding Heaps in SQL Server

Published 2020-05-22 by Kevin Feasel

Uwe Ricken has a series on the much-maligned heap:

This article is the beginning of a series of articles about Heaps in Microsoft SQL Server. Heaps are rejected by many database developers using Microsoft SQL Server. The concerns about Heaps are even fuelled by Microsoft itself by generally recommending the use of clustered indexes for every table. Globally renowned SQL Server experts also generally advise that tables in Microsoft SQL Server be provided with a clustered index.

Again, and again, I try to convince developers that a heap can even have advantages. I have discussed many pros and cons with these people and would now like to break a “PRO HEAP” lance. This article deals with the basics. Important system objects that play a major role in Heaps are only superficially presented in this article and described in detail in a follow up article.

I’m generally in the anti-heap camp, but I can acknowledge that there are situations in which heaps are better—I save my dogmatism for other things, like hating pie charts and loving representations of things as event streams.

Comments closed

Understanding RID Lookups

Published 2020-05-22 by Kevin Feasel

Hugo Kornelis takes us through an operator I usually don’t want to see:

The RID Lookup operator offers the same logical functionality within the execution plan as the Key Lookup operator. But where Key Lookup is used for tables that have a clustered index, RID Lookup is instead used when a table is “heap” (table without clustered index). It is used when another operator (usually an Index Seek, sometimes an Index Scan, rarely a combination of two or more of these or other operators) is used to find rows that need to be processed, but the index used does not include all columns needed for the query. The RID Lookup operator is then used to fetch the remaining columns from the heap structure where the table data is stored.

Click through for a great deal of information about RID Lookups.

Comments closed