Press "Enter" to skip to content

Month: November 2016

Query Folding In Power Query

Published 2016-11-18 by Kevin Feasel

Reza Rad discusses the performance implications of query folding in M:

I can’t start talking about the issue without explaining what Query Folding is, so let’s start with that. Query Folding means translating Power Query (M) transformations into native query language of the data source (for example T-SQL). In other words; when you run Power Query script on top of a SQL Server database, query folding will translate the M script into T-SQL statements, and fetch the final results.

Click through for more details.  The advice here sounds pretty similar to what we get for optimizing Integration Services:  push as much of the heavy lifting onto well-optimized source queries as possible, particularly when it comes to filtering out rows.

Comments closed

Calling Cognitive Services With R

Published 2016-11-17 by Kevin Feasel

David Smith has written a go-to guide for connecting to Azure Cognitive Services using R:

There’s no official R package (yet!) for calling Cognitive Services APIs. But since every Cognitive Service API is just a standard REST API, we can use the httr package to call the API. Input and output is standard JSON, which we can create and extract using the jsonlite package.

(There’s also an independent R interface to the text APIs. And there are already Python SDKs for many of the services, including the Face API.)

This is also useful for other REST APIs for times when there isn’t already a pre-built package to do most of the translation work for you.

Comments closed

Saving DBCC Results

Published 2016-11-17 by Kevin Feasel

Wayne Sheffield shows how to save CHECKDB results to table:

Okay, let’s try this out. At the above BOL links, there are two DBCC commands that are documented to use the TABLERESULTS option: OPENTRAN and SHOWCONTIG. Testing all of the other DBCC commands shows that this option can also be used on the CHECKALLOC, CHECKDB, CHECKFILEGROUP and CHECKTABLE commands. I’m going to continue this post with using DBCC CHECKDB. If you check BOL, it does not mention the TABLERESULTS option.

As Wayne notes, you can do this for other DBCC commands as well.

Comments closed

Graphing Row Counts With R

Published 2016-11-17 by Kevin Feasel

I look at one use of R for DBAs:

I have a client data warehouse which holds daily rollups of revenue and cost for customers.  We’ve had some issues with the warehouse lately where data was not getting loaded due to system errors and timeouts, and our services team gave me a list of some customers who had gaps in their data due to persistent processing failures.  I figured out the root cause behind this (which will show up as tomorrow’s post), but I wanted to make sure that we filled in all of the gaps.

My obvious solution is to write a T-SQL query, getting some basic information by day for each customer.  I could scan through that result set, but the problem is that people aren’t great at reading tables of numbers; they do much better looking at pictures.  This is where R comes into play.

Click through for the code and a walkthrough of what each line is doing.

Comments closed

Visualizing SQL Server Agent Jobs

Published 2016-11-17 by Kevin Feasel

Daniel Hutmacher shows how to visualize SQL Server Agent job runtimes using spatial data types:

If all you have is a hammer, everything will eventually start looking like a nail. This is generally known as Maslow’s hammer and refers to the fact that you use the tools you know to solve any problem, regardless if that’s what the problem actually needs. With that said, I frequently need a way to visualize the load distribution of scheduled jobs over a day or week, but I could never be bothered to set up a web server, learn a procedural programming language or build custom visualizations in PowerBI.

So here’s how to do that without leaving Management Studio.

Click through for discussion and link to the code.

Comments closed

New SSMS And SSDT

Published 2016-11-17 by Kevin Feasel

In this blast of new things, Andy Leonard makes mention that there are new versions of Management Studio and Data Tools available:

It’s Release Day! 🙂

New versions of SQL Server Data Tools (SSDT) are available here. SSDT 16.5 and 17.0 (RC1) are available. Also available are Data-Tier Application Framework (DacFx) versions 16.5 and 17.0 (RC1).

New versions of SQL Server Management Studio (SSMS) are available here. SSMS 16.5 and 17.0 (RC1) versions are available for SSMS.

It’s going to be a busy couple of days for some people…

Comments closed

New Features In 2016 SP1

Published 2016-11-17 by Kevin Feasel

Niko Neugebauer looks at new functionality released as part of SQL Server 2016 SP1:

– CREATE OR ALTER. I almost cried when I found out that it was implemented. I was asking, begging, threatening, crying for years to get this in the SQL Server. Now, I can finally have future project deployments of those who are not using SSDT running with much less problems.
Now we can modify and deploy objects like Stored Procedures, Triggers, User-Defined Functions, and Views without any fear. Just “Make it so!”

Yeah, they’re not as big as “every Enterprise Edition development feature over the past decade is now available to anybody” but there are some nice additions here.

Comments closed

Ola’s Jobs On Linux

Published 2016-11-17 by Kevin Feasel

Joey D’Antoni gives a few pointers around SQL Server on Linux, including running Ola Hallengren’s scripts on a system without a SQL Server Agent:

Aside from a couple of DMVs that show you Linux specific performance information, everything in SQL Server on Linux is the same. Some of the HA and DR functionality is not complete, and the SQL Agent is not done, however you can use cron (and if you’re familiar with Linux, you should learn about cron—I’ll have another post on that next week).

These are some good notes, so check it out.

Comments closed

Installing SQL Server On Ubuntu

Published 2016-11-17 by Kevin Feasel

James Anderson installs SQL Server on Ubuntu:

’m installing Ubuntu 16 on a Hyper-V VM. I’ll be using a generation 1 Hyper-V VM as I’ve had much better luck installing Linux VMs on these. I’ve had boot and performance issues with Linux on Generation 2 VMs.

After installing Ubuntu I followed the instructions in the SQL Server Linux documentation and recorded my progress below.

For Ubuntu we need to register the SQL Server repository before we can use the apt-get package manager to download and install SQL Server.

This step-by-step walkthrough shows that installing SQL Server on Linux is pretty easy:  it’s just a single package (RPM or DEB depending upon your flavor of Linux).

Comments closed