Azure PostgreSQL built-in Health Recovery

During my testing of CLUSTER and PG_REPACK on Azure PostgreSQL, I found that Azure PostgreSQL has an automatic health recovery mechanism. This feature drops running processes on the Postgres database when it detects an unhealthy server condition, such as full storage.

Enable Audit

SHOW shared_preload_libraries;

-- Azure Portal -> Server parameters -> shared_preload_libraries -> Add PGAUDIT

SELECT name

FROM pg_available_extensions

WHERE name = 'pgaudit';

SHOW azure.extensions;

-- Azure Portal -> Server parameters -> azure.extensions -> Add PGAUDIT

CREATE EXTENSION IF NOT EXISTS pgaudit;

SHOW pgaudit.log;

-- Azure Portal -> Server parameters -> pgaudit.log -> ALL

SHOW log_line_prefix;

-- Azure Portal -> Server parameters -> log_line_prefix -> %t-%c-user=%u,db=%d,app=%a,client=%h

CLUSTER

-- This command is metadata only; it set the default index for future CLUSTER operations. It does not actually re-cluster the table.

ALTER TABLE curves.xc_curve

CLUSTER ON xc_curve_pk;

-- verify the clustered index

SELECT

    c.relname AS table_name,

    i.relname AS index_name

FROM pg_class c

JOIN pg_index x

    ON c.oid = x.indrelid

JOIN pg_class i

    ON i.oid = x.indexrelid

WHERE x.indisclustered

  AND c.oid = 'curves.xc_curve'::regclass;

/*

 * This command is Session wide only (for the duration of that specific connection).

 * It will not apply to the whole server or all users.

 */

SET maintenance_work_mem = '8GB';

SHOW maintenance_work_mem;

\timing on

/*

 * For a 1.8 TB table, I would typically want at least 2–2.5 TB of free storage beyond the current database size before attempting a CLUSTER, otherwise there is a real risk of running out of space and having the operation fail.

 */

CLUSTER VERBOSE curves.xc_curve USING xc_curve_pk;

-- Check CLUSER running

SELECT

    pid,

    usename,

    application_name,

    state,

    wait_event_type,

    wait_event,

    now() - query_start AS runtime,

    query

FROM pg_stat_activity

WHERE query ILIKE 'CLUSTER%';

PG_REPACK

CREATE EXTENSION pg_repack;

 -- PGSQL pg_repack

pg_repack -t curves.xc_curve -d axioma -h us5-dp-d-pg1.postgres.database.azure.com -U breakglass -k

-- Check PG_REPACK running:
SELECT pid,

       usename,

       application_name,

       state,

       wait_event_type,

       wait_event,

       query

FROM pg_stat_activity

WHERE application_name LIKE '%repack%';

Health recovery

An internal Azure process executed the pg_terminate_backend command to kill the CLUSTER connection.

/*
 * SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE backend_type = 'client backend' AND usename<> 'azuresu' AND usename<> 'replication' AND pg_catalog.pg_is_in_recovery() = false and (SELECT setting = 'on' FROM pg_settings WHERE name = 'default_transaction_read_only')

 */

 

Analyzing PostgreSQL Query Performance

To evaluate the performance of a PostgreSQL query, we can utilize the EXPLAIN command. In pgAdmin, upon opening a query window, you will find two buttons located on the top menu bar.

Core Comparison

	EXPLAIN	EXPLAIN ANALYZE
Execution	Does not run the query.	Executes the query completely.
Speed	Instantaneous (takes milliseconds).	Takes as long as the query itself to run.
Data Safety	100% safe for all commands.	Modifies data if used on INSERT/UPDATE/DELETE.
Metrics	Theoretical estimates (cost, rows, width).	Real runtime stats (actual time, actual rows, memory).

