Press "Enter" to skip to content

Month: June 2017

Power BI Report Server Released

Published 2017-06-15 by Kevin Feasel

Ginger Grant notes that Power BI Report Server is now generally available:

The most glaring change from what was announced earlier, is Power BI Report Server can only connect to analysis services data sources, both tabular and multidimensional.  If you want to connect to SQL Server, Oracle or Excel or all three, use the Power BI Web Service.  Only going to the cloud version will users be able to create a data mashup or connect to anything but SQL Server.

Connecting to one data source is not what was promised when the Power BI Report Server was announced in May.  Various Power BI Product members held a session at the Microsoft Data Summit where attendees were able to ask questions.  I asked “When are we going to be able to use Power BI Report Server with data sources other than analysis services?”  In a room full of people, I was assured that it was a top priority of the team to release the same data connectivity functionality for Power BI Report Server that currently exists for Power BI Services and the current plan was to release this functionality the next release.

This is the most glaring flaw with Power BI Report Server at the moment.  Unfortunately, that probably makes it DOA for my purposes, at least until they introduce SQL Server relational as a valid data source.

Comments closed

Planning A Power BI Enterprise Deployment

Published 2017-06-15 by Kevin Feasel

Melissa Coates announces a new whitepaper co-authored with Chris Webb:

I’m excited to share that a new technical whitepaper I co-authored with Chris Webb is published. It’s called Planning a Power BI Enterprise Deployment. It was really a fun experience to write something a bit more formal than blog posts. My interest in Power BI lies in how to successfully deploy it, manage it, and what the end-to-end story is especially from the perspective of integration with other data assets in an organization. Power BI has grown to be a huge, wide set of features so we got a little verbose at just over 100 pages.

It is a beefy whitepaper.  I haven’t had a chance to read it yet, but it’s now on my list.

Comments closed

Using rxInstallPackages

Published 2017-06-15 by Kevin Feasel

Tomaz Kastrun explains how to use rxInstallPackages to install packages on Microsoft R Server:

In rxInstallPackages function use computeContext parameter to set either to “Local” or to your  “SqlServer” environment, you can also use scope as shared or private (difference is, if you install package as shared it can be used by different users across different databases, respectively for private). You can also specify owner if you are running this command out of db_owner role.

This updated installation method is certainly easier than the prior method, which included incantations and sacrificing a chicken.

Comments closed

The Downside Of Oversizing

Published 2017-06-15 by Kevin Feasel

Aaron Bertrand shows why you might not want to oversize VARCHAR columns by too much:

Now, whether you go by the old standard or the new one, you do have to support the possibility that someone will use all the characters allowed. Which means you have to use 254 or 320 characters. But what I’ve seen people do is not bother researching the standard at all, and just assume that they need to support 1,000 characters, 4,000 characters, or even beyond.

So let’s take a look at what happens when we have tables with an e-mail address column of varying size, but storing the exact same data:

This is a good argument against automatically using VARCHAR(8000) (much less MAX) when creating columns.

Comments closed

Checking Backup Encryption Size Differences

Published 2017-06-15 by Kevin Feasel

Tracy Boggiano has a script to check whether your backup file sizes are larger or smaller when they’re encrypted:

I had a recent project to enable backup encryption on all our servers.  Then question from the storage team came up will this required additional space.  Well by then I had already enabled in all our test servers so I wrote a query that would compare the average size of backups before encryption to after encryption.  Keep in mind we do keep only two weeks of history in our backup tables so this is a fair comparison.  If you don’t have maintenance tasks to clean up your backup history then you should have backup_start_time to the where clauses to get more accurate numbers and setup a maintenance tasks to keep your msdb backup history in check.

Unfortunately, Tracy leaves us in suspense regarding whether they did increase.

Comments closed

Duplicate Key Error On DBCC CLONEDATABASE

Published 2017-06-15 by Kevin Feasel

Erin Stellato shows an edge case when you have a rather old database you’re trying to clone:

If you’ve been using DBCC CLONEDATABASE at all, you might have run into a cannot insert duplicate key error (or something similar) when trying to clone a database:

