Press "Enter" to skip to content

Category: Administration

Reading a SQL Server XML Deadlock Report

Published 2025-01-09 by Kevin Feasel

Stephen Planck reads a report:

SQL Server includes an Extended Events session called system_health, which runs by default and, among other things, captures information about deadlocks as they occur. When two or more sessions block each other in such a way that no progress can be made (a deadlock), SQL Server chooses one session as the “victim,” rolls back its transaction, and frees resources so other sessions can continue. By reviewing the deadlock report in the system_health session’s XML output, you can see precisely why the deadlock happened and identify which queries or procedures were involved.

Below is a walkthrough of how to interpret a sample XML deadlock report, followed by a brief note on how to access this output.

Read on for that walkthrough.

Comments closed

The Use Case for Memory-Optimized tempdb Metadata

Published 2025-01-08 by Kevin Feasel

Ben Johnston takes us through a scenario:

I recently had an interesting production SQL Server issue that turned out to be very easy to fix. The fix doesn’t fit every workload or server, but for the limited use cases described below, it’s a simple configuration change. The general best practice for server level configurations is to leave things at default, unless there is a specific reason for changing the settings. This will outline a use case for using memory-optimized tempdb metadata.

This covers a very specific scenario and workload. You won’t use this on a typical server, which is why it isn’t a default setting. You will see this for very specific server workloads, those with many transactions and high temp table usage. Most systems can better use the memory for the regular workload instead of optimizing tempdb metadata, so don’t use this as a default setting for your servers.

Click through for the scenario.

Comments closed

Postgres 18 Backend I/O Statistics

Published 2025-01-08 by Kevin Feasel

Bertrand Drouvot looks at some new statistics that will be available in Postgres 18:

It means that, thanks to the new pg_stat_get_backend_io() function, we can see the I/O activity in each backend (in addition to the overall aggregate still available through the pg_stat_io view).

Click through for some examples of what you can see and how it works. This seems like it’d be pretty helpful in a clustering scenario to make sure that you’re getting the load balancing that you expect.

Comments closed

Setting RCSI on a Database in an Availability Group

Published 2025-01-07 by Kevin Feasel

Haripriya Naidu makes a change:

I was working on modifying isolation level of database from Read Committed to Read Committed Snapshot(RCSI) and had to get exclusive access to the database. After letting the application team know about it and having stopped their processes, I tried to set the database to SINGLE_USER but it errored out.

It turns out that you cannot set a database to single user mode if it is in an availability group or part of database mirroring. Nonetheless, there is still a way to make this change. Read on to learn more.

Comments closed

Bulk Inserts and High Unused Space in SQL Server Tables

Published 2024-12-27 by Kevin Feasel

Vitaly Bruk works through an issue:

High allocated unused space is storage assigned to a SQL Server table that isn’t used. This condition often indicates internal fragmentation. Free space is present within allocated pages. Such fragmentation leads to inefficient storage and can degrade database performance.

Read on for an explanation of the issue, followed by a real-world situation whose ultimate cause was bulk insert operations.

Comments closed

The Benefits of Read Committed Snapshot Isolation

Published 2024-12-23 by Kevin Feasel

Haripriya Naidu gets optimistic:

In this article, we are going to take a look at how write operations block read operations under default isolation level – Read Committed and how to avoid this using the optimistic isolation level – Read Committed Snapshot.

I do wish that this were the default for new databases in SQL Server, like it is in Azure SQL Database.

H/T Brent Ozar’s newsletter for pointing me in Haripriya’s direction.

2 Comments

Cloning Schemas in Snowflake

Published 2024-12-19 by Kevin Feasel

Kevin Wilkie makes a smaller clone:

One table at a time. Now I know what you’re thinking “Copying some of my databases would take forever…”

Well, don’t fret, my friends! Snowflake has a way!

Click through to see how you can clone an entire schema, including relevant tables, views, functions, procedures, etc. Kevin also specifies what types of objects do not copy, so check that out.

Comments closed

Useful PostgreSQL Administrative Queries

Published 2024-12-19 by Kevin Feasel

Shane Borden shares some queries:

In the spirit of the holiday season, I thought I would write a quick post regarding some of my favorite queries that I use on a day to day basis working on Postgres. Some of these queries I have developed and others were found on the internet (hat tip to those who have previously posted) and further refined.

Click through for several useful queries, as well as a link to a GitHub repo that Shane maintains, containing plenty more.

Comments closed