Press "Enter" to skip to content

Month: August 2016

Power BI Storage Regions

Published 2016-08-15 by Kevin Feasel

Adam Saxton explains where Power BI stores your data and how they choose the region:

This selection is what drives the location of where your data will be stored. Power BI will pick a data region closest to this selection. This selection CANNOT BE CHANGED! You will want to think about the location that makes the most sense for you.

For example, if the majority of your organization’s users are in Australia, and you are in the United States, it probably makes more sense to select Australia for the country. You may also have legal requirements that your organization’s data needs to be in a specific country.

The “cannot be changed” part means that this decision is a lot more important than you might first realize.

Comments closed

The Case For Self-Service BI

Published 2016-08-15 by Kevin Feasel

Matt Allington makes the case for self-service BI:

Success or failure of Enterprise BI can be shown as a continuum.

The 5 sample points I call out (from best to worst) are:

  1. It adds lots of value to lots of people.
  2. It’s OK, lots of “export to Excel”
  3. Some use, but not worth the cost
  4. It is a failure and it is written off
  5. It is a failure but you keep it.

Note what I list as the worst possible outcome.  The solution is no good, and no one does anything about it.  This is much worse than writing it off as a failure as you can’t move on if you don’t accept you have a problem.

This is a provocative article with some good comments.  I’ve mixed emotions about this, as I see Matt’s point and agree with him in the hypothetical scenario, but it’s really easy for business users to get the wrong answers from self-service tools (e.g., introducing hidden cartesian products or not applying all business rules to calculations) and give up on the product.  That might be a function of me doing it wrong and I’ll cop to that if so, but I think that self-service BI needs a “You must be this tall to ride” sign.

Comments closed

Missing Libraries With SQL Server R Services

Published 2016-08-15 by Kevin Feasel

Tomaz Kastrun has a script to check and install missing packages in SQL Server R Services code:

Result in this case will be successful with correct R results and sp_execute_external_script will not return error for missing libraries.

I added a “fake” library called test123 for testing purposes if all the libraries will be installed successfully.

At the end the script generated xp_cmdshell command (in one line)

This is a rather clever solution to a problem which I’d rather not exist.  There really ought to be a better way for authorized users programmatically to install packages.

Comments closed

Latch Promotion

Published 2016-08-15 by Kevin Feasel

Ewald Cress discusses latch promotion threshold calculations:

Now I wish I could use the phrase “cycle-based promotion threshold” in a tone that suggests we were all born knowing the context, but to be honest, I don’t yet have all the pieces. Here is the low-down as it stands in SQL Server 2014:

  • Everything I’m describing applies only to page latches.

  • A cycle-based promotion simply means one that is triggered by the observation that the average acquire time for a given page latch (i.e. the latch for a given page) has exceeded a threshold.

  • Because the times involved are so short, they are measured not in time units but in CPU ticks.

  • There exists a global flag that can enable cycle-based promotions, although I do not know what controls that flag.

  • If cycle-based promotion is disabled, there is another path to promotion; this will be be discussed in Part 4.

I don’t think I’d ever seen the informational message Ewald mentions, so this was a brand new topic to me.

Comments closed

Table-Valued Parameters With Always Encrypted

Published 2016-08-12 by Kevin Feasel

Arvind Shyamsundar wants to use Table-Valued Parameters to load data in batches into an Always Encrypted table:

With this setup on the database side of things, we proceed to develop our client application to work around the TVP limitation. The key to doing this is to use the SqlBulkCopy class in .NET Framework 4.6 or above. This class ‘understands’ Always Encrypted and should need minimal rework on the developer front. The reason for the minimal rework is that this class actually accepts a DataTable as parameter, which is previously what the TVP was passed as. This is an important point, because it will help minimize the changes to the application.

Let’s get this working! The high-level steps are outlined below; there is a full code listing at the end of this blog post as well.

The upshot is that, at least as of today, Table-Valued Parameters are not supported with Always Encrypted.  Arvind does give an alternative, however, so click through for more information.

Comments closed

Enabling Table Changes

Published 2016-08-12 by Kevin Feasel

Sander Stad shows how to enable GUI-based table changes in SQL Server Management Studio:

When you’re developing on an instance you might want to change something in a database where the change might require to re-create the table. By default, the SQL Server Management Studio (SSMS) will prevent saving changes that require the table to be recreated.

Examples of changes that require table re-creation:

  • Change a column to no longer allow NULL values

  • Adding columns in the before another column

  • Moving a column

I agree with Sander:  this is a useful feature, but not something you want to abuse.  If you don’t understand the magnitude of your change, it could cause production problems.  And if you do understand the magnitude of your change, typically you’ll want to script it out for later.

Comments closed

Write-Only Permissions

Published 2016-08-12 by Kevin Feasel

Kenneth Fisher looks at granting write permissions but no read permissions to a user:

Now wait, why are they getting a read error when trying to UPDATE or DELETE? Because of the WHERE clause. The WHERE requires reading the data to see if a row meets the required conditions.

It turns out that write-only permissions don’t really work the way you’d want, as typically you want to read data even if your final goal is to update or delete rows.

Comments closed

Task Manager Graffiti

Published 2016-08-12 by Kevin Feasel

Brent Ozar has time to kill and a 72-core box on which to kill it:

For example, when I run it with 10,000 joins:

  1. Msg 8631, Level 17, State 1, Line 1
  2. Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.

When I drop it down to a much more realistic 5,000 joins:

  1. Msg 4074, Level 16, State 1, Line 1
  2. Client drivers do not accept result sets that have more than 65,535 columns.

Ah! Okay, that’s fair. (That’s also two error messages I’ve never seen before. Probably a good thing.) Alright, let’s take out the SELECT * and replace it with SELECT p1.* and see what happens:

This is a fun read.

Comments closed

Technical Debt

Published 2016-08-12 by Kevin Feasel

Daniel Hutmacher takes on the idea of technical debt:

When you think of technical debt, you may think only of classic shortcuts like making assumptions about the data, not using a TRY-CATCH block or perhaps hard-coding a manual correction into a stored procedure or view.

But I would argue that not paying attention to performance is just as much a technical debt. And rather than just crashing with an error message, performance issues are not always easy to just fix in production when your business users are working late to meet their deadlines, or when your web request are timing out. Start thinking of performance as an important part of your development process – half the job is getting the right data in the right place, the other half is making sure that your solution will handle double or triple the workload, preferably under memory pressure conditions with other workloads running at the same time.

Read the whole thing.

Comments closed