Database cloning for ‘YourDatabase’ has started with target as ‘COPY_YourDatabase’.
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘sys.sysschobjs’ with unique index ‘clst’. The duplicate key value is (1977058079).

If you do some searching, you’ll probably end up at this Connect item: DBCC DATABASECLONE fails on sys.sysowners.

The Connect item states that the problem exists because of user objects in model.  That’s not the case here.

I’m working with a database created in SQL Server 2000…now running on SQL Server 2016.

This isn’t very likely to pop up for most places (I hope!), but it’s good to know.

Comments closed

Finding Clustered Columnstore Index Candidates

Published 2017-06-15 by Kevin Feasel

Sunil Agarwal has a script that helps you find potential clustered columnstore index candidates:

Most of us understand that clustered columnstore index can typically provide 10x data compression and can speed up query performance up to 100x. While this sounds all so good, the question is how do I know which tables in my database could potentially benefit from CCI? For a traditional DW scenario with star schema, the FACT table is an obvious choice to consider. However, many workloads including DW have grown organically and it is not trivial to identify tables that could benefit from CCI. So the question is how can I quickly identify a subset of tables suitable for CCI in my workload?

Interestingly, the answer lies in leveraging the DMVs that collect data access patterns in each of the tables. The following DMV query provides a first order approximation to identify list of tables suitable for CCI. It queries the HEAP or the rowstore Clustered index using DMV sys.dm_db_index_operational_stats to identify the access pattern on the base rowstore table to identify tables that meet the criteria listed in the comments below:

Read on for the script, which has a sensible set of criteria.

Comments closed

The Most Useless SQL Server Feature

Published 2017-06-15 by Kevin Feasel

Adam Machanic put out a poll on Twitter, asking for the most useless SQL Server feature:

It was at this point that I realized just how many candidates there are for “most useless” things lying around the product. So I decided to create my own tweet. I asked for the most useless feature, anytime between version 7.0 (which I would call the beginning of SQL Server’s “modern era”) and now. I received quite a few suggestions, and so I have decided to catalog them here—along with a bit of personal commentary on each one.

The list that follows is mostly unordered and culled straight from what I received on Twitter. Hopefully I haven’t missed anything due to Twitter’s weird threading and “priority” mechanisms. And please let me know in the comments if your favorite useless feature is missing, or you’d like to add a comment and/or argument about one of these. Perhaps we can find some way to turn these dark and ugly corners into things of beauty? Well, we shall see…

I almost completely agree with Adam’s opinions on this long list.  I’d emphasize, though, that In-Memory OLTP is by no means useless.

1 Comment

Power BI Premium Released

Published 2017-06-14 by Kevin Feasel

Dustin Ryan reports that Power BI Premium is now generally available:

Microsoft stated that Power BI Premium would be GA late Q2 of 2017, which could mean nothing else aside from June. Well, today is the day that Power BI Premium, along with Power BI Report Server, are generally available.

Lucky for me, I have a user on a tenant with Power BI Premium capacity available for me to use. When you first log into your tenant after Power BI Premium capacity has been purchased, you are presented with the following welcome screen which includes a link to learn more about Premium capacity.

The Power BI team continues to be busy.

Comments closed

When Snapshots Begin

Published 2017-06-14 by Kevin Feasel

Kendra Little explains when a transaction really begins when you are in the snapshot isolation level:

  • 00.000 – Session A sets its isolation level to snapshot

  • 00.001 – Session A explicitly begins a transaction with BEGIN TRAN

  • 00.002 – Session A starts a WAITFOR command for 15 seconds

  • 10.000 – Before the WAITFOR completes, Session B inserts rows into dbo.Table

  • 15.001 – Session A starts a SELECT from dbo.Table, which returns the rows that Session B inserted

This seems wrong, because many of us commonly say things like, “in Snapshot Isolation level, all statements see data consistent with the beginning of the transaction.”

But in this case, Session B inserted the rows after Session A began its transaction using Snapshot Isolation level. So why did Session A see those rows?

Kendra explains the nuance well, so read the whole thing.

Comments closed