Anti-Slicers In Power BI

Nicolo Grando shows how to create anti-slicers in Power BI:

A normal slicer can be tedious when you want to show everything apart from just one or two entries in your filtered tiles – don’t take your finger off the Ctrl key! You could always turn on Select All, then unselect the items. But you may not want Select All enabled, and it’s not available for chiclets. Or you could use Visual/Page/Report level filters, but these are not available in dashboards or publish-to-web. So you may be interested in an anti-slicer? There are many ways to do this, this is one approach.

This is an interesting problem to solve, and I think this is a nice solution.

Using Powershell To Shred Query Plan XML

Mike Fal shows how to use Powershell (or any .NET language) to read parts of a query plan:

Once the pattern is down, the use is pretty straightforward. There’s also more options accessible to you. If we just look at the RunTimeCountersPerThread node, we can compare other values such as Rows, Scans, and CPU time. We could really get crazy and extract all the different statements within the batch. There are numerous possibilities for analysis and review.

I’m not here to tell you that you should start using PowerShell to automate query tuning. Query performance is an art form and requires a lot of case-by-case analysis. However, like any great carpenter, it’s good to know the capabilities of your tool set. Understanding the options available to you not only helps you be more effective, but can also provide answers you may not have had access to.

It’s another tool for the belt.

Security As A Metaphor

Kevin Hill explains some of the basics of security using an extended metaphor:

Imagine if you will, that you own a house.

Because you are the owner, you have a key to the house.   You can do anything you want in any room of the house.  You decide who else gets keys, and what they can do inside.  We’ll assume your significant other also has a key and equal rights to all rooms.

Read on for the rest of the story.

SQL Server VMs In Google Compute Engine

Kevin Feasel

2017-02-02

Cloud

Brent Ozar reports on Google cloud improvements:

Google Compute Engine is infrastructure-as-a-service (IaaS), selling virtual machines by the hour like Azure VMs and AWS EC2. You can run whatever you like in these VMs, and Google has long supported running SQL Server in GCE. You could build your own SQL Servers, or use pre-built (and licensed) instances of SQL Server 2012, 2014, or 2016 – but only Standard or Web Editions.

Today, GCE supports Enterprise Edition AND Always On Availability Groups.

We’ve got a white paper coming soon on how to build and test it, plus more cool stuff in the pipeline that DBAs will love.

We live in interesting times.

SQL On Linux Connectivity

Slava Murygin troubleshoots connectivity issues with SQL Server on Linux:

After you enter SA password you have to get “vNext” version of your SQL Server.
If you did not get the correct response you might have following problems:
I)   Wrong SA password. To fix it, just re-configure SQL Server.
II)  SQL Server Tools are not installed.
III) Typo. Check your syntax.

The troubleshooting process is a bit different from SQL Server on Windows, but it’s still pretty straightforward.

Avoiding Percent Growth

Angela Henry has a script to tame percent growth on database files:

I decided I needed to do something else other than just send an email notification, I needed to take corrective action when it occurred.  So I wrote a little stored procedure that will take the ALTER DATABASE statement as a parameter, parse it and take the appropriate corrective action. 

Simple enough, right?  Now I just need to add the call to my newly created stored procedure in my server level trigger and we are good to go.  But wait, you can’t ALTER a database within an ALTER DATABASE statement (don’t believe me? Use this as a learning exercise to see what happens when you try).  So what could I do?  There are several things you could do, but I chose to create a table that could hold this newly created ALTER DATABASE statement and insert the record there.  Then I created a SQL Agent job that runs once every hour and reads that table and executes any entries it finds, then deletes them after successfully executing.

Read the whole thing, including the disclaimer.

Time Zone Info Table

Kevin Feasel

2017-02-01

Data

Andrea Allred notes that there’s now a time zone system table, as of SQL Server 2016:

Yes, it is 132 rows of magic! Now that we have this super cool table, how do we use it? Let’s pretend that my data is time-stamped in US Mountain Standard Time, but I want to display it in Western Australia Standard Time.

Read on for table details.

Powershell Defaults

Michael Sorens has some time-saving defaults for Powershell:

Besides setting up some convenient shortcuts for use with built-in cmdlets, for developers it is also handy to be able to work some magic with your own custom cmdlets. In my shop, for example, we have one module containing a couple dozen cmdlets, many of which use a common parameter, Mode. This Mode parameter varies from client to client, but for any single client, every cmdlet working on their data needs to use the same value of Mode. So I have to add -Mode hist-0010-dev.test onto each cmdlet I am using, which gets very tiring/annoying very quickly. You could, of course, put that value into a variable and then just use, e .g. -Mode $myMode, which is less typing, but if less is better, then no typing at all is better still.

There are a lot of tips in this article, so take some time with it.

Categories

February 2017
MTWTFSS
« Jan Mar »
 12345
6789101112
13141516171819
20212223242526
2728