A Fine Slice Of SQL Server
Salman Ahmed gives us a 10,000 foot view of two topics:
When it comes to managing large and complex databases in PostgreSQL, an important decision you’ll face is how to optimize your data storage and retrieval strategies. Two common techniques for improving database performance and manageability are indexing and partitioning in PostgreSQL.
Read on for a quick overview of each topic, including the variety of index types and partitioning strategies available.
Comments closedJosephine Bush builds on prior work:
Yes, you still need to do some work to maintain indexes in Azure SQL Database. This post will walk you through setting up statistic updates and index maintenance using Terraform.
Thanks to Tracy Boggiano for her directions for setting up the runbooks. If you want to do this manually instead of with Terraform, Tracy’s post walks you through it step by step. I only modified the role assignment so it had read to the entire subscription level to loop through every DB in the subscription.
Thanks to Kendra for blogging about index maintenance in Azure SQL. Her post helped me decide on index maintenance thresholds.
Click through for a link to Josephine’s GitHub repo and a walkthrough of how it all works.
Comments closedHenrietta Dombrovskaya continues a series on Postgres:
What is an index? One might assume that any person who works with databases knows what an index is. However, a surprising number of people, including database developers and report writers and, in some cases, even DBAs, use indexes, even create indexes, with only a vague understanding of what indexes are and how they are structured. Having this in mind, let’s start with defining what is an index.
Since there are many different index types in PostgreSQL (and new index types are constantly created) we won’t focus on structural properties to produce an index definition. Instead, we define an index based on its usage.
Indexing is one area in which SQL Server and Postgres differ, as SQL Server relies on clustered indexes for storage and “default” operations, whereas Postgres has a different model.
Comments closedBrent Ozar answers a question:
I got an interesting request for consulting, and I’m going to paraphrase it:
We were using Azure SQL DB with automatic index tuning enabled for months. Things were going great, but… we just deployed a new version of our code. Our deployment tool made the database schema match our source control, which… dropped the indexes Azure had created. How do we get them back?
Read on for Brent’s answer.
Comments closedAdron Hall gives us a tour of indexes in several relational database management systems:
1. Structure of the Craft: Dive deep, and you’d find tree-like structures, be it the B-tree or its illustrious cousins – the B+ tree and B* tree. These aren’t ordinary trees; they’re a labyrinth that efficiently guides the system to the row it seeks in a jiffy.
2. Guiding Stars – Pointers: Each entry in this labyrinth isn’t just a dead end. It carries a key value and – wait for it – a pointer. Think of it as a magical compass pointing directly to the treasure, or in this case, the row in the table.
3. Supercharged Searches: Now, imagine sifting through a library without a catalog – agonizingly slow, right? That’s how a database without an index feels. But bring in an index, and suddenly even the vastest of tables become a playground of swift searches.
Read on for an overview of what purposes indexes fulfill in these data platforms. I use “purposes they fulfill” rather than “types of indexes” because there are a couple entries on the list which are not, strictly speaking, actual types of indexes.
Comments closedScott Klein continues a series on index maintenance in Azure SQL Database:
It’s finally here: the third and final blog post about Azure automation. The first blog covered how to automate Azure using Runbooks, the second blog post showed how to do it using Azure Functions, and this blog post will cover how to do it using Azure Elastic jobs.
To be fair, I titled this blog “Automating Azure with Elastic Jobs”, but Elastic Jobs isn’t part of Azure Automation, so please don’t get confused. The goal with this is to demonstrate how to automate some Azure database DBA tasks.
Read on for a brief primer on elastic jobs and how to use them.
Comments closedKendra Little gives an answer:
Have you ever received advice that was technically correct, but which was delivered in such a way that it was too hard to understand?
I think of this as “accidental bad advice,” because it leads to confusion. There’s a LOT of accidental bad advice out there on index maintenance for SQL Server and cloud versions like Azure SQL, even in the official documentation.
In this post I’m answering a common index maintenance question, and we’re going to keep it simple.
The answer is essentially the same as it would be on-premises: yes, but perform index maintenance when it is appropriate. Read on to learn what that means in this case.
Comments closedJose Manuel Jurado Diaz shares a note:
During a remote session with a customer, a comparison between the source and target databases revealed a discrepancy in the indexes. Specifically, while the source database contained three types of indexes (PK_xxx, IX_xxx, and NCI_xxx), only the manually created PK_xxx and IX_xxx indexes were visible in the target database after restoration. The absence of NCI_xxx indexes prompted an investigation into the BACPAC export process.
This meant that the BACPAC file didn’t have the set of recommended automatic indexes. Click through for the explanation, though I’m not sure there’s a good way to fix it, short of manually scripting out those automated indexes yourself.
Comments closedHarshvardhan Singh has a few tips for us:
As with the other databases, indexing is the first go-to option to improve query performance. The same is the case with Cosmos DB as well. Below are a few points which you can leverage to optimize the indexing strategy for Cosmos DB.
Indexes are kind of similar to indexing in SQL Server in intent, though different enough in implementation that you’ll want to read up on them. Harshvardhan also includes some tips around data modeling and querying data.
Comments closedAndrea Allred doesn’t want that index to resume:
It had been building for about 10 minutes, when I realized I had the fields in the wrong order. No big deal, I just canceled it, corrected it and tried to start it again. “Tried” was the keyword here. I got an error because I had set RESUMABLE = ON but hadn’t properly cleared it out of the system. I started to furiously dig through articles about how to clear out an index that was resumable but had been canceled.
Read on to see what Andrea found.
Comments closed