A Fine Slice Of SQL Server
Kevin Wilkie gets down to tuning:
In our last post, we talked about some of my favorite queries I use in Snowflake to see various items of interest – such as finding the worst-performing queries. For today’s post, though, I want to talk about performance tuning.
Yes, you read that right. We’re going to use query history to do some fun performance tuning.
Click through for two queries that can help you find what you may need to tune.
Comments closedFor quite a while, I have been extremely interested in data visualization. Over the last few years, I have been focused on ways to visualize graph databases (regardless of where the data comes from Using force directed graphs to highlight the similarities or “connected communities” in data is incredibly powerful. The purpose of this post is to highlight the recent work that the Kusto.Explorer team has done to visualize graphs in Azure Data Explorer database with data coming from a Fabric KQL Database.
Note: The Kusto.Explorer application used to visualize the graph is currently only supported on Windows.
I’ll just sit here in the corner with my Linux laptop, not jealous or anything.
Comments closedGilbert Quevauvilliers creates a notebook:
I was recently working with a customer where they had more then 100 app workspaces and I was running into some challenges when using the Scanner API in Power Automate.
I then discovered this blog post where they detailed how to download the Scanner API data (DataXbi – admin-scan.py), it was not quite in the format that I needed, so below is my modified code.
The reason that I am downloading the Scanner API into a JSON file is that I find it easier to extract the data that I need using Power BI Desktop.
Click through for the code and how it all works.
Comments closedChad Callihan gets things moving after a few 1s without enough 0s clog up the pipe:
Keeping an Always On Availability Group healthy is crucial, and seeing a non-synchronizing database in an Always On High Availability Group can give you a sinking feeling (pardon the pun). Disregarding the reason for the syncing issue, there are a few ways to resume syncing and get your setup back in the green.
Let’s look at resuming using the SSMS GUI and running a SQL statement.
Read on for the process. I appreciate that Chad also includes the T-SQL operation to do this.
Comments closedKendra Little troubleshoots an error:
At times when shrinking a data file in a SQL Server or Azure SQL Managed Instance/Database, shrink operations may persistently fail with the error:
Msg 1119, Level 16, State 1, Line 11 Removing IAM page ([filenumber]:[pagenumber]]) failed because someone else is using the object that this IAM page belongs to. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
There’s not much documented on this error anywhere that I can find, so I’m sharing my experience with this error.
Click through to see how Kendra was able to get around this issue.
Comments closedSemab Tariq continues a series on internals by covering write-ahead logging:
Write-Ahead Logging (WAL) is a critical feature in PostgreSQL that ensures data integrity and durability. It records changes to the database before they are applied, allowing PostgreSQL to recover data and restore the database to its most recent state in case of a crash or failure.
Before the introduction of Write-Ahead Logging (WAL) in PostgreSQL, PostgreSQL relied on a simpler mechanism for ensuring data integrity, which was less robust and did not support advanced features like point-in-time recovery and replication.
Read on for a bit of history, how write-ahead logging has changed over the years, and the basics of how it works. Write-ahead logging is a common feature in major relational databases and serves a critical purpose for database consistency. There are also some sneaky performance gains you can get out of it.
Comments closedBen Johnston spins in circles:
Welcome back to my R for SEO series. We’re in the home stretch now, with part seven. Today, we’re going to be looking at different ways that we can run functions or commands over a series of elements using the various kinds of loops that exist in R.
If you’ve followed along so far, or you’ve tried some experimentation of your own, you’ve probably encountered loops and applys along the way. I know early on in my R journey, it very much seemed like pot luck as to which apply I should use, or whether a loop was easier, so hopefully today’s piece will start to clear that up for you a little.
I know that most programming courses cover these elements earlier, but for me, it really didn’t click until I’d learned more about the other areas we’ve covered in this series, so that’s why I’ve placed it here.
Read on for examples of For loops and While loops, as well as breaking conditions.
Ben also talks about loops versus using the apply() series of functions (or equivalent map() functions in the purrr library). I tend to lean heavily on using the mapping function approach when there are no side effects, and use for loops when there are. H/T R-Bloggers.
Steven Sanderson looks for the pattern:
Hello, fellow useRs! Today, we’re going to expand on previous uses of the
grepl()function where we looked for a single pattern and move onto to a search for multiple patterns within strings. Whether you’re cleaning data, conducting text analysis,greplcan be your go-to tool. Let’s break down the syntax, offer a practical example, and guide you on a path to proficiency.
Read on for all of that.
Comments closedAndrei Lepikhov walks through an interesting scenario:
What was the impetus to begin this work? It was caused by many real cases that may be demonstrated clearly by the Join Order Benchmark. How much performance do you think Postgres loses if you change its preference of employing parallel workers from one to zero? Two times regression? What about 10 or 100 times slower?
The black line in the graph below shows the change in execution time of each query between two cases: with parallel workers disabled and with a single parallel worker per gather allowed. For details, see the test script and EXPLAINs, with and without parallel workers.
Click through for an overview of what Andrei wrote, including architectural notes. But stick around until the end to see just how difficult the challenge is to re-optimize without making performance worse in the end.
Comments closedRecently, we faced a requirement to upgrade large number of Azure SQL databases from general-purpose to business-critical.
As you’re aware, this scaling-up operation can be executed via PowerShell, CLI, or the Azure portal and follow the guidance mentioned here – Failover groups overview & best practices – Azure SQL Managed Instance | Microsoft Learn
Given the need to perform this task across a large number of databases, individually running commands for each server is not practical. Hence, I have created a PowerShell script to facilitate such extensive migrations.
Click through for the scenarios Sakshi tested, some important considerations, and the script itself.
Comments closed