Because EXPLAIN ANALYZE actually runs the query, it is important to enclose the entire script with START TRANSACTION and ROLLBACK commands when analyzing DML statements. If you do not take this step, you might unintentionally modify the table data while analyzing the query. To avoid such unintended changes, always use these transaction commands during your analysis.

To analyze your query in the pgAdmin query window, select the START TRANSACTION, EXPLAIN ANALYZE, and the specific query, then hit F5 to run it, such as:

After that, you can highlight the complete output, copy it, and insert it into a text file.

Finally, you should highlight a ROLLBACK command, then execute it.

Be aware that executing the full set of commands (START TRANSACTION, EXPLAIN, your SQL query, ROLLBACK) together will lead to the ROLLBACK canceling the EXPLAIN output, which means you won't be able to access the information you need.

Additionally, you can retrieve the BUFFERS, COST, and TIMING data from the real execution plan by running EXPLAIN (ANALYZE, BUFFERS, TIMING, COSTS).

ANALYZE BUFFERS is the gold standard for query optimization because it reveals the exact I/O footprint of a query. While ANALYZE executes the query to show actual runtime and row counts, BUFFERS shows exactly how many data blocks (pages) Postgres had to read, write, Postgres handles data in blocks, where 1 block = 8 KiB by default.

Why can the same Query Text in the Query Store have multiple Query_IDs?

In Query Store, query_id is not just based on the query text. It is derived from a broader concept of a query signature, which includes additional attributes beyond the literal SQL text.

That’s why you can see the same query_sql_text mapped to multiple query_ids.

✅ Core Reason

A query_id is generated based on:

• Normalized query text (parameterized form)
• Context settings (set_options)
• Database settings / environment affecting compilation

So even if the raw text looks identical, SQL Server treats them as different queries internally.

🔍 Main Cause

1. Different SET Options (set_options)

SELECT DB_NAME(), query_id, OBJECT_NAME(Q.object_id), T.query_sql_text, q.last_execution_time, T.query_text_id, Q.context_settings_id

FROM sys.query_store_query Q

JOIN sys.query_store_query_text T ON Q.query_text_id = T.query_text_id

WHERE Q.object_id = OBJECT_ID('<Stored Procedure/Trigger name>')

AND T.query_sql_text LIKE '%<query_text>%'

ORDER BY q.last_execution_time DESC;

GO

SELECT GETUTCDATE();

SELECT * FROM sys.query_context_settings WHERE context_settings_id IN (1, 6);

DECLARE @set_options INT = 4347; -- change value

SELECT name, value

FROM (VALUES

    ('ANSI_PADDING', 1),

    ('Parallel Plan', 2),

    ('FORCEPLAN', 4),

    ('CONCAT_NULL_YIELDS_NULL', 8),

    ('ANSI_WARNINGS', 16),

    ('ANSI_NULLS', 32),

    ('QUOTED_IDENTIFIER', 64),

    ('ANSI_NULL_DFLT_ON', 128),

    ('ANSI_NULL_DFLT_OFF', 256),

    ('NO_BROWSETABLE', 512),

    ('ARITHABORT', 1024),

    ('NUMERIC_ROUNDABORT', 2048),

    ('DATEFIRST', 4096),

    ('DATEFORMAT', 8192),

    ('LANGUAGE', 16384)

) AS t(name, value)

WHERE (0x000010FB & value) = value;

SELECT name, value

FROM (VALUES

    ('ANSI_PADDING', 1),

    ('Parallel Plan', 2),

    ('FORCEPLAN', 4),

    ('CONCAT_NULL_YIELDS_NULL', 8),

    ('ANSI_WARNINGS', 16),

    ('ANSI_NULLS', 32),

    ('QUOTED_IDENTIFIER', 64),

    ('ANSI_NULL_DFLT_ON', 128),

    ('ANSI_NULL_DFLT_OFF', 256),

    ('NO_BROWSETABLE', 512),

    ('ARITHABORT', 1024),

    ('NUMERIC_ROUNDABORT', 2048),

    ('DATEFIRST', 4096),

    ('DATEFORMAT', 8192),

    ('LANGUAGE', 16384)

) AS t(name, value)

