Press "Enter" to skip to content

Day: June 23, 2016

Polybase Setup Errors

Published 2016-06-23 by Kevin Feasel

Murshed Zaman on the Azure CAT team covers a number of Polybase configuration errors:

SSMS Error:

Any Select query fails with the following error.
Msg 106000, Level 16, State 1, Line 1
Java heap space

Possible Reason:

Illegal input may cause the java out of memory error.  In this particular case the file was not in UTF8 format. DMS tries to read the whole file as one row since it cannot decode the row delimiter and runs into Java heap space error.

Possible Solution:

Convert the file to UTF8 format since PolyBase currently requires UTF8 format for text delimited files.

I imagine that this page will get quite a few hits over the years, as there currently exists limited information on how to solve these issues if you run into them, and some of the error messages (especially the one quoted above) have nothing to do with root causes.

Comments closed

Query Store On Read-Only Databases

Published 2016-06-23 by Kevin Feasel

Kendra Little wants to know if Query Store works on read-only databases:

At least for now, Query Store can only record query performance in databases that are read-write. Once you go read-only you can review the performance of past queries, but you can’t track the performance of anyone who queried the database after the point it went read-only.

At least for now. Query Store is such an awesome feature that perhaps this will change in the future. (I don’t have any inside info, only optimism.)

That’s a little bit of a letdown, but makes perfect sense.

Comments closed

Checking Users And Principals

Published 2016-06-23 by Kevin Feasel

Shane O’Neill walks through a permissions issue and cautions against jumping the gun:

All the above is what I did.

Trying to fix the permission error, I granted SELECT permission.
Trying to fix the ownership chain, I transferred ownership.
Mainly in trying to fix the problem, I continually jumped the gun.
Which is why I am still a Junior DBA.

To be fair, I’d argue that if you intended to have replicated objects live in a different schema, the second action was fine.  Regardless, the advice is sound.

Comments closed

Polybase Statistics

Published 2016-06-23 by Kevin Feasel

I dig into a non-trivial Polybase query:

Polybase offers the ability to create statistics on tables, the same way that you would on normal tables.  There are a few rules about statistics:

  1. Stats are not auto-created.  You need to create all statistics manually.

  2. Stats are not auto-updated.  You will need to update all statistics manually, and currently, the only way you can do that is to drop and re-create the stats.

  3. When you create statistics, SQL Server pulls the data into a temp table, so if you have a billion-row table, you’d better have the tempdb space to pull that off.  To mitigate this, you can run stats on a sample of the data.

Round one did not end on a high note, so we’ll see what round two has to offer.

Comments closed

Netflix Billing Architecture

Published 2016-06-23 by Kevin Feasel

The Netflix tech blog discusses changing their billing infrastructure to be entirely in the cloud (AWS in this case):

Cleaning up Code: We started chipping away existing code into smaller, efficient modules and first moved some critical dependencies to run from the Cloud. We moved our tax solution to the Cloud first.

Next, we retired serving member billing history from giant tables that were part of  many different code paths. We built a new application to capture billing events, migrated only necessary data into our new Cassandra data store and started serving billing history, globally, from the Cloud.

We spent a good amount of time writing a data migration tool that would transform member  billing attributes spread across many tables in Oracle  into a much simpler Cassandra data structure.

We worked with our DVD engineering counterparts to further simplify our integration and got rid of obsolete code.

Purging Data: We took a hard look at every single table to ensure that we were migrating only what we needed and leaving everything else behind. Historical billing data is valuable to legal and customer service teams. Our goal was to migrate only necessary data into the Cloud. So, we worked with impacted teams  to find out what parts of historical data they really needed. We identified alternative data stores that could serve old data for these teams. After that, we started purging data that was obsolete and was not needed for any function.

All in all, a very interesting read on how to migrate large databases.  Even if you’re moving from one version of a product to another, some of these steps might prove very helpful in your environment.

Comments closed

R 3.3.1 Available

Published 2016-06-23 by Kevin Feasel

David Smith reports that a new version of R is now available, 3.3.1:

This minor update, codenamed “Bug in Your Hair”, makes a few small fixes to the R 3.3.0 release. Bugs fixed include mostly rarely-encountered cases like generating Gamma random numbers with zero or infinite rate parameters, and correctly matching text (with the matchfunction) that only differed in the encoding.

There are no new features in this update, and all R code and packages should work with R 3.3.1 just as they did with R 3.3.0. For a complete list of the fixes in R 3.3.1, follow the link below.

Even though this is a small update, it might be useful to check out.

Comments closed

New No-Longer-Features In SQL Server 2016

Published 2016-06-23 by Kevin Feasel

Bob Pusateri acts as SQL grim reaper:

32-bit SQL Server. SQL Server 2016 is 64-bit only. If for whatever reason you’re running on a 32-bit architecture, sadly you’re now out of luck – 2014 is the end of the road. On the bright side, there’s probably some new hardware in your future!

Compatibility Level 90. If you’re using compatibility level for backwards compatibility, the oldest available version in SQL Server 2016 is 100, which corresponds to SQL Server 2008. Compatibility level 90, SQL Server 2005, is no longer an option.

Bob also covers a few deprecated features, none of which (hopefully) are in regular use in your environment.

Comments closed

Radar Charts

Published 2016-06-23 by Kevin Feasel

Devin Knight continues his custom visuals course:

In this module you will learn how to use the Radar Chart, a Power BI Custom Visual. The Radar Chart is sometimes is also know to some as a web chart, spider chart, or star chart.  Using the Radar Chart allows you to display multiple categories of data on each spoke (like spokes on a bicycle wheel) of the chart. The Radar Chart does support the display of multiple metrics, which allows you to compare and contrast the “pull” that each category has on your metrics.

I still say you should stick with the fish chart for all of your visualization needs.

Comments closed

Clustered Indexes

Published 2016-06-23 by Kevin Feasel

Derik Hammer looks at the power of clustered indexes:

The data in a clustered index is logically sorted but does not guarantee that it will be physically sorted. The physical sorting is simply a common misconception. In fact, the rows on a given page are not sorted even though all rows contained on that page will be appropriate to its place in the logical sort order. Also, the pages on disk are not guaranteed to be sorted by the logical key either.

The most likely time where you will have a clustered index that is physically sorted is immediately after an index rebuild operation. If you are trying to optimize for sequential reads, setting a fill factor to leave free space on your pages will help limit how often you have pages physically out of order at the expense of disk space.

Derik also discusses four qualities for a good clustered index.  My preferred acronym is NUSE (Narrow, Unique, Static, Ever-increasing); Derik uses slightly different terms.

Comments closed

Standard Deviation Estimation

Published 2016-06-23 by Kevin Feasel

Dan Goldstein gives a rule of thumb for getting standard deviations for various distributions:

Say you’ve got 30 numbers and a strong urge to estimate their standard deviation. But you’ve left your computer at home. Unless you’re really good at mentally squaring and summing, it’s pretty hard to compute a standard deviation in your head. But there’s a heuristic you can use:

Subtract the smallest number from the largest number and divide by four

Let’s call it the “range over four” heuristic. You could, and probably should, be skeptical. You could want to see how accurate the heuristic is. And you could want to see how the heuristic’s accuracy depends on the distribution of numbers you are dealing with.

Sometimes you just don’t have STDEV() available.

Comments closed