Press "Enter" to skip to content

Month: July 2016

Graphing Swear Words In Movies

Published 2016-07-06 by Kevin Feasel

Jos Dirksen uses Spark and D3 to count and graph swear words in movies:

So how do we do this? Well, the first thing to do is get the number of swearwords per minute. I mentioned that for the original article someone just counted every swearwords, in our case, we’re just going to parse a subtitle file, and extract the swear words from that.

Without going into too much detail, you can find the code I’ve experimtend with in this gist (it’s very ugly code, since I just hacked something together that worked).

Jos includes counts for four movies.  This link does contain a few bad words, but if you get past that, it’s a good pattern for analyzing word counts in general.

Comments closed

TDE And Backup Compression

Published 2016-07-06 by Kevin Feasel

Erik Darling notes that databases using Transparent Data Encryption now support backup compression:

First, the database without a Max Transfer Size at the bottom was a full backup I took with compression, before applying TDE. It took a little longer because I actually backed it up to disk. All of the looped backups I took after TDE was enabled, and Max Transfer Size was set, were backed up to NUL. This was going to take long enough to process without backing up to Hyper-V VM disks and blah blah blah.

The second backup up, just like the blog man said, no compression happens when you specify 65536 as the Max Transfer Size.

You can see pretty well that the difference between compressed backup sizes with and without TDE is negligible.

Check it out, including the table Erik put together.  I’m glad that backup compression is now supported, although I’m kind of curious how they can do that while retaining encrypted backups—are they decrypting data, writing to backup (and compressing), and then encrypting the backup?  That’d be worth checking out with a hex editor.

Comments closed

“Add Existing Package” Quirk

Published 2016-07-06 by Kevin Feasel

Kenneth Fisher points out an “interesting” quirk with SQL Server Data Tools when you try to add an existing package under certain circumstances:

That SSIS change just won’t stick! There is a new business requirement, so now you have to change an existing SSIS package. You opened up a new project, imported the package, and made your changes.

But the changes just aren’t showing up. You back and look again and sure enough, the changes you made are there. You run your job (or whatever) and still the change aren’t taking effect. So what happened?

Read on for the cause and the solution.

Comments closed

SET XACT_ABORT On By Default

Published 2016-07-06 by Kevin Feasel

Tibor Karaszi notes an important change to SQL Server Management Studio with the July 2016 release:

Now, exposing one more SET option can’t be bad, right? But Erland Sommarskog found out that this is checked by default. Now, using XACT_ABORT might now [sic] be a bad thing, au contraire. See this one of Erland’s series of error handle articles.

Making this change after all of these years is a little odd.  Making it in a regular update is very odd.  Using SET XACT_ABORT ON is a smart move in general, but there are times in which you don’t want to rollback immediately after an error; the problem is, are all of those places in your code well-documented?

Comments closed

New Powershell Cmdlets

Published 2016-07-06 by Kevin Feasel

Chrissy LeMaire talks about the 25 new cmdlets in SQL Server’s Powershell module:

One of the best things about these cmdlets is that it makes failed jobs a whole lot easier to find. Note that SQL Server Management Studio 2016 does not have to be installed on the server — only your workstation. That’s because the cmdlet is built on top of SMO which is available in all versions of SQL Server since 2000.

As an aside, you may be wondering what a cmdlet is, and what’s the difference between a cmdlet and what you and I write. A cmdlet is a formally written PowerShell command that’s written in C#. I’ve never written one in my life. Instead, I write Functions. They’re both essentially the same, but a cmdlet is fancier on the back end.

Powershell is the present (not the future) for administration, so it’s nice to see the SQL Server team pushing themselves a bit when it comes to cmdlets.  They’ve a long way to go, but this is a nice step.

Comments closed

Indexes On Disk

Published 2016-07-06 by Kevin Feasel

Kendra Little has a great diagram showing which indexes are disk-based and which are memory-resident:

I was looking through some terms in SQL Server documentation the other day, thinking about what it’s like to learn about SQL Server’s indexes when you’re new to the field. I jotted down a note: B-tree = Rowstore = Disk Based

And then I realized that’s not quite right.

Not all disk based indexes are traditional clustered and nonclustered indexes. Columnstore indexes are also disk based. Updatable Columnstore indexes use special rowstore B-trees behind the scenes. And Books Online says “rowstore” also refers to Memory-Optimized tables.

If you’re new to indexing, this picture will save you some learning time.

Comments closed

Histograms

Published 2016-07-06 by Kevin Feasel

Devin Knight continues his Power BI visualization course:

In this module you will learn how to use the Histogram, a Power BI Custom Visual.  A Histogram is a column chart which shows the distribution of occurrences divided into categories, called bins.  This type of chart is useful for estimating density and discovering outliers.

Another fine entry in a great series.  Check it out.

Comments closed

Query Folding

Published 2016-07-05 by Kevin Feasel

Devin Knight discusses query folding and the View Native Query feature inside Power Query:

The idea behind Query Folding is to push the logic that you built into a Power BI query back to the data source server and execute it there in it’s native language instead of doing a client side transform of the data.  Why is this important?  Let me give you an example.  Say you have a 2 billion row SQL Server table you need to connect to in Power BI, but you want to filter to only return the last year of data.  With Query Folding the filter of that data is done on the SQL Server side instead of on the client side. If Query folding did not take place than that would mean all 2 billion rows would be brought across the network only to then filtered out on the client workstation.  So clearly the ideal situation is that all your queries get folded for the best possible performance, but Query Folding only works in certain scenarios.

I hadn’t heard the term “query folding” before, but the concept makes sense; in the PolyBase world, it’s “predicate pushdown.”  Check out Devin’s post, as he shows how easy it is to see to what extent your query is running client-side versus server-side.

Comments closed

DDL Events

Published 2016-07-05 by Kevin Feasel

Dave Mason looks at DDL triggers:

DDL triggers and the events they handle are run within the same transaction, which can be rolled back. This is a powerful and convenient feature. It gives you the ability to programmatically “undo” undesirable events. Let’s look at a variation of the previous DROP_TABLE trigger we created. This script will create a temporary logging table, drop the previous trigger, and create it again. This time, the trigger will roll back the transaction, preventing the table from being dropped. Then it inserts a message to the log table.

There are a few gotchas here, to be sure, but DDL triggers are powerful tools.

Comments closed