WHERE (0x000000FB & value) = value;

The outcome presented below indicates that the values of query_text_id and the object_name for these two query_store entries are identical, which implies that these two query_store entries essentially refer to the same query within the same object (stored procedure/trigger).

The reason there are two query_ids for the same query is that, as indicated by the context_settings_id value, the two query_store entries are executed under different context settings (SET OPTIONs).

Table Fast Row Count

 sys.dm_db_partition_stats (Transact-SQL) - SQL Server | Microsoft Learn

SELECT

    OBJECT_NAME(object_id) AS TableName,

    SUM(row_count) AS RowCount

FROM sys.dm_db_partition_stats

WHERE index_id IN (0,1)

GROUP BY object_id

ORDER BY RowCount DESC;

If sys.dm_db_partition_stats (or sys.partitions) is giving inaccurate row counts, it’s usually not a bug—it’s due to how SQL Server maintains metadata. Here’s what’s happening and how to deal with it:

Metadata Lag

• Row counts come from internal allocation metadata, not real-time scans.
• Updates happen during:
  • checkpoints
  • index rebuild/reorg
  • statistics updates

👉 Under heavy INSERT/DELETE, the count can drift.

⚠️ Common Causes of Wrong Counts

Cause	Effect
Heavy DML (INSERT/DELETE)	DMV lag
Truncated tables	mismatch temporarily
Bulk operations	metadata delay
Disabled/rebuilt indexes	stale counts
Partition switching	incorrect totals
Heaps with forwarding records	inaccuracies

✅ How to Fix / Improve Accuracy

DBCC UPDATEUSAGE (YourDatabaseName) WITH COUNT_ROWS;

DBCC UPDATEUSAGE (Transact-SQL) - SQL Server | Microsoft Learn

Create CLR assembly in Azure SQL Managed Instance (MI)

Creating a CLR assembly in Azure SQL Managed Instance (MI) follows a similar process to on-premises SQL Server, but with key differences due to the cloud-managed nature of the service—most notably, you cannot reference local file paths.

1. Enable CLR Integration

CLR integration is disabled by default. You must enable it at the instance level using the sp_configure system stored procedure.

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

2. Produce the CREATE ASSEMBLY command including the Binary Hex Literal

Most likely, the developer will give us a .dll file. As mentioned earlier, MI cannot access the local file path. Therefore, as a DBA, you can use your own local machine, install the free SQL Server Express edition, and then use the .dll to create the assembly in your local SQL Express first. After that, generate the CREATE ASSEMBLY from your local SQL Express, connect to the MI, and execute the CREATE ASSEMBLY script there.

-- in your local SQL Express
CREATE ASSEMBLY HelloWorld

FROM 'C:\Path\HelloWorld.dll'

WITH PERMISSION_SET = SAFE;

How to generate the CREATE ASSEMBLY from your local SQL Express:

Filtered Index can lead to a reduction in query performance

This month, I faced a compelling performance challenge that I am eager to share. My client indicated that one of their older data cleanup tasks is executing very slowly. Upon reviewing the Query Store and the table indexes, I discovered something quite intriguing.

Below, the image depicts the execution plan obtained from the query store.

At its essence, the query is simple; it consists of a DELETE statement with a WHERE clause that filters the rows to be deleted based on an AccountId column, which is already indexed as non-clustered.

Interestingly, the query execution plan is not just about using the nonclustered index on AccountId to retrieve the qualified rows (visible on the far right of the plan graph); it also involves a Key Lookup on the clustered index of the table.

Also, there is a suggestion for a missing index that suggests adding a duplicate index while including the ToBeDeleted column. This seems strange, as the DELETE query does not refer to any elements from the ToBeDeleted column. 

