Press "Enter" to skip to content

Month: November 2019

Power BI Paginated Reports on the Cheap

John White shows how you can get paginated reports in Power BI at a lower cost:

In our scenario, we have a paginated report that uses a published Power BI dataset as a data source. as of this writing, there is no API call available to render a paginated report on demand, so we will rely on the scheduled subscription capability. in order to minimize the cost of the solution, we want the dedicated capacity to run as little as possible.

The solution will consist of an Azure logic app, and Power BI paginated report scheduling. An Azure logic app uses the same set of actions that a Flow in Power automate does, but is a little more flexible in its permissions model.

Read on for the step-by-step instructions.

Leave a Comment

SQL Server 2019 Installation Enhancements

Aaron Bertrand looks at a couple things you can configure on installation with SQL Server 2019:

One of the long-standing defaults in SQL Server has been the maximum degree of parallelism (MAXDOP), which has always been 0, meaning use (up to) all cores when the engine believes that will help. For many OLTP workloads, 0 is not the optimal setting, and you may want to use a different number depending on the behavior of your workload. I don’t want to belabor the thought process here, but this will be based on settings like the number of cores exposed to SQL Server, whether they are divided into NUMA nodes, and if there are other instances, applications, or services running on the same Windows Server. Microsoft’s guidelines are published here.

Aaron shows us how to set MAXDOP as well as min and max server memory for our new instance. It’s nice to see these types of additions to the setup process—that makes it a bit more likely that the DBA who installs instances only occasionally doesn’t forget to set these afterward.

Leave a Comment

Important Assumptions with Linear Models

Sebastian Sauer takes us through two of the most important assumptions of linear models:

Additivity and linearity as the second most important assumptions in linear models
We assume that \(y\) is a linear function of the predictors. If y is not a linear function of the predictors, we cannot expect the model to deliver correct insights (predictions, causal coefficients). Let’s check an example.

Read on to understand what this means, as well as the most important assumption.

Leave a Comment

Error Log Files and SQL Server Upgrades

Mike Hays points out that setting the number of error log files is something which resets on SQL Server upgrade:

This morning when working with one of my SQL Server servers, I went looking for an error log from last August.  After finding that I was only able to go back six days, I checked the directory that stored SQL Server’s error logs,  and noticed a gap in the logs from October to last March.

When I checked the configuration for the error logs in SQL Server Management Studio, I saw that the setting was configured at the default setting of 6 error logs for history.

Read on to understand why this happened.

Leave a Comment

Puzzles with Powershell

Jana Sattainathan takes us through solving a couple of problems with Powershell:

As I said before, I am a regular at my local PowerShell user group. I keenly look forward to our monthly meetings. We share a lot of PowerShell code/tips/tricks among ourselves. It is a small but close-knit group. Yesterday, one of the members, Jason Walker came up with coding puzzles for the group. This blog post is about the puzzles Jason created for solving using PowerShell. This is the kind of simple stuff that keeps the meetings interesting and fun (besides the Pizza!).

My solutions may not be the most elegant or concise but they work and were created on the fly during the meeting!

Read on for the four puzzles as well as solutions.

Leave a Comment

What’s New with Query Store in SSMS 18.4

Erin Stellato takes us through changes to Query Store components in SQL Server Management Studio 18.4:

Last week the SQL Server Tools team released a new version of Management Studio, 18.4, and it dropped in the middle of my Query Store full day pre-con on Monday. Those two are related. There were some changes in SSMS 18.4 specific to Query Store, and for a few seconds over lunch I thought about downloading the new version and running it for my afternoon demos. But…I didn’t want to anger the demo Gods, so I stuck with 18.3.

Read on for info on the changes.

Leave a Comment

SQL Server Trends Worth Watching

Grant Fritchey follows up on a Kevin Hill tweet:

There are a million things to learn about in our rapidly shifting technological landscape, but I think this assessment, especially the way it was put, “no longer justify ignoring” really nails some of the fundamentals.

Let’s talk about why you can no longer ignore Docker, Git and DBATools either.

If you’re a DBA and aren’t familiar with Docker, Git, or DBATools, that’s a pretty good trio of things to spend some time learning. You can survive without them, but you’re more likely to thrive if you know them.

Leave a Comment

Stack Overflow DB, Columnstore Edition

Erik Darling has started a new series. Part one is the intro:

I really wanted a version of the Stack Overflow data dump that was all clustered column store. In SQL Server 2016, that didn’t really work because of restrictions around MAX data types. In 2017 it did, but… If your data warehouse has a bunch of max data type columns, that’s bad and you should feel bad.

The problem here is that once you drop out the “big” columns (AboutMe from Users, Text from Comments, Body from Posts), the entire ~300GB database compressed down to about 6GB. That means if we want a realistically sized data warehouse, we’d need a “Make Big” script, like people used to use for Adventure Works before it went out of business.

Part 2, like a noble spirit, embiggens the smallest man:

One thing I’d love feedback on is advancing dates. Right now, the script doesn’t do that at all. I thought of different ways to handle it, but didn’t like any of them, mostly because of how it might mess with the partitioning function I’m using. I felt like I was overthinking it quite a bit, and decided to leave dates as-is, and only increment User and Post Ids.

A quick note: This script assumes that a database called StackOverflow will be the source of the loads. If you need to use a different version, that’s a manual change. I didn’t want to go down the dynamic SQL route here until I gauged popularity.

If you want to play along at home, you can grab the data dump from archive.org.

Leave a Comment

Building a SQL Cluster: Availability Groups

Ryan Adams continues a series on building a SQL Server cluster as a lab:

You are going to create a multi-subnet Availability Group in Part 3 of our series on how to build a SQL Cluster Lab. First you give the Cluster Name Object rights in Active Directory and then install your AG. You will also ensure the AG endpoints are using the dedicated network created for them in Part 1. Here are links to the other articles in the series.

Click through for the video.

Leave a Comment