Press "Enter" to skip to content

Month: January 2016

Power BI Gateways

Published 2016-01-21 by Kevin Feasel

Ginger Grant talks about Personal and Enterprise Power BI Gateways:

The Personal Gateway takes the data and imports it into Power BI. If you want to extract data from a variety of different places such as an Oracle Database, and Excel Spreadsheets, the Personal Gateway will support this, and the Enterprise Gateway won’t.   Remember the Enterprise Gateway only connects to three different data sources, and Excel and Oracle are not on that list. If you want to manage connection and refresh of the data as the administrator or provide access to the data to everyone who needs it, use the Personal Gateway.

It sounds like these are currently different enough that “both” might be the correct option within an organization, at least until Enterprise Gateway adds the missing features.

Comments closed

Columnstore Pagination

Published 2016-01-20 by Kevin Feasel

Aaron Bertrand investigates whether columnstore indexes help with pulling out specific “pages” of data:

Since then, I’ve wondered if ColumnStore indexes (both clustered and non-clustered) might help any of these scenarios. TL;DR: Based on this experiment in isolation, the answer to the title of this post is a resounding NO. If you don’t want to see the test setup, code, execution plans, or graphs, feel free to skip to my summary, keeping in mind that my analysis is based on a very specific use case.

I actually would have been surprised to find the answer here to be “yes.”  Columnstore is designed with aggregation in mind, rather than pulling out a fairly small subset of the data.

Comments closed

Finding A Page

Published 2016-01-20 by Kevin Feasel

Kenneth Fisher is looking for the database page tied to a particular row:

It’s not one of those things you have to do frequently but every now and again you need to know what page a particular row is on. It’s not terribly difficult. There is a virtual column called %%physloc%%, but unfortunately it’s not terribly useful on it’s own.

Finding a record’s page probably isn’t something you want to do every day; this is more one of those “once in a great while” activities which can help with troubleshooting.

Comments closed

Naming Standards

Published 2016-01-20 by Kevin Feasel

Aaron Bertrand talks about naming standards:

I’ve long been a proponent of not caring about which naming standards you use, but I do find it very important that your standards follow these three basic rules:

  1. The conventions make sense. You should be able to argue why the chosen convention is better than an alternative, and it can’t just be because you like it better. This doesn’t mean you have to win that argument, just that you should be arguing for something tangible.

  2. The entire team is on board. You should all agree on a standard before implementation, and any changes you make over time should be by committee.

  3. You don’t make exceptions. Even if you’re a one-person team, if you’re going to bother having a standard, it needs to be used consistently. It’s amazing how quickly exceptions can become the new rules.

If you want to talk subjectivity, I disagree with the idea that tables should be plural, as I tend to think terms of an entity (e.g., Person) which contains attributes, rather than the collection of entities which contain a specific combination of attributes.  Regardless, “set a standard and stick to it” is important advice.

Comments closed

OPENROWSET And BULK Rowsets

Published 2016-01-20 by Kevin Feasel

Dave Mason looks at the OPENROWSET function:

The built-in SQL Server function OPENROWSET() provides a way to access remote data from an OLE DB data source. It can be used with the BULK rowset provider to read data from a file without loading the data into a target table. This post will show the basics to get started with OPENROWSET(), the BULK rowset provider, and text files of fixed-width data fields.

For permanent connections, look into linked servers.  But for one-off things, OPENROWSET works fine.

1 Comment

What’s New In Power BI 2.0?

Published 2016-01-20 by Kevin Feasel

Meagan Longoria tells us what’s in Power BI version 2.0:

The Microsoft Power BI team was fast and furious in 2015, and there are no indications they are slowing down in 2016. If you haven’t checked out Power BI V2 since it was first released last summer, you might want to take another look. Many features have been added and updated since then. Based upon the release schedules since July, it seems there are 3 separate release cycles for Power BI:

  • The Power BI Service (PowerBI.com) gets weekly updates.

  • The Power BI Desktop tool gets monthly updates.

  • The Power BI mobile apps get monthly updates.

I expect no fewer than 6 updates per week from the Power BI team.

Comments closed

Multiple Instances On A VM

Published 2016-01-20 by Kevin Feasel

David Klee answers the question, when should you have multiple named instances on a single VM?

I am personally partial to having just one instance per VM, as long as the situation allows for it. The resource management area between SQL Server and Windows allows me to manage the overall resource consumption at the VM level, and en mass, managing at this layer rather than multiple layers is usually preferable. I claim that the extra overhead of managing more VMs is worth the resource management flexibility.

I agree with this.  The biggest advantage I see is in licensing, but if your environment is of a non-trivial size, you’re probably going to license the host instead of individual VMs.  Nevertheless, check out David’s pro-and-con list and see where your situation lies.

Comments closed

Automating SSAS deployments

Published 2016-01-19 by Kevin Feasel

Matt Smith as introduced SQL Server Analysis Services deployments to Octopus Deploy:

The only thing missing was SSAS. After watching Chris Webb’s video tutorial –Cube Deployment, Processing and Admin on Project Botticelli, I decided it had to use Microsoft.AnalysisServices.Deployment.exe. After a bit of scripting and testing, I managed to write a PowerShell that updates the xml config files for the deployment – it sets the ProcessingOption to ‘DoNotProcess’. It updates the Data source – where the cube will refresh the data from. The script isn’t perfect. For starters, what if you have more then one data source? Also what if your not using SQL Server 2014? Still the great thing about open source is that other can update it. Anyone can improve it, its not reliant on me having free time. So hopefully by the time we move to SQL 2016 someone will have already updated it to work with SQL 2016.

A big part of product maturation is automated deployment.  Good on Matt for introducing that to the community.

Comments closed

Specify Valid Network Protocols

Published 2016-01-19 by Kevin Feasel

Steve Jones shows how to specify the set of network protocols people can use to connect to a SQL Server instance:

I ran across a question on network protocols recently, which is something I rarely deal with. Often the default setup for SQL Server is fine, but there are certainly times you should add or remove network connectivity according to your environment.

Microsoft’s guidance on protocols pushes you toward TCP/IP and that’s a good default.

Comments closed

Delayed Durability Deletions

Published 2016-01-19 by Kevin Feasel

Melissa Connors looks at using Delayed Durability while deleting a large batch of records:

Recently, while considering possible use cases for Delayed Durability, it occurred to me that data loss might be entirely acceptable in cases where the data would not truly be lost. I have worked with a number of applications that have processes that purge old information from the database. If a purge process failed in these applications, data would simply live a little bit longer, and be purged the next time the process was successful – they have a recovery mechanism built in as it is. I decided to test Delayed Durability in a database with a long-running purge to observe the potential performance impact. I chose a process that was clearly contributing to transaction log waits, because that is where the real performance impact comes from when delaying durability. If you do not have notable waits or some level of a bottleneck there, you are not likely to improve anything simply by turning on this feature.

I was not aware that you could set durability at the transaction level; I was under the mistaken impression that once you flipped the switch, all transactions were subject to Delayed Durability.  Disk-heavy operations (like large batches of deletions) does seem like a good use case for this.

Comments closed