Press "Enter" to skip to content

Month: September 2022

Basic Tier Performance in Azure SQL DB

Published 2022-09-19 by Kevin Feasel

Reitse Eskens keeps things basic:

When you look at the documentation, the basic tier has low CPU (at max less than one), 1 to 4 IOPS per DTU (translating to 5-20 IOPS in total), a latency of 5ms read and 10 ms write and a maximum of 7 days backup retention. Even though it’s advertised as a production database, I wouldn’t store essential data there. However, I can use it for metadata storage in a Data Factory or Synapse Analytics environment.

About the IOPS, one IOP is a read of a disk cluster, usually 4 Kb. In this case, this means that the database is capable of reading 20 to 80 Kb of data per second.

When they say Basic, they aren’t kidding.

Comments closed

Importing Delta Tables into a Synapse Dedicated SQL Pool

Published 2022-09-19 by Kevin Feasel

Mark Pryce-Maher does a bit of integration:

In June, Databricks announced that they are open sourcing Delta Lake 2.0. Delta Lake is quickly becoming the format of choice in data science and data engineering.

To import Delta Lake into a Synapse dedicated SQL Pool you would need Azure Data Factory/Synapse Pipelines or Spark to handle the Delta Lake files.


This is not ideal because it adds extra overheads of complexity, time, and costs.

As an intellectual challenge, I wondered if it’s possible to import Delta Lake files directly into the dedicated SQL Pool and support features like time-travel. It turned out to be a great little project and a great way of learning about Delta Lake.

This turned out to be a bit more difficult than I would have imagined. Click through for the script and check the comments as well for a preview of upcoming attractions.

Comments closed

sp_helpExpandView

Published 2022-09-19 by Kevin Feasel

Andy Yun cleans up some nested views:

Yeah, that thing was small. But the processing was a horrific case study in T-SQL worst practices. And the architect that created it LOVED nested views (and scalar functions… and MERGE… on Standard edition).

I spent a good amount of effort trying to unravel those as part of my efforts to improve performance, and as a result, decided to create my own community tool to help with unraveling them – sp_helpExpandView.

I also occasionally deal with a group loving nested views and now I’m going to need to use Andy’s stored procedure because tracking that on my own gets really painful.

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 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

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

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

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

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

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