Let’s examine the Key Lookup operator more closely; it claims to be used for retrieving the ToBeDeleted column, which is not included in the nonclustered index at the rightmost top operator.

Indeed, a filtered index is available on that table, specifically filtered by the ToBeDeleted column, as shown below.

The filtered index features a key column called AccountId, filtered based on the ToBeDeleted column, yet it lacks any INCLUDED columns.

The following blog post clarifies the need for an extra Key Lookup in the query and advises incorporating the filtered column into either the key column or the included column.

sql server - Should the filtering column(s) always be in the keys / includes? - Database Administrators Stack Exchange

In my opinion, it is important to assess the actual usefulness of a filtered index before adding it, ensuring that it can significantly enhance your query performance. The filtering value should only be a constant, as sometimes its effectiveness can be overestimated.

SQL Server terminated unexpectedly because of Memory exhaustion and Oracle Linked Server

This month, I want to discuss an incident involving one of my clients, where their production SQL server was unexpectedly terminated. 

Moments before the SQL Server was terminated unexpectedly, an EXCEPTION_ACCESS_VIOLATION error was raised by a stored procedure call, as shown below:

The Stack Dump generated by SQL Server just before the service was shut down indicates that the exception is related to the Oracle Provider for OLE DB (OraOLEDB), as shown below:

Employing the WinDbg tool to analyze the minidump (.mdmp file) produced by the SQL Server upon crashing also indicates the error caused by OracleOLEDB, as shown below:

With the Oracle Linked Server Provider option set to "Allow inprocess" as depicted in the image below, an exception in the linked server provider may lead to a crash of the SQL Server.
(Ref.: Create Linked Servers - SQL Server | Microsoft Learn
SQL Server service crashes when you run an Oracle linked server query - SQL Server | Microsoft Learn)

To address this issue, I lowered the SQL Server maximum memory limit from the original 95% to 85%. The server has a total memory of 1.25TB, and the system administrator at my client's company believed that reserving 5% (64GB) for the Windows OS was sufficient for a server dedicated to SQL Server. However, he failed to consider that the Oracle OLEDB Linked Server Provider also consumes memory; as more concurrent SQL sessions invoke the Oracle Linked Server query, memory usage increases. After I adjusted the SQL Server maximum memory, allowing more memory for the Oracle OLEDB provider, the problem was resolved.

TempDB LOGBUFFER and IO_COMPLETION Waits

We observed numerous SQL sessions that were waiting for LOGBUFFER and IO_COMPLETION; all of these sessions were executing an INSERT INTO #Temp table, as illustrated below.

Upon examining the count of Virtual Log Files (VLFs) in the tempdb .ldf file, we discovered that the tempdb transaction log was significantly fragmented.

Subsequently, we executed defragmentation of the VLFs within the TempDB transaction log file, using the SQL commands provided below:

use tempdb

DBCC SHRINKFILE ( 'templog', 0, TRUNCATEONLY)

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = 'templog', SIZE = 150000MB )

The VLFs in the tempdb log file have been decreased to 32, as shown below:

We also observed that the DB Full Backup job was planned to run at that time. Thus, we rescheduled the backup job to operate during off-peak hours.

We found that the maximum memory allocation for SQL Server was set to 75% of the overall server memory, which we think was a result of the SQL Server installer GUI's suggestions.

Next, we increased the SQL max memory limit to 1216GB, representing 95% of the total 1.25TB, on the server, which is chiefly assigned to SQL server services. It should be adequate to reserve 64GB of RAM for the Windows operating system and other various tasks.

Finally, we relocated the tempdb transaction log file to a separate disk that offers improved write speed.

Slow disk in Tempdb leads to unexpected failover of the Availability Group

A problem arose for one of my clients, as their production SQL Server availability group kept failing over back and forth. Presented below is the SQL Server error log from when the failover took place.

I recommended that they relocate the tempdb from the overloaded disk.

In the meantime, to minimize the likelihood of unexpected failover, I recommended that they adjust certain cluster settings as outlined below:

