Press "Enter" to skip to content

Day: July 7, 2021

Having Fun with the QDS Toolbox

Published 2021-07-07 by Kevin Feasel

Jared Poche shares a few queries with us:

The QDS Toolbox is set of tools that can help you review and store the performance related data in Query Store. This was released by ChannelAdvisor last September thanks to the hard work of a number of my coworkers.

If you aren’t experienced with Query Store, this can provide a good starting point for getting familiar with data that is available and what you can do with it. If you are experienced with Query Store, this may give you an easy way to set up customizable reports that help you find issues and see trends.

The QDS Toolbox has several components, and I intend to post about each in turn. Two new components were added to this recently by @sqlozano (https://www.sqlozano.com/), bringing the current total to eight.

Click through for a deeper dive into the Server Top Queries report.

Comments closed

Notes on Temporal Tables

Published 2021-07-07 by Kevin Feasel

Hugo Kornelis wraps up a discussion of temporal tables with miscellany:

Of course, it will be a quite common requirement to query products and their suppliers. When querying the present, you can just access the Suppliers and Products table without the “FOR SYSTEM_TIME” keyword, and the execution plan will unsurprisingly show that a regular join of the two “current” tables is used, with no reference to the history table. Nothing special. We already saw in the earlier parts that querying the present simply ignores the history table; joining does not make that different.

But what if, for instance, we want to show all data as it was valid on June 3 at noon? Well, that is also simple. We already know that we can use FOR SYSTEM_TIME AS OF” to get the rows from a single temporal table as they were at a specific time. We can use that syntax for both tables, to get the data we need:

Read on as Hugo dives into some messy problems. Temporal table queries can expand out in complexity very quickly, as this post shows.

Comments closed

Introducing the Display-Object Cmdlet

Published 2021-07-07 by Kevin Feasel

Phil Factor has a bit of Powershell for us:

How do you list all the objects and values within a PowerShell object, investigate an object or explore its structure? There must be a simple way. I used to use ConvertTo-JSON. This is fine up to a point but what if you just wish to search for strings or look for objects with a cartain name or value? What if you need their path so you can reference them? It isn’t plain-sailing. In the ISE, the intellisense will help you a lot but I want more and I want to do it in script rather than the IDE

Read on to check it out and get a copy of the cmdlet.

Comments closed

Migrating Historical Data from Azure Analysis Services to Power BI Premium Per User

Published 2021-07-07 by Kevin Feasel

Gilbert Quevauvilliers continues a series on moving to Power BI Premium Per User:

In this blog post I am looking at how to load or reload historical data in AAS and PPU and compare the differences.

It should already be noted that I am only going to compare tables where I have partitions created and enabled. The reason being for dimension tables it is typically quick and easy to reload the data by re-processing the data for the table.

Read on for the details.

Comments closed

Required Permissions for Forcing Query Store Plans

Published 2021-07-07 by Kevin Feasel

Grant Fritchey reviews minimum requirements:

I was recently asked what permissions were needed to force plans in query store. I’m sure I knew at one point, but at the moment I was asked, I couldn’t remember to save my life. So, I went and looked it up. In the interest of sharing, especially for the poor person who I left hanging, here’s what I found.

Click through for the disappointing answer.

Comments closed

Against sp_hexadecimal and sp_help_revlogin

Published 2021-07-07 by Kevin Feasel

Andy Mallon says it’s time to give up a couple of procedures:

We recently ran into some performance problems with our login sync, which is based on sp_hexadecimal and sp_help_revlogin, the documented & recommended approach by Microsoft.

I’ve been installing & using these two procedures since I started working with SQL Server, back at the turn of the century. In the nearly two decades since, I’ve blindly installed & used these procedures, first on SQL Server 2000, and then on every version since… just because that’s the way I’ve always done it. But our recent performance problems made me rethink that, and dive in to take a look at the two procedures to see if I could do better, which made me realize, OHBOY! WE CAN DO BETTER!!

Read on to understand how.

Comments closed