Press "Enter" to skip to content

Author: Kevin Feasel

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

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

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

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

Ten Comments from a DBA

Published 2020-05-22 by Kevin Feasel

Kevin Chant spins the DBA archetype around:

2. So glad we test our backups.

Now this is something that every DBA should do, or at least persuade whoever is responsible for backups to do it.

Otherwise you may find yourself in a situation where a database is corrupt and a restore is not possible. Which means that you have to try and recover the database using other methods like the one here.

I can tell you from experience that this is definitely not the best situation to be in unless you enjoy working for over twenty-four hours straight. So, if your backups are not being tested at the moment then I highly recommend you change that.

And if you are a DBA who can’t say this, Kevin has some advice for each of the ten.

Comments closed

Data Platform Announcements from Build

Published 2020-05-22 by Kevin Feasel

James Serra looks at some announcements from the Build conference:

A few data platform announcements yesterday at Microsoft Build that I wanted to blog about.

The biggest one is Azure Synapse Analytics is now available in public preview! You can immediately log into your Azure portal and use it. While in the Azure portal, search for “Synapse” and you will see “Azure Synapse Analytics (workspaces preview)”. Choose that and then click “Create Synapse workspace” (you first may need to register the resource provider “Microsoft.Synapse” in your subscription – see Azure resource providers and types).

James also covers other highlights, including Cosmos DB and Azure SQL Database Edge.

Comments closed

dbatools Commands for Performance Tuning

Published 2020-05-22 by Kevin Feasel

John McCormack takes a look at dbatools with an eye on performance tuning:

DBATools is well known in the SQL Server community for the ease at which it allows you to automate certain tasks but did you know that DBATools can help with performance tuning your SQL Server. As my job recently changed to have more of a performance tilt, I wanted to investigate which commands would be useful in helping me with performance tuning. It turned out there are quite a few.

There are some good commands in here.

Comments closed