Press "Enter" to skip to content

Month: February 2017

Blocked Process Report: monitorLoop

Published 2017-02-13 by Kevin Feasel

Michael Swart explains what the “monitorLoop” attribute is on the blocked process report:

You won’t find too much explanation about that field in the official documentation but I believe I know what it means.

The blocked process report is closely tied to deadlock detection and it’s generated by the same process as the deadlock monitor. If you remember, the deadlock monitor runs frequently looking for deadlocks (which are just blocking chains in a circle). It runs every couple seconds when there are no deadlocks, and if it detects any, it runs a bit more frequently. Each time it runs it’s called a monitor loop. The monitorLoop is just a number that starts at zero when the server restarts and increments by one each time the monitor runs.

Read on for more details.

Comments closed

Always Encrypted Data And SSMS

Published 2017-02-13 by Kevin Feasel

Justin Goodwin shows how to view Always Encrypted data from within Management Studio:

I am now able to view the encrypted data. Why is this?

Because I am connecting to the database from the database server, I have access to the Encryption Certificate that was generated. I can verify this by opening certmgr.msc and browsing to Personal -> Certificates:

Justin then goes on to show what happens when you don’t have access to the certificate and how to provide access to another machine.

Comments closed

More Dynamically Changing Shapes In Power BI

Published 2017-02-10 by Kevin Feasel

Koen Verbeeck has a follow-up post regarding dynamic shape changing in Power BI:

Yesterday I published the blog post Dynamically Changing Shapes in Power BI, which seemed to be quite popular in social media. It showed a lot of people were struggling with this issue and that some people had also found (alternative) solutions for it. In the comments of that blog post, you can find a solution proposed by Jason Thomas (blog | twitter). Jason has quite the Power BI & SSRS knowledge and he has found a cool trick to solve our changing images problem. In this blog post, I’m going to explain the solution step-by-step.

Click through to see the solution.

Comments closed

High-Compression JSON With vNext

Published 2017-02-10 by Kevin Feasel

Jovan Popovic gives an example of 25x compression of JSON data using a clustered columnstore index in vNext:

CLUSTERED COLUMNSTORE INDEXES provide extreme data compression in SQL Server and Azure SQL Database. With NVARCHAR(MAX) support in CCI indexes you can use them on your JSON data stored is database and get high 25x compression. Therefore, CCI is a perfect solution if you need to store a large volume of JSON data in your SQL Database.

ContosoDW database is publicly available for download, so you can use this database and the script below to re-create this table and try this in your environment.

I’m curious whether this will also apply to non-JSON data.

Comments closed

Finding Disproportionate Outliers With DAX

Published 2017-02-10 by Kevin Feasel

Rob Collie uses DAX to find disproportionate sentiment on a per-group basis:

My wife loves to travel AND she loves data, so it’s no surprise that she showed me the infographic in question.

“Oh come on, California residents don’t visit the Philippines more often than any other country.”  That was my first reaction.  (Mexico is the overwhelming #1 destination, basically for every state… because Cancun.  And because there are so many Mexican-Americans).

“Ah…  but California residents DO visit the Philippines disproportionately more often than they ‘should,’ according to national averages!”  That was my dawning second realization (and confirmed by the fine print of the Orbitz article, even though the article’s title suggests otherwise.)

So, how do we do that in DAX?  Pretty simply, actually.

I’m mentally working out whether this could be useful in anomaly detection.

Comments closed

Spark Data Frame Checkpoints

Published 2017-02-10 by Kevin Feasel

Jean Georges Perrin introduces checkpoints on Spark data frames:

Basically, I use a checkpoint if I want to freeze the content of my data frame before I do something else. It can be in the scenario of iterative algorithms (as mentioned in the Javadoc) but also in recursive algorithms or simply branching out a data frame to run different kinds of analytics on both.

Spark has been offering checkpoints on streaming since earlier versions (at least v1.2.0), but checkpoints on data frames are a different beast.

This could also be very useful for a quality control flow:  perform operation A, and if it doesn’t generate good enough results, roll back and try operation B.

Comments closed

SQL Server ODBC Driver 13.1 For Linux

Published 2017-02-10 by Kevin Feasel

Meet Bhagdev announces a new version of the SQL Server ODBC driver for Linux:

Added

  • BCP API support

    • You can use functions through the ODBC driver as described here on Linux.

  • Support for user-defined KeyStoreProvider for Always Encrypted

    • You can now user-defined/created AE Column Master Key keystore providers. Check out code samples and more information here.

  • Ubuntu 16.10 support

    • Developed a package Ubuntu 16.10 for an apt-get experience.

  • Dependency on the platform unixODBC Driver Manager instead of the custom unixODBC-utf16 Driver Manager

    • This avoids conflicts with applications/software that depends on the platform unixODBC Driver Manager.

No groundbreaking additions, but there are a couple nice fixes in the update.

Comments closed

Full-Text Search

Published 2017-02-10 by Kevin Feasel

Kendra Little gives the scoop on full-text indexing:

The “dirty little secret” about full-text search indexes is that they don’t help with ‘%blabla%’ predicates.

Well, it’s not a secret, it’s right there in the documentation.

A lot of us get the impression that full-text search is designed to handle “full wildcard” searches, probably just because of the name. “Full-Text Searches” sounds like it means “All The Searches”. But that’s not actually what it means.

Kendra’s take is a bit more optimistic than mine; I’m definitely more inclined to dump text out to a Lucene-based indexing system (like Solr or ElasticSearch), as they’ll typically perform faster and solve problems that full-text cannot.  Some of that may just be that I was never very good at full-text indexing, though.

Comments closed

When To Define Clustered Index Columns On Non-Clustered Indexes

Published 2017-02-10 by Kevin Feasel

Kim Tripp explains when to include a clustered index column on a non-clustered index column’s definition:

So, when SHOULD you explictly define the clustering key columns in a nonclustered index? When they ARE needed by the query.

This sounds rather simple but if the column is USED by the query then the index MUST have the column explicitly defined. Yes, I realize that SQL Server will add it… so it’s not necessary NOW but what if things change? (this is the main point!)

One of the more common cases I could think of is multi-part clustered indexes, like on a junction table.

Comments closed