1. Set the LeaseTimeout and HealthCheckTimeout values to 60000 in the Availability Group, as depicted below.

2. Raise the heartbeat delay and threshold values, as 1/2 * LeaseTimeout should be lower than SameSubnetThreshold * SameSubnetDelay, by executing the following PowerShell commands:

3. Bring the AG group offline and subsequently online, or perform a switchover, to apply the changes.

Gather the Usage and Size of all Indexes across every database

EXEC sp_MSforeachdb 'USE [?];

SELECT DB_NAME() AS DB,

    OBJECT_NAME(i.[object_id]) AS [TableName],

    i.name AS [IndexName],

    i.index_id,

    s.user_seeks,

    s.user_scans,

    s.user_lookups,

    s.user_updates,

    i.type_desc AS [IndexType],

    s.last_user_seek,

    s.last_user_scan,

    s.last_user_lookup,

    s.last_user_update,

    SUM(ps.used_page_count) * 8 / 1024 AS [Used Space (MB)],

    SUM(ps.reserved_page_count) * 8 / 1024 AS [Reserved Space (MB)]

FROM 

    sys.dm_db_index_usage_stats AS s

    INNER JOIN sys.indexes AS i

        ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id

    INNER JOIN  sys.dm_db_partition_stats AS ps

        ON ps.object_id = i.object_id AND ps.index_id = i.index_id

WHERE 

    OBJECTPROPERTY(s.[object_id], ''IsUserTable'') = 1

    AND s.database_id = DB_ID()

GROUP BY 

    OBJECT_NAME(i.[object_id]),

    i.name,

    i.index_id,

    s.user_seeks,

    s.user_scans,

    s.user_lookups,

    s.user_updates,

    i.type_desc,

    s.last_user_seek,

    s.last_user_scan,

    s.last_user_lookup,

    s.last_user_update

ORDER BY 

    [TableName], [IndexName];

';

 

Assessing the Latency of Availability Group Database Synchronization

The SQL script provided below can be utilized to assess the real-time latency in seconds of SQL Server AlwaysOn synchronization.

;WITH

AG_Stats AS

(

SELECT

AR.replica_server_name,

AG.name AS AGName,

HARS.role_desc,

DB_NAME(DRS.database_id) AS DBName,

DRS.last_commit_time

FROM sys.dm_hadr_database_replica_states DRS

INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id

INNER JOIN sys.dm_hadr_availability_replica_states HARS

ON AR.group_id = HARS.group_id AND AR.replica_id = HARS.replica_id

INNER JOIN sys.availability_groups AG ON AG.group_id = AR.group_id

),

Pri_CommitTime AS

(

SELECT

replica_server_name,

AGName,

DBName,

last_commit_time

FROM AG_Stats

WHERE role_desc = 'PRIMARY'

),

Sec_CommitTime AS

(

SELECT

replica_server_name,

AGName,

DBName,

last_commit_time

FROM AG_Stats

WHERE role_desc = 'SECONDARY'

)

SELECT

p.replica_server_name AS PrimaryReplica,

p.AGName,

p.DBName AS DatabaseName,

s.replica_server_name AS SecondaryReplica,

DATEDIFF(SECOND, s.last_commit_time, p.last_commit_time) AS Sync_Latency_Secs

FROM Pri_CommitTime p

LEFT JOIN Sec_CommitTime s

ON s.DBName = p.DBName AND s.AGName = p.AGName;

Example output as shown below:

Snapshot Replication Agent Profile's Parameter BcpBatchSize

This week, a customer of mine shared the image below, mentioning that their Snapshot Replication is experiencing bad performance.

