Press "Enter" to skip to content

Author: Kevin Feasel

Interacting with Microsoft Graph API via Synapse

Published 2022-10-03 by Kevin Feasel

Paul Hernandez starts a new series:

In this and the next post I want to show you how to connect to the Microsoft Graph API, request some data, process it and store it in a database using Synapse Analytics. 

This first post presents a sample use case, briefly introduces the Graph API, how to create a linked service to it, and how to start querying data. In the next post a sample  Synapse pipeline will be described. The pipeline grabs some data and copies it into some target tables. Finally, I will create a sample query to showcase the newly imported data. 

Because there’s some potential confusion to people, Graph API is completely different from the idea of graph databases.

Comments closed

Premium Azure SQL DB Performance

Published 2022-10-03 by Kevin Feasel

Reitse Eskens is moving on up:

The standard tier starts at 125 DTU’s and goes up to 4000. DTU’s are made up from a magic mix of CPU, memory, read iops and write iops. An iop (Input Output oPeration) should be a 4kb (disk cluster size) read or write. 125 DTU translates to 500 Kb/sec up to 32.000 Kb/sec. As we’re used to datapages which are 8Kb in size, you could say these databases are able to pull 62 to 4.000 pages per second from disk. When there are simultaneous writes, you’ll share the performance. At least that’s my interpretation of the IOP. For the DTU part, I’m still struggling to get a good grip on what it exactly is, beyond the magic mix.

It’s also a good idea to compare this to what the Standard tier has to offer. The general data patterns look similar with respect to elbows but the magnitudes are quite different, with Premium P1 starting out around Standard S4 in the test for insertion but more like S3 for selects.

Comments closed

Troubleshooting High Non-SQL CPU Utilization

Published 2022-10-03 by Kevin Feasel

Ajay Dwivedi finds out it wasn’t the database:

Since SQL Server is not a cheap application and would cost a lot of money if we need to scale it horizontally. So it is not a good practice to allow any other resource-consuming processes to run alongside with SQL Server.

In this blog, we are going to troubleshoot a high CPU scenario when the CPU issue is caused by non-SQL Server process. Let’s say, I receive a call from the monitoring team where the application team is complaining about a “slow” SQL Server.

Ajay first uses SQLMonitor to perform this troubleshooting and then shows how to do this without SQLMonitor.

Comments closed

Creating Repeatable Test Data

Published 2022-10-03 by Kevin Feasel

Louis Davidson repeats himself:

In order to test graph structures, I needed a large set of random data. In some ways, this data will resemble the IMDB database I will include later in this chapter, but to make it one, controllable in size and two, random, I created this random dataset. I loaded a set of values for an account into a table and a set of interests. I wanted then to be able to load a set of random data into edge, related one account to another (one follows the other), and then an account to a random set of interests.

In this article, I will discuss a few techniques I used, starting with the simplest method using ordering by NEWID(), then using RAND() to allow the code to generate a fixed set of data.

There’s a lot of code needed to do it but if it’s something you’ve got to do, that’s the cost of doing business.

Comments closed

Oracle Machine Learning for R in Oracle Autonomous Database

Published 2022-09-30 by Kevin Feasel

Brendan Tierney notes an update to Oracle Autonomous Database:

Oracle Machine Learning for R (OML4R) is available on Oracle Autonomous Database. Finally. After waiting for way, way too long we can now run R code in the Autonomous Database (in the Cloud). It’s based on using Oracle R Distribution 4.0.5 (which is based on R 4.0.5). This product was previously called Oracle R Enterprise, which I was a fan of many few years ago, so much so I wrote a book about it.

I’m curious how well this has done compared to SQL Server ML Services—my experience is that getting ML Services adoption has been a struggle.

Comments closed

Calculating Compound Interest in Power BI

Published 2022-09-30 by Kevin Feasel

Koen Verbeeck does the math:

Recently I had an interesting use case where I had to a compound calculation in Power BI. You can compound an interest rate for example, where you get a certain rate on your savings. Let’s say 1% (which is at the time of writing ridiculously high, but bear with me). After 1 year, you get 1% interest on your money. If you leave that (small) amount of money on your savings account, you’ll get 1% after another year on the original amount + the interest amount of the previous year. This means you’re money grows exponentially (sounds more exciting than it is in reality).

Read on for an example of creating what-if parameters around compound interest rates.

Comments closed

Testing API Packages in R

Published 2022-09-30 by Kevin Feasel

Jamie Owen does some testing:

This blog post is a follow on to our API as a package series, which looks to expand on the topic of testing {plumber} API applications within the package structure leveraging {testthat}. As a reminder of the situation, so far we have an R package that defines functions that will be used as endpoints in a {plumber} API application. The API routes defined via {plumber} decorators in inst simply map the package functions to URLs.

Jamie covers a lot of testing ground in that post as well, so check it out.

Comments closed

SQL Server Backup and Restore Operations for S3

Published 2022-09-30 by Kevin Feasel

Hugo Queiroz shows off something new in SQL Server 2022:

Backup and restore to simple storage service (S3)–compatible object storage is a new feature introduced in SQL Server 2022 that grants the user the capability to back up or restore their databases using S3-compatible object storage, whether that be on-premises, or in the cloud.

There are some differences from other backup operations, so you should definitely read up on it before using it. One interesting side benefit I got to try out recently is that Pure Storage’s FlashBlade product has an S3 API, allowing you to use that interface for backup/restore operations as well as data virtualization.

Comments closed

Finding Outdated Powershell Modules

Published 2022-09-30 by Kevin Feasel

Jeff Hill has a script for us:

In the world of PowerShell, some things move super fast while others may stay the same for years. If the module you are using does what you want with no issues, you may not think to see if a newer version is available. The PSReadline module is a good example. The version I was on worked great and it did what I wanted. I had no idea there was so much more that had been done.

Click through for a script which checks the Powershell Gallery for updates, though it does not force install those updates.

Comments closed

High Memory Grants in SQL Server

Published 2022-09-30 by Kevin Feasel

Ajay Dwivedi tracks down memory grants:

Very often, specially on mixed workload enviroment where server deals with oltp & olap queries together, concurrent sessions allocated with High Memory Grants cause entire SQL Server workload to suffer.

To detect such a situation, I like to watch my critical metrics dashboards. For example, dashboard Monitoring – Live -All Servers of free SQLMonitor tool highlights all these metrics.

Click through for an example of the dashboard as well as a diagnostic query which helps find high-memory grant queries.

Comments closed