Press "Enter" to skip to content

Category: Administration

When Statistics Updates Happen

Published 2023-06-28 by Kevin Feasel

Matthew McGiffen gives us the numbers:

SQL Server has had the ability to automatically update statistics since version 7.0. Nonetheless for a long part of my career working with SQL Server, whenever a performance issue raised its head everyone’s knee-jerk response would be “Update Statistics!” In most cases though the people shouting that didn’t really understand what the “Statistics” were, or what mechanisms might already be in place for keeping them up to date.

Of course SQL Server isn’t perfect and sometimes it is helpful for human intelligence to intervene. But to provide intelligent intervention one has to understand how things work.

Read on to learn what triggers automatic stats updates in various versions of SQL Server.

Comments closed

Loading Multiple Extended Events Files in SQL Server

Published 2023-06-27 by Kevin Feasel

Jose Manuel Jurado Diaz reviews the tapes:

As the volume of data grows, SQL Server creates multiple extended event files to store the captured information efficiently. These files are usually saved in a designated target folder. However, when it comes to loading and analyzing these files, administrators often face the challenge of dealing with multiple files individually. Manually loading each file can be time-consuming and inefficient, especially when dealing with a large number of extended event files.

Read on to see which function you can use to read multiple Extended Events files and how it works.

Comments closed

Loading Multiple Audit Log Files in Azure SQL DB

Published 2023-06-26 by Kevin Feasel

Jose Manuel Jurado Diaz can’t stop at one:

In Azure SQL Database, the auditing feature enables you to track and monitor database activities, providing valuable insights into the actions performed on your database. One of the key components of auditing is the audit log files, which store the recorded data.

However, when dealing with a large number of audit log files stored in a blob storage container, loading them into Azure SQL Database can be a challenging task.

This article explores a workaround using the sys.fn_get_audit_file function to load multiple audit log files without being able to define a pattern such as *.xel.

Note that, even though the example is for Azure SQL Database, the function is built into SQL Server, SQL Managed Instance, and Synapse dedicated SQL pools as well and works the same way.

Comments closed

Choosing a Load Balancing Option in Azure

Published 2023-06-23 by Kevin Feasel

Santosh Hari looks at the options:

Azure docs have a great page on the various load balancing options in Azure that even has an awesome flowchart summing up the choices. However, not being from a networking background, combined with Microsoft’s “special” naming, combined with some sort of memory issue recalling these names from memory meant that even if I had to rely on rote memory when in conversations with customers, I would often mix up the names. For instance, confuse traffic manager and load balancer. So, I decided to understand some of the basics behind cloud load balancers to help become a more interesting conversationalist in this topic: “well actually, you should be using an app gateway there, John”.

This often isn’t in the database administrator’s purview, but Santosh does a good job of explaining the concepts and, if you’re hosted in Azure, it is good to know what’s sitting in front of your database.

Comments closed

Schema Optimization and Disk Usage in Cassandra

Published 2023-06-15 by Kevin Feasel

John Del Castillo has an after-action report for us:

Instaclustr’s automated systems are constantly monitoring the growth of Instametrics, and periodically it reaches a threshold where TechOps determines it requires more storage. 

When this happens, they contact the Engineering team to get permission to add more nodes. 

On one of these occasions, our team of experts in TechOps looked more closely at our usage of Cassandra and how it has changed over the years, to see if there were ways to further optimize it. 

And we found a way to reduce our disk usage by over 20%, with just a change to our data schema. 

Read on to see what they did and how they were able to save a good amount of disk space.

Comments closed

Methods for Monitoring MongoDB

Published 2023-06-15 by Kevin Feasel

Hadi Fadlallah does some watching:

Several utilities included with the MongoDB distribution provide statistics about instances’ performance and activity. Usually, they are used to diagnose problems and assess the functioning of a system.

The MongoDB database tools are a suite of command-line utilities for working with MongoDB. Starting with MongoDB 4.4, these tools are released separately from the MongoDB Server. To install them, we should follow the guidelines provided in the following documentation.

Click through for information on command-line tools, built-in web services, and one third-party tool for the job.

Comments closed

Clearing Backup History on SQL Server

Published 2023-06-14 by Kevin Feasel

Rich Benner reminds us to clear that backup history:

One thing we regularly come across with a new client is large system databases. Something that SQL Server should do but doesn’t is clearing up system tables. One of the more common issues is a very large backup history which causes bloat in the msdb database. Let’s find out how to clear backup history.

These tables store information about backups that you have taken, which is extremely useful … for a time. After that useful time, it’s just unnecessary data that is taking up space on your server. There’s a number of related tables that store backup history, and it’s not simple to delete data from them all. Most don’t have dates stored in the table so you have to join them together and make sure you delete in the correct order.

Read on for a built-in procedure, as well as a warning. It’s interesting to see, sometimes, just how much space msdb is taking up with stuff people don’t realize. Backup history is usually one of the offenders, along with SQL Agent history and (especially on pre-2016 instances) SSIS history.

Comments closed

Missing Index Hints and Index Rebuilds

Published 2023-06-13 by Kevin Feasel

Forrest McDaniel abuses that poor server:

The prod version of this issue manifested as 1-3 minutes of SCH-M blocking for index ops, yet only on a single server; I couldn’t replicate it anywhere else. Of course, bad blocking meant nasty timeouts, and that in turn meant I was reluctant to run live diagnostics. Better to just stop index rebuilds. One does not take down production in order to test weird SQL behavior.

An important clue came from a tiny table with a rebuild, and is visible in the whoisactive screenshot above. Why would such a small rebuild need so much CPU? That worker was definitely doing something – the question was what.

Read on to find out the answer, a repro script (that you should not run in your own production environment!), and what you can do about it.

Comments closed

Roles and Domains in Microsoft Fabric

Published 2023-06-09 by Kevin Feasel

Marc Lelijveld explains two key concepts:

Microsoft Fabric is out there for a few weeks now. With the release of Fabric, a new concept in line with data-mesh architectures became available in Fabric, or Power BI if you will. With the introduction of Domains, we have a new level of controls added next to existing roles. In this blog I will further elaborate on the levels of control that are available today and provide a clear overview of these different levels.

There’s going to be a bit of nomenclature adjustment for people who have spent most of their time in Synapse or other platforms moving to Fabric. If you’ve already spent most of your time in Power BI, this shift is probably a little easier.

Comments closed