The customer informed me that the snapshot replication was operating correctly. I then inquired whether the tables being replicated had increased in size, to which they responded affirmatively. Consequently, I recommended that they utilize a smaller BcpBatchSize value in the Snapshot Agent Profile. I recommended that they reduce the BcpBatchSize setting from its default value of 100000 to 50000 rows. This can be done by creating a new agent profile derived from the default profile, adjusting the BcpBatchSize value to 50000, and subsequently assigning this new profile to the Snapshot Publication.

Lowering it can reduce memory usage and improve stability for large datasets, though it may slow down snapshot generation.

Streamline the MSSQL High Availability to improve business continuity

This short document proposes an exploration into the possibility of refining the SQL Server high availability architecture to improve business continuity.

The issue we aim to address

Since March 2025, the Production SQL Server has faced outages 2 times. DBA team determined that the cause was the Windows Server Failover Cluster (WSFC) losing its quorum, which resulted in both the primary SQL availability replica in the US and the secondary SQL availability replica in the DK site being in a "Resolving" state, rendering them inaccessible for both reading and writing. The DBA team cannot fix this issue alone and needs help from the IT team to recover the WSFC quorum.

Current architecture for production SQL Server High Availability

Displayed below is the current WSFC architecture of the production SQL Server.

To mitigate the performance effects of network latency between the US and DK sites, the primary SQL replica and the secondary replica are configured to synchronize data in Asynchronous-Commit mode. This configuration limits the SQL availability group to Manual Failover only, where Automatic Failover is not supported, a fact that should already be known and accepted by us.

The table below has been taken from Microsoft Online regarding WSFC Quorum:

Based on the information provided by Microsoft, we can derive the following disaster recovery scenarios for our existing configuration:

Primary Server	Cloud Witness	Secondary Server	Database accessibility within the availability group
Up	Up	Up	The primary database server allows read and write access.
Up	Up	Down	The primary database server allows read and write access.
Up	Down	Up	The primary database server allows read and write access.
Up	Down	Down	According to dynamic quorum behavior of WSFC, if the secondary server and the witness are taken down one at a time, the quorum continues to exist, and the primary database server is still accessible. However, if both the secondary server and the witness go down simultaneously from the primary server's perspective—imagine the internet connection to both is severed—the databases in the primary server's availability group will become inaccessible, and immediate IT support is needed to recover the quorum. If the secondary server is connected to the witness, we might be able to reinstate the database services on the secondary server through a forced manual failover.
Down	Up	Up	DBA must conduct a manual failover to the secondary server to resume read-write access to the databases in availability group.
Down	Down	Up	Quorum is lost; the databases are inaccessible, and immediate IT support is needed to recover the quorum.

 

The new architecture we present aims to tackle the highlighted scenarios.

The proposed architecture – Clusterless Availability Group

SQL Server 2017 introduces read-scale availability groups without a cluster.

In the same way as a conventional clustered availability group, a Read-Scale availability group (often referred to as Clusterless; the term Read-Scale is merely a marketing label by Microsoft) allows for data synchronization between primary and secondary replicas to be configured in either Synchronous-Commit mode or Asynchronous-Commit mode at any time without causing service interruptions. A clusterless availability group only allows for manual failover, and does not support automatic failover. Using a clusterless availability group offers the benefit of removing the necessity for WSFC quorum and the cloud witness, thereby simplifying maintenance. Moreover, the disaster recovery scenarios for our SQL Servers will be less complex as described below:

Primary Server	Secondary Server	Database accessibility within the availability group
Up	Up	Primary database server is Read-Write accessible.
Up	Down	Primary database server is Read-Write accessible.
Down	Up	DBA must conduct a Manual Failover to the Secondary Server to resume Read-Write access to the databases in Availability Group.

 

Following Actions

DBA team is looking to obtain a new set of SQL Server VMs, one in the US and the other in DK, to set up a lab environment for assessing the clusterless availability group, focusing on disaster recovery and data integrity. After we secure satisfactory testing results, we can develop a migration plan for the clusterless architecture or include it in the forthcoming SQL Server upgrade initiative (upgrading from version 2019 to 2022/2025).