Press "Enter" to skip to content

Author: Kevin Feasel

Geo-Zone Redundant Storage for SQL MI Backups

Published 2022-10-18 by Kevin Feasel

Niko Neugebauer moves the backups pretty far away:

The new Geo-Zone Redundant Storage (GZRS) backup storage option combines the best of two worlds – Geo-Redundant and Zone-Redundant storage, keeping backups safe from both regional (Geo-Redundant) and Data Center (Zone-Redundant) failures. It provides the highest availability for storage currently offered on Azure, improving recovery speed and enabling Point-In-Time Restore (PITR) of backups in the event of a zone failure.  

Geo-Zone Redundant Storage for Azure SQL Managed Instance backups provides 3 synchronous copies in different availability zones within the same primary region, plus an additional asynchronous copy within a single availability zone in the paired secondary region, as shown on the following picture: 

Click through for that picture and what it does for expected availability. Basically, a whole bunch of data centers would need to fail before you lose a backup. Or someone messes up DNS and makes everything unavailable for a day, not that that’s ever happened before with a large cloud service provider…

Comments closed

Auto Partitioning Recommendations for Oracle

Published 2022-10-18 by Kevin Feasel

Brendan Tierney checks out some recommendations:

In a previous blog post I gave an overview of the DBMS_AUTO_PARTITION package in Oracle Autonomous Database. This looked at how you can get started and to setup Auto Partitioning and to allow it to automatically implement partitioning.

This might not be something the DBAs will want to happen for lots of different reasons. An alternative is to use DBMS_AUTO_PARTITION to make recommendations for tables where partitioning will have a performance improvement. The DBA can inspect these recommendations and decide which of these to implement.

Read on to see how you can run the recommender, as well as what a recommendation looks like.

Comments closed

Checking All Metrics when Query Tuning

Published 2022-10-18 by Kevin Feasel

Grant Fritchey has some query tuning advice:

Recently, a person asked about the costs differences in an execution plan, referencing them as if they were performance measures. The key to understanding performance is to check every metric. When it comes to execution plans, I’m sure I’ve said this before, so please allow me to repeat myself.

The cost numbers shown in an execution plan, which, barring a recompile, will be the same for an execution plan or an execution plan with runtime metrics (aka, estimated and actual plans), are not measures of performance. They do not represent actual metrics. Instead, they are calculations of a theoretical actual performance measurement. So, you can’t look at two plans, with two costs, and say, “this plan will perform better.” Instead, you can say, “this plan has a lower estimated cost.” To really see performance metrics, you must measure performance.

Read on for the full set of advice.

Comments closed

Thoughts on Current 2022

Published 2022-10-17 by Kevin Feasel

Markos Sfikas, et al, recap the Current 2022 conference:

Throughout the conference, the theme of Batch vs. Streaming was apparent. Discussions covered how they can be unified, how batch processing’s performance can / must be improved for real-time applications, and more. There was even a dedicated panel discussion with Adi Polak, Amy Chen, Eric Sammer and Tyler Akidau discussing the state of streaming adoption today, and debating if streaming will ever fully replace batch. You can view some interesting points from the panel discussion in the Twitter thread from Robin Moffatt.

Click through for the full recap.

Comments closed

General Purpose Tier Azure SQL DB Performance

Published 2022-10-17 by Kevin Feasel

Reitse Eskens continues a series on comparing tiers of Azure SQL Database:

In my previous blog, I wrote about the serverless tier, the one that can go to sleep if you’re not using it for more than one hour (minimum). That tier is cheaper as long as you’re not running it for more than 25% of the time. If you need more time, go provisioned.
Another difference between serverless and provisioned is that the provisioned one gets a set number of cores whereas the serverless one has a minimum and a maximum number of cores. So this time, the blog is about the provisioned tier where you choose a fixed number of CPU’s with a fixed monthly cost.

Click through for the analysis. I’ll reiterate here that I really hope Reitse has some graphics at the end (or at least tables) which sort of lay out where the boundaries between tiers are and what the performance and cost profiles look like between them.

Comments closed

Running Jobs in Azure SQL Database

Published 2022-10-17 by Kevin Feasel

Etienne Lopes gets a job:

In SQL Server, many automated tasks are implemented via SQL Server Agent jobs. If you’re used to it, you may get a bit sad when you learn that SQL Server Agent is not present in Azure SQL Database. However if those automated tasks rely on T-SQL then they can still be easily implemented in Azure SQL Databases using elastic jobs that provide the ability to run T-SQL scripts on a schedule or on-demand

Read on to see how they work.

Comments closed

MySQL Backups via mysqldump

Published 2022-10-17 by Kevin Feasel

Lukas Vileikis begins a series on MySQL backups with the classic option:

mysqldump is one of the most popular database backup tools in the MySQL world.

The tool is prevalent partly because it’s very basic and quite powerful – mysqldump database backup tool is command line-based, very simple and very straightforward to use. As far as MySQL or its flavors (MariaDB and Percona Server) are concerned, this command line-based tool is one of the top choices for junior and senior database engineers across the world alike.

Click through to see how it works.

Comments closed

Date Ranges and Merge Interval

Published 2022-10-17 by Kevin Feasel

Daniel Hutmacher notes a performance killer:

In my last post, I found that DATEDIFF, DATEADD and the other date functions in SQL Server are not as datatype agnostic as the documentation would have you believe. Those functions would perform an implicit datatype conversion to either datetimeoffset or datetime (!), which would noticeably affect the CPU time of a query.

Well, today I was building a query on an indexed date range, and the execution plan contained a Merge Interval operator. Turns out, this operator brings a few unexpected surprises to your query performance. The good news is, it’s a relatively simple fix.

Click through for an example and some information on a fix. Hugo Kornelis also adds some good insights in the comments.

Comments closed