Press "Enter" to skip to content

Curated SQL Posts

Extended Events In Azure Data Studio

Published 2018-11-23 by Kevin Feasel

Jason Brimhall walks us through installation of the SQL Server Profiler on Azure Data Studio:

Azure Data Studio (ADS) is getting all sorts of love and attention these days. So much so that they have finally gotten around to adding Extended Events (XE) to the tool – sort of. Now we have the power to run traces on SQL Server via ADS.

The presence of XE in ADS comes via an extension and comes with a few other caveats. I will explore the extension for XE available in ADS in this article and discuss some of the caveats. As you read the article, it might be helpful to go ahead and download ADS if you do not already have it.

Jason points out the name of SQL Server Profiler and I’d like to add my own bit of irritation here.  “Don’t use Profiler, except the one good Profiler but not the Profiler you think you’re using unless you know not to use Profiler and use Profiler instead.”  Yeah, that’s pretty clear.

Comments closed

dbatools: On The Way To 1.0

Published 2018-11-23 by Kevin Feasel

Chrissy LeMaire points out a bunch of changes to dbatools:

Aliases have been added for the changes, so these are not breaking changes:

  • Mismatched Copy commands have been renamed to match their corresponding Get command names (ie. Copy-DbaCentralManagementServer is now Copy-DbaCmsRegServer).

  • Most parameters named Password have been changed to SecurePassword. They’ve always been a SecureStringdata type but this makes that clear.

  • The parameters ExcludeAllSystemDb and ExcludeAllUserDb have been changed to ExcludeSystem and ExcludeUser, respectively.

These are some of the non-breaking changes, but this latest release has several breaking changes too.  Chrissy is promising no more breaking changes for a little while, so it’s probably a good time to upgrade and check those scripts to see what you need to change.

Comments closed

Query Store & Spinlocks

Published 2018-11-21 by Kevin Feasel

David Fowler ran into an issue with the Query Store process experiencing waits due to spinlocks:

We were enjoying a nice peaceful afternoon when we hear panicked shouting that a SQL Server had become unresponsive and the customers were unable to do anything.

We moseyed on down to the server in question to take a look at it.  One thing stood out immediately, CPU was pegged out at 100% but SQL itself didn’t actually seem to be doing anything, transactions\second was on the floor. Unfortunately this happened a while back and I didn’t think to capture any graphs or metrics at the time so you’re just going to have to take my word for this.

The issue David ran into was subsequently fixed, making this a cautionary tale to keep those SQL Server instances patched.

Comments closed

Dealing With Zero-Value Rows In dplyr

Published 2018-11-21 by Kevin Feasel

Kieran Healy shows an oddity in dplyr when dealing with zero-value records:

That looks fine. You can see in each panel the 2015 column is 100% Men. If we were working on this a bit longer we’d polish up the x-axis so that the dates were centered under the columns. But as an exploratory plot it’s fine.

But let’s say that, instead of a column plot, you looked at a line plot instead. This would be a natural thing to do given that time is on the x-axis and so you’re looking at a trend, albeit one over a small number of years.

This is behavior I hadn’t run into, and it does seem a bit odd.  On a totally unrelated note, Healy’s Data Visualization: A Practical Introduction is one of the best books on the topic.

Comments closed

The Good And Bad Of Dataflows

Published 2018-11-21 by Kevin Feasel

Teo Lachev gives us the lowdown on Dataflows in Power BI:

There is a lot to like about dataflows. I can think of two primary self-service scenarios that can benefit from dataflows:

  • Data staging – Many organizations implement operational data stores (ODS) and staging databases before the data is processed and loaded in a data warehouse. As a business user, you can use data-flows for a similar purpose. For example, one of our clients is a large insurance company that uses Microsoft Dynamics 365 for customer relationship management. Various data analysts create data models from the same CRM data, but they find that refreshing the CRM data is time consuming. Instead, they can create a dataflow to stage some CRM entities before importing them in Power BI Desktop. Even better, you could import the staged CRM data into a single dataset or in an organizational semantic model to multiple data copies and duplicating business logic.

  • Certified datasets – One way to improve data quality and promote better self-service BI is to prepare a set of certified common entities, such as Organization, Product, and Vendor. A data steward can be responsible for designing and managing these entities. Once in place, data analysts can import the certified entities in their data models.

Read on for some more positives and negatives.

2 Comments

Checking If Ports Are Open Using Powershell

Published 2018-11-21 by Kevin Feasel

Anthony Nocentino has a quick Powershell script to see if ports are open on a machine:

Ever want to confirm that a port is accessible from one computer to another? There’s a PowerShell cmdlet for that, Test-NetConnection. With the -Port option, this cmdlet will do a quick TCP three-way handshake on the remote system to confirm that the service is available and reports back if it succeeded or not. Check out that last line of output TcpTestSucceeded: False. That indicates that this port is not accessible. You can see, however, that the system is reachable via ICMP (Ping), PingSuceeded: True so we know that the remote system is alive, just not listening on the port we want to access.

For when your security team won’t let you install nmap.

Comments closed

Root Cause Discovery For Availability Group Failovers

Published 2018-11-21 by Kevin Feasel

Sourabh Agarwal announces a new tool to determine why your Availability Group failed over:

The first step in using the utility is to configure the configuration.json file to include the location of the data files and the details of the availability group for which analysis is being done. For a correct analysis, all replicas in the availability groups needs to be listed in the configuration file.

The next step is to capture the various logs from each of the replicas and add those under the data folder. The following files are required for the analysis.

  • SQL error logs

  • Always On Availability Groups Extended Event Logs

  • System Health Extended Event Logs

  • System log

  • Windows cluster log

This looks interesting.

Comments closed

What Power BI Costs

Published 2018-11-21 by Kevin Feasel

Eugene Meidinger delineates what is free versus paid with respect to Power BI:

The Power BI Service, think powerbi.com, allows for free users. These free users can create reports and upload them, but with a significant number of limitations. The biggest is you only have one way of sharing content to others. Specifically with Publish to Web, which essentially makes your entire report free to the public.

You also only have one way of privately consuming other people’s reports, and that’s if someone places content in Power BI Premium. Otherwise, other users can’t share their reports directly with you. Power BI Free users are truly and island to themselves.

One other thing worth nothing is that you can’t sign up with a personal email. David Eldersveld has a good blog post on the issue. As of this writing, the uservoice request to change this has 2,800 votes.

See here for some more limitations of the free version of Power BI.

Read the whole thing.

Comments closed

A Pessimistic View Of The State Of Deep Learning

Published 2018-11-20 by Kevin Feasel

William Vorhies provides us a negative (and necessary) look at the current state of Deep Learning solutions:

Reinforcement Learning (RL) is arguably the hottest research area in AI today because it appears RL can be adapted to any problem that has a well-defined reward function.  That encompasses game play, robotics, self-driving cars, and frankly pretty much else in machine learning.

Within RL, the hottest research area is Deep RL which means using a deep neural net as the ‘agent’ in the training.  Deep RL is seen as the form of RL with the most potential to generalize over the largest number of cases and perhaps the closest we’ve yet come to AGI (artificial general intelligence).

Importantly, Deep RL is also the technique used to win at Alpha Go which brought it huge attention.

The problem is, according to Alex Irpan, a researcher on the Google Brain Robotics team that about 70% of the time they just don’t work.

Alex has written a very comprehensive article critiquing the current state of Deep RL, the field with which he engages on a day-to-day basis.  He lays out a whole series of problems and we’ve elected to focus on the three that most clearly illustrate the current state of the problem with notes from his work.

Vorhies is not unduly negative and is optimistic in the medium to long term, but he is right in noting that there is a lot of work yet to do in this field.

Comments closed