Press "Enter" to skip to content

Month: September 2018

What sys.dm_exec_query_stats Can Miss

Published 2018-09-19 by Kevin Feasel

Matthew McGiffen takes us through a scenario where sys.dm_exec_query_stats did not give a complete view of what was running on SQL Server:

I got less than 50 rows back so figured I had everything covered, but the total elapsed time across all the queries was less than ten minutes, I knew the server hadn’t been rebooted for about a month so potentially that could be as little as 20 seconds of query execution a night. Even if the full 10 minutes was from the last 24 hours that still didn’t account for the long run times.

So, I convinced myself it wasn’t the queries and started looking at other things they could check. It was a long list. Lots of theories revolved around the idea that something else was running on the host at the same time.

Click through for the rest of the story.

Comments closed

Permissions Needed To View Permissions

Published 2018-09-19 by Kevin Feasel

Kenneth Fisher shows us what we can do to grant a low-privilege user the ability to view permissions for other users:

Every now and again you’ll have a user that needs to be able to see what permissions other users have. Not change them, just look at them. In the cases I’ve seen it’s usually a manager or something similar reviewing the database permissions. Or maybe someone doing an entitlement review (checking to make sure everyone has the permissions they need, and just the permissions they need).

And, if you’ve made it this far, you read the first line and you know that the permission required is VIEW DEFINITION. What’s interesting is that this permission is usually used to grant someone the ability to look at the code behind T-SQL code objects. SPs, Views, Functions etc. But, it turns out that principals also have the VIEW DEFINITION permission.

Kenneth has a few notes for this as well, so check it out.

Comments closed

Removing The Windows Container Service

Published 2018-09-19 by Kevin Feasel

Melody Zacharias shows us how to remove the Windows container service from a machine:

This week I wanted to try out a new product from a vendor.  I thought I may use it for a demo so wanted to be able to run it on my laptop.  You never know when you go to do a demo and cannot access Azure, so I try to always put my demos on my laptop as a backup; just in case….

The product required me to remove all previous versions of windows containers on my machine.  The vendor recommended I use this posh command.

Remove-WindowsFeature Containers

I was fairly sure I did not have any but wanted to check to make sure.

Melody walks through a few tricky issues, including the difference between the command on Windows 10 versus Windows Server.

Comments closed

Running Google Translate Inside SQL Server ML Services

Published 2018-09-19 by Kevin Feasel

David Fowler shows us how to use SQL Server Machine Learning Services to call the Google Translate API via Python:

In my recent post, Installing External Modules into SQL Server’s Python I had a look at just how simple it is to import external modules into Python so that they can be used within SQL Server.

In this post I’d like to show you a little something to demonstrate how we can integrate one of these modules into SQL Server and just how powerful this can be.

This is really just for fun and it may not really be something that you’d want to put out into production but when I happened to notice that there’s a Python module that interfaces with Google Translate, I wondered to myself if it’d be possible to write a procedure that could take a string and automatically translate it into our native language.

There are a couple of setup steps but once you get past them, it’s easy going.

Comments closed

Tips For Creating Population Share Maps

Published 2018-09-18 by Kevin Feasel

Lisa Charlotte Rost uses election results to give us some tips on building map-based comparisons:

This map shows us that both parties received a higher vote share in the east than in the west. But it also artificially increases the polarisation: If the AfD gets just one more vote than the Linke, the whole district flips from pink to blue. And we would need to create a third category, “tied”, for the nine election districts in which there were exactly as many AfD voters as Linke voters. (The New York Times created that category for their “Extremely Detailed Map of the 2016 Election”.)

There is another option: We could show the percentage point difference between the two shares. To do so, we subtract the AfD votes from the Linke votes. If the result is positive, we show the district in blue. If it’s negative, we show it in pink.

This is a case where there’s not a huge difference between methods, but it can make a big difference in other situations.

Comments closed

Using Kubernetes To Support Microservices

Published 2018-09-18 by Kevin Feasel

Samir Behara walks us through a high-level explanation of how you can use Kubernetes to support development of microservices:

Kubernetes is an open source container-orchestration system for automating deployments, scaling and management of containerized applications. In this tutorial, you will learn how to get started with Microservices on Kubernetes. I will cover the below topics in details —

  • How does Kubernetes help to build scalable Microservices?

  • Overview of Kubernetes Architecture

  • Create a Local Development Environment for Kubernetes using Minikube

  • Create a Kubernetes Cluster and deploy your Microservices on Kubernetes

  • Automate your Kubernetes Environment Setup

  • CI/CD Pipeline for deploying containerized application to Kubernetes

This sticks mostly to a high-level architecture discussion, and does a good job at that.

Comments closed

Choose Your Next SQL Job Title

Published 2018-09-18 by Kevin Feasel

Tomaz Kastrun has created a job title generator in T-SQL:

While writing a sample random function in using T-SQL Server, I have remembered, why not write a job title generator for T-SQL domain only. You might have seen so called bulls**t job title generator and similar, but this one is T-SQL SQL server specific.

So, why not come up with random, yet funny T-SQL job titles. And making it, I have to tell you, it was fun. And I was simply hitting that F5 button in SSMS, to get new job title generated and laugh out loud.

It took me a few clicks, but I got “Qualitative R ggplot library Stackover subscriber,” which might be hitting the mark a little close.

Comments closed

Enabling Preview Features In Power BI

Published 2018-09-18 by Kevin Feasel

Jeanne Combrinck walks us through how to enable Power BI preview features:

Every month PowerBI releases new features. Some of the features are in preview mode and unless you turn it on you don’t get to use the preview features. This post explains how to turn them on.

Firstly you need to have the latest version of PowerBI to get the latest features. You can download it here.

Click through to see the remaining steps.  There are some interesting preview features that I’d expect to make it to the general product in the next few months.

Comments closed

Gaps And Islands: Solving Stochastic Islands Problems

Published 2018-09-18 by Kevin Feasel

Itzik Ben-Gan shares with us a special case of the islands problem:

In your database you keep track of services your company supports in a table called CompanyServices, and each service normally reports about once a minute that it’s online in a table called EventLog. The following code creates these tables and populates them with small sets of sample data:

[…]

The special islands task is to identify the availability periods (serviced, starttime, endtime). One catch is that there’s no assurance that a service will report that it’s online exactly every minute; you’re supposed to tolerate an interval of up to, say, 66 seconds from the previous log entry and still consider it part of the same availability period (island). Beyond 66 seconds, the new log entry starts a new availability period. So, for the input sample data above, your solution is supposed to return the following result set (not necessarily in this order):

It’s a neat twist on an old problem.

Comments closed

Backing Up Query Store Data

Published 2018-09-18 by Kevin Feasel

Grant Fritchey explains that Query Store data gets backed up like regular data, but with a caveat:

The core of the answer is very simple. Query Store, like any other data written to a database, whether a system table or a user table, is a logged operation. So, when you backup the database, you’re backing up Query Store data. When you backup the logs, you’re also backing up Query Store data. A point in time will include all the data written to the Query Store at that point.

However, that’s the kicker. At what point was the Query Store information written to disk?

Read on to learn when, and what you can do about it if you prefer otherwise.

Comments closed