Press "Enter" to skip to content

Author: Kevin Feasel

SQL Server Health Checks with SQLMonitor

Published 2022-09-19 by Kevin Feasel

Ajay Dwivedi performs a server health check:

Working as a DBA, I often get pulled into issues where application teams complain about “database server is slow”.

This general statement “server is slow” requires an equally robust approach that can help us figure out if there is an issue with CPU, memory, io, or regressed query. At this point, I prefer to use my SQLMonitor dashboard. This tool is entirely free and open source, and can be deployed on SQL Server 2014+ irrespective of any environment or edition.

Read on to see how it works and check out the GitHub repo as well as a one-off script you can run if you don’t have SQLMonitor set up.

Comments closed

Using EvaluateAndLog to Diagnose DAX Performance Problems

Published 2022-09-19 by Kevin Feasel

Chris Webb does a bit of sleuthing:

The Switch function is often a cause of performance problems in DAX. Last year a number of optimisations were made that fixed the most common issues (and as a result you should ignore older blog posts that you may find on this subject) but some scenarios still remain where performance can be bad; Marco and Alberto wrote a great post recently outlining some of them that I encourage you to read. How do you even know whether your measure is slow because of your use of the Switch function though? Trying to read query plans, as Marco and Alberto do in their post, isn’t usually practical and commenting out branches in a Switch to see if there is a performance change can be very time-consuming. Luckily, the new EvaluateAndLog DAX function can help you diagnose Switch-related performance issues.

Click through to see how.

Comments closed

SQL Server Practices for the CIO/CTO

Published 2022-09-16 by Kevin Feasel

Kevin Hill has some high-level advice for high-level people:

As a CIO or CTO, one of your primary responsibilities is to ensure that your organization’s data is managed effectively and efficiently. To do this, you need to have a strong understanding of SQL Server best practices. In this post, we’ll discuss some of the most important best practice areas for SQL Server management.

This is a high-level discussion with items that we will explore more in future posts

All of this is stuff that you’d want to do as a DBA but this is at a higher level to make it easier for an executive to understand the why behind it.

Comments closed

Structuring an API Project in R

Published 2022-09-16 by Kevin Feasel

Jamie Owen begins a series on building APIs as R packages:

At Jumping Rivers we were recently tasked with taking a prototype application built in {shiny} to a public facing production environment for a public sector organisation. During the scoping exercise it was determined that a more appropriate solution to fit the requirements was to build the application with a {plumber} API providing the interface to the Bayesian network model and other application tools written in R.

When building applications in {shiny} we have for some time been using the “app as a package” approach which has been popularised by tools like {golem} and {leprechaun}, in large part due to the convenience that comes with leveraging the testing and dependency structure that our R developers are comfortable with in authoring packages, and the ease with which one can install and run an application in a new environment as a result. For this project we looked to take some of these ideas to a {plumber} application. This blog post discusses some of the thoughts and resultant structure that came as a result of that process.

Read on for the first post in the series, dealing with some of the prep work.

Comments closed

Using a Service Principal Account for Power BI + Dedicated SQL Pool

Published 2022-09-16 by Kevin Feasel

Dan English provides a hookup:

In this post I will go over a topic that is frequently asked about and that is using a Service Principal account with Power BI when connecting to data sources. Currently today none of the built-in connectors support this capability natively, but the SQL Server ODBC driver does support the use of a Service Principal account. The one caveat with using an ODBC driver with Power BI is that a gateway would be required once the report is published to the service.

Read on for the step-by-step process.

Comments closed

Pinging All Day in Powershell

Published 2022-09-16 by Kevin Feasel

Patrick Gruenauer wants to know if a service is up:

Recently, I have created a script that returns a beep sound when the connection is re-established. I would like to share this script here on my website.

Assume your network connectivity is broken. You wait until the connection is restored. In addition, you want to hear a sound when the connection is re-established. For this task, just use my script.

I’ve done some ping -t checks in my day, though never with a beep.

Comments closed

Deleting Rows in Order

Published 2022-09-16 by Kevin Feasel

David Fowler understand the order of things:

This was an interesting question that I was asked yesterday and something that I’d never really thought of before. Can you delete the top x number of rows based on an ORDER BY?

Why would you want to do that? Well let’s just assume that we have a ‘people’ table and some strange bug in the application has cause the need to delete the top 10 oldest females for whatever reason (I know, it’s very contrived example and not the situation that my colleague was facing but it’ll do to illustrate the point).

Click through for one way which doesn’t work and two ways which do.

Comments closed

Updates to Intelligent Query Processing in SQL Server 2022

Published 2022-09-16 by Kevin Feasel

Derek Wilson and Kate Smith dive into IQP updates:

SQL Server 2022 introduces a handful of new members to the growing Intelligent Query Processing (IQP) family. These additions range from expanding and improving various query performance feedback mechanisms to adding two new features—parameter sensitive plans (PSPs) and optimized plan forcing. In this blog, we give a general overview of these additions, with detailed feature-specific blogs to follow.

Read on to see what they have in store.

Comments closed

Lack of Training on the Edge

Published 2022-09-15 by Kevin Feasel

Pete Warden explains a phenomenon:

One of the most frequent questions I get asked from people exploring machine learning beyond cloud and desktop machines is “What about training?”. If you look around at the popular frameworks and use cases of edge ML, most of them seem focused on inference. It isn’t obvious why this is the case though, so I decided to collect my notes in a post here, so I can have something to refer to when this comes up (and organize my own thoughts too!).

Pete’s reasons make sense. I think the last one is the most important.

Comments closed

Declaring a Minimum R Version in Packages

Published 2022-09-15 by Kevin Feasel

Hugo Gruson and Maelle Salmon show how to set a minimum version of R itself in a package:

There have been much talk and many blog posts about R package dependencies. Yet, one special dependency is more rarely mentioned, even though all packages include it: the dependency on R itself. The same way you can specify a dependency on a package, and optionally on a specific version, you can add a dependency to a minimum R version in the DESCRIPTION file of your package. In this post we shall explain why and how.

Read on for that explanation, as well as a lot of depth on why you might choose a particular R version, popular R versions (at least by number of packages), and what some of the largest maintainers do.

Comments closed