Press "Enter" to skip to content

Curated SQL Posts

Analysis Of A Failed Project

Published 2018-10-11 by Kevin Feasel

Eugene Meidinger looks back at a big project which fell apart:

So the first issue was that the software was built in-house by another company in the same industry. Imagine, for example, if a large bakery had created an ERP system and another large bakery wanted to move to that system. Sounds great, right? Well, you run into two issues in that scenario.

First, a bakery is not an independent software vendor. Programming, by definition, is not their core competency. Which means that you may run into fragility or issues that you wouldn’t run into with a commercial piece of software. It also means that there isn’t going to be any documentation on migrating to the software or implementing it. Why would there be. If you built software for one company, why would you create scaffolding to move other companies onto it?

Second, not every business is the same. A lot of the fundamentals are the same, but you will run into many edge cases. We do invoices this way. They do workorders this way. We handle purchase orders this way. They handle inventory that way.

The way that I think about it is like a sea shell. It’s this intricate curve that’s grown over time, organically, to fit that creature. If you just try to fit a different snail or mollusk in that shell, it may not work out.

Read the whole thing.

Comments closed

Embedding Images In Power BI

Published 2018-10-11 by Kevin Feasel

Zach Conroe shows how you can embed an image in Power BI:

The good news is that there are workarounds to this challenge. We are going to reconstruct the above use case and demonstrate how to pull in images from a local database, and then use custom columns in Power Query to reformat the source data in a way we can render graphically.

Note: This same custom column technique can also be used to display images imported using a local folder as a data source. If you have Power BI Desktop installed, you can work through along with this post by downloading the .pbix file with this link.

In our sample database we have a couple of tables containing images stored in a binary format, as well as a few columns of metadata for the images. The images being used here are a JPEG file type, but this technique can also be used for PNG files. We imported the data into Power BI and loaded two tables of images: Examples 1 and 2. For the first example we used three small images in the table, as shown below.

There’s a 32K size limit that Zach mentions, which can be a bit painful to work within.

Comments closed

Risks Of Using Resource Governor To Set Max DOP

Published 2018-10-11 by Kevin Feasel

Joe Obbish builds an example where Resource Governor’s CPU cap can actively harm query performance:

I uploaded the query plan here if you want to look at it. This type of scenario can happen even without Resource Governor. For example, a compiled parallel query may be downgraded all the way to MAXDOP 1 if it can’t get enough parallel threads.

The query performs significantly worse than before, which hopefully is not a surprise. A single execution took 12860 ms of CPU time and 13078 ms of elapsed time. Nearly all of the query’s time is spent on the hash join for the index intersection, with a tempdb spill and the processing of additional rows both playing a role. The tempdb spill occurs because SQL Server expected the build side of the hash join to be reduced to 1213170 rows. The bitmap filtering does not occur so 8 million rows were sent to the build side instead.

Read the whole thing.

Comments closed

SQL Undercover Inspector 1.2 Released

Published 2018-10-11 by Kevin Feasel

Adrian Buckman announces a new version of SQL Undercover Inspector:

#21 Added AG Databases check to warn on databases not joined to an AG 

If you are using Availability groups and you have this new module enabled the Inspector will assume that all databases should be joined to an AG, every database name for the instance is inserted into a new table called [Inspector].[AGDatabases] and the Is_AG flag is set to a 1 , if databases are joined to an AG then the Is_AGJoined column is set to a 1 therefore no Advisory will be shown on the report. If a database is marked as Is_AG then it will continue to warn if not joined to an AG on the Inspector reports, if you wish to exclude a given database from the advisory condition simply update Is_AG to a 0 .

Instances that are not Hadr enabled with at least one AG will automatically have Is_AG set to 0 and will be excluded from the checking even if the module is enabled.

Click through for a big list of changes.

Comments closed

Multi-Threaded R With Microsoft R Client

Published 2018-10-10 by Kevin Feasel

David Parr shows us how to get started with Microsoft R Client and performs some quick benchmarking:

This message will pop up, and it’s worth noting as it’s got some information in it that you might need to think about:

  • It’s worth noting that right now Microsoft r Client is lagging behind the current R version, and is based on version 3.4 of R, not 3.5. This will mean your default package libraries will not be shared between the installations if you are running R 3.5.

  • It’s using a snapshot of CRAN called MRAN to source packages by default. 90% of the time it will operate just as you expect, but because it takes a ‘snapshot’ of packages, newer features and changes that have hit CRAN may not be in the version of the package you are grabbing.

    • RevoScaleR and probably the ggplot2 and dplyr packages will likely be installed for you already as default in Microsoft R Client. The other two you will probably have to install yourself.

  • Intel MKL will have scanned your system on install and attempted to work out how many cores your processor has. Here it’s identified 2 on my old Lenovo Yoga. This is where the speed boost will come from.

I had an old two-core Lenovo Yoga too, so this article really spoke to me.

Comments closed

Using Biml To Read Excel Files Without Excel

Published 2018-10-10 by Kevin Feasel

Bill Fellows follows up on his prior post and shows how you can write BimlScript to parse an Excel file without having Microsoft Office installed:

My resources are quite simple: Excel Spreadsheet containing meta data, a driver program and a package template.

The template is your standard truncate and reload pattern with the target table being specified by a parameter. The client validates data by running processes in parallel so the existing mainframe process delivers data to the Billing table while ours delivers to a Billing_NEW table. Once they accept the new process, the target table becomes Billing and the NEW table is dropped. I decided the most native SSIS route would be use specify the target table in as a parameter. We originally have a boolean parameter indicating whether we were loading the new table or the production one but that was more logic and overhead that just specifying which table to load. I force their queries to be dirty reads as some of these queries can be rather messy.

Click through for the script and explanation.

Comments closed

Calculating Effective Rights For A SQL Server Principal

Published 2018-10-10 by Kevin Feasel

Louis Davidson gives us a procedure which provides effective rights for a principal:

In my blog Calculating a Security Principal’s Effective Rights. I built a view, named Utilty.EffectiveSecurity that you could query to fetch a security principal’s rights to objects in a database. In that blog I tested the code and showed how it works. Now I have taken this to the extreme and expanded the view to include all of the user’s security by finding all of their rights to all of the things that the get rights for.

The list of possible permissions you can fetch can be retrieved from:

This returns the following 26 types of things that can have permissions assigned and returned by the sys.fn_my_permissions function:

Read on for the code.

Comments closed

Uninstalling A SQL Server Feature

Published 2018-10-10 by Kevin Feasel

Marek Masko shows how to uninstall a particular SQL Server feature:

If you wonder if it is possible to uninstall particular SQL Server feature instead of uninstalling the complete instance, then I have a good news. It is possible and it is very easy.

Let’s assume you want to uninstall PolyBase because you want to install it with a newer version of SQL Server (PolyBase can be installed on only one SQL Server instance per computer).

It brings me mild sadness that the feature Marek chose for this demo was Polybase, but slightly greater joy for the reason.

Comments closed

Help Make dbatools Even Better

Published 2018-10-10 by Kevin Feasel

Patrick Flynn wants you to improve dbatools:

As part of dbatools participation in this event we are encouraging contributors to assist “the road towards 1.0” by improving the examples available in the comment-based help, which power the new docs site at docs.dbatools.io.

The activity is available to anyone who wants to help and does not require any expertise in PowerShell. Any of the following actions are desirable:

  •  Fix typos in examples
  •  Fix obvious errors in examples
  •  Add examples to illustrate use of all possible parameters
  •  Add examples to illustrate use of pipeline support
  •  Add examples to illustrate combining multiple dbatools commands.
  •  Add examples that illustrate use of dbatools commands in new or interesting ways.

We are a looking for a max of 6-8 examples per command.

Patrick also shows you how easy it is to edit the documentation, so check that out and get contributing.

Comments closed

Image Clustering With Keras And R

Published 2018-10-09 by Kevin Feasel

Shirin Glander shows us how to use R to extract learned features from Keras and cluster those features:

For each of these images, I am running the predict() function of Keras with the VGG16 model. Because I excluded the last layers of the model, this function will not actually return any class predictions as it would normally do; instead we will get the output of the last layer: block5_pool (MaxPooling2D).

These, we can use as learned features (or abstractions) of the images. Running this part of the code takes several minutes, so I save the output to a RData file (because I samples randomly, the classes you see below might not be the same as in the sample_fruits list above).

Read the whole thing.

Comments closed