DBA Insights Oracle • SQL Server • Postgresql,RDS

07/24/2026

MSSQL Performance Tuning starts with understanding SQL Server wait types.
If you're troubleshooting SQL Server performance issues, these are the first wait types you should know:
PAGEIOLATCH – Disk I/O Bottlenecks
Indicates SQL Server is waiting for data pages to be read from disk. Common causes include slow storage, missing indexes, and queries performing excessive reads.
CXPACKET / CXCONSUMER – Parallelism
These waits are related to parallel query ex*****on. High values may indicate inefficient ex*****on plans or SQL Server parallelism settings that need optimization.
LCK_M_* – Blocking
Occurs when sessions are waiting for locks held by other transactions. Review blocking chains, long-running transactions, and application transaction design.
RESOURCE_SEMAPHORE – Memory Pressure
Shows that queries are waiting for memory grants before ex*****on. Optimize large queries, improve indexing, and review SQL Server memory configuration.
SOS_SCHEDULER_YIELD – CPU Pressure
Typically indicates CPU-intensive workloads or inefficient query ex*****on plans. Focus on query optimization and reducing unnecessary CPU consumption.
Understanding SQL Server wait statistics is one of the fastest ways to identify the root cause of performance problems. Instead of guessing, let wait types guide your SQL Server performance tuning efforts.
What is the wait type you investigate most often in your SQL Server environment?

07/24/2026

PostgreSQL Streaming Replication with repmgr: Complete Setup Guide
Building a highly available PostgreSQL environment starts with a reliable replication strategy. PostgreSQL Streaming Replication with repmgr simplifies standby management, replication monitoring, failover, and recovery, making it a popular choice for production databases.
This guide covers the complete setup process:
Prerequisites
Install PostgreSQL on both Primary and Standby servers.
Ensure both servers use the same PostgreSQL version.
Configure network connectivity, SSH, hostname resolution, firewall, and time synchronization.
Step 1: Install repmgr on both servers.
Step 2: Configure postgresql.conf for Streaming Replication.
Step 3: Configure pg_hba.conf to allow replication connections.
Step 4: Create the repmgr database.
Step 5: Create the repmgr user with replication privileges.
Step 6: Configure repmgr.conf on both Primary and Standby servers.
Step 7: Register the Primary server.
Step 8: Clone the Standby server using repmgr standby clone.
Step 9: Start PostgreSQL and register the Standby server.
Step 10: Verify Streaming Replication using pg_stat_replication and pg_is_in_recovery().
Step 11: Test replication by creating data on the Primary server and validating it on the Standby server.
Step 12: Perform failover using repmgr standby promote.
The infographic below includes all required commands, configuration files, SQL statements, and verification steps for a complete PostgreSQL Streaming Replication setup.
Which High Availability solution do you use in production—repmgr, Patroni, Pgpool-II, or another PostgreSQL replication solution?

07/24/2026

Understanding HADR Wait Types in SQL Server (Always On Availability Groups)
If you work with SQL Server Always On Availability Groups, understanding HADR wait types is essential for troubleshooting performance issues.
A wait type tells you what SQL Server is waiting for before it can continue processing a task. HADR wait types are specifically related to High Availability and Disaster Recovery (HADR) features.
Here are some of the most common HADR wait types and what they mean:
1. HADR_SYNC_COMMIT
Meaning: SQL Server is waiting for the secondary replica to acknowledge that the transaction log has been hardened before the transaction commits.
When it's normal:
Synchronous commit mode.
Low network latency.
When to investigate:
High network latency.
Slow disk performance on the secondary replica.
Heavy workload causing replication delays.
2. HADR_DATABASE_WAIT_FOR_RECOVERY
Meaning: The database is waiting for the recovery process to complete before becoming available.
Common causes:
Database restart.
Failover.
Database restore or recovery.
3. HADR_LOGCAPTURE_WAIT
Meaning: SQL Server is waiting to capture new log records from the transaction log for replication to secondary replicas.
Possible reasons:
Low transaction activity.
Waiting for new log records.
Usually, this is a normal wait.
4. HADR_WORK_QUEUE
Meaning: A background HADR worker is waiting for new work.
This is an idle wait and is generally not a performance problem.
5. HADR_TIMER_TASK
Meaning: Internal HADR timer tasks are waiting for the next scheduled operation.
This is also an expected idle wait.
6. HADR_CLUSAPI_CALL
Meaning: SQL Server is communicating with Windows Server Failover Clustering (WSFC).
Investigate if:
Cluster communication is slow.
WSFC has connectivity or configuration issues.
7. HADR_FILESTREAM_IOMGR_IOCOMPLETION
Meaning: SQL Server is waiting for FILESTREAM I/O operations to complete in an Always On environment.
Usually related to FILESTREAM storage performance.
8. HADR_NOTIFICATION_DEQUEUE
Meaning: HADR background processes are waiting for notifications or events.
This is normally an idle wait and does not indicate a problem.
Key Takeaways
Not every HADR wait indicates a performance issue.
Focus on waits with high wait time, high wait percentage, and performance impact.
Monitor:
Network latency
Disk performance
Replica synchronization
Cluster health
Wait statistics trends
Understanding HADR wait types helps DBAs troubleshoot replication delays, improve Always On Availability Group performance, and maintain high database availability.

07/24/2026

One SQL Server setting that is often overlooked is optimize for ad hoc workloads.
By default, when a query runs for the first time, SQL Server stores the entire ex*****on plan in memory. This works well for frequently executed queries, but in environments with many ad hoc queries, dynamic SQL statements, reporting tools, or ORM-generated queries, thousands of ex*****on plans may be cached and never reused.
Over time, these single-use plans consume valuable memory that could otherwise be used for caching data pages and improving overall performance.
To address this, SQL Server provides the following configuration:
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
When enabled, SQL Server stores only a small plan stub during the first ex*****on. If the same query executes again, the full ex*****on plan is then cached. This significantly reduces plan cache bloat caused by one-time queries.
Benefits:
Reduces unnecessary plan cache memory usage
Improves memory utilization
Helps minimize memory pressure on busy servers
Particularly useful for OLTP systems with large numbers of ad hoc queries
The change is online, requires no downtime, and takes effect immediately for new query compilations.
A simple configuration change that can have a noticeable impact on SQL Server performance and memory management.

07/24/2026

MSSQL Performance Tuning starts with understanding SQL Server wait types.
If you're troubleshooting SQL Server performance issues, these are the first wait types you should know:
PAGEIOLATCH – Disk I/O Bottlenecks
Indicates SQL Server is waiting for data pages to be read from disk. Common causes include slow storage, missing indexes, and queries performing excessive reads.
CXPACKET / CXCONSUMER – Parallelism
These waits are related to parallel query ex*****on. High values may indicate inefficient ex*****on plans or SQL Server parallelism settings that need optimization.
LCK_M_* – Blocking
Occurs when sessions are waiting for locks held by other transactions. Review blocking chains, long-running transactions, and application transaction design.
RESOURCE_SEMAPHORE – Memory Pressure
Shows that queries are waiting for memory grants before ex*****on. Optimize large queries, improve indexing, and review SQL Server memory configuration.
SOS_SCHEDULER_YIELD – CPU Pressure
Typically indicates CPU-intensive workloads or inefficient query ex*****on plans. Focus on query optimization and reducing unnecessary CPU consumption.
Understanding SQL Server wait statistics is one of the fastest ways to identify the root cause of performance problems. Instead of guessing, let wait types guide your SQL Server performance tuning efforts.
What is the wait type you investigate most often in your SQL Server environment?

07/24/2026

PostgreSQL Architecture: Understanding How PostgreSQL Works
PostgreSQL is one of the most powerful open-source relational database management systems (RDBMS), known for its reliability, ACID compliance, extensibility, and advanced SQL features. Understanding its architecture helps DBAs, developers, and cloud engineers optimize performance, improve scalability, and ensure high availability.
Key Components
Client Layer
Applications connect using drivers like JDBC, psycopg, Npgsql, libpq, Node.js, Go, or tools like pgAdmin.
PostgreSQL Server
Processes client requests, executes SQL, manages storage, transactions, authentication, and security.
Postmaster
Accepts client connections, starts backend processes, and manages background workers.
Backend Processes
Each client connection gets its own backend process to parse, plan, and execute SQL statements.
Shared Memory
Shared Buffers (cache)
WAL Buffers
Lock Manager
Write-Ahead Log (WAL)
Logs every database change before writing to disk, ensuring durability, crash recovery, PITR, and replication.
Background Processes
Checkpointer
Background Writer
WAL Writer
Autovacuum
These processes improve performance, write efficiency, and automatic maintenance.
Indexes
Supports B-tree, Hash, GIN, GiST, SP-GiST, BRIN, partial, and expression indexes for faster queries.
Replication
Supports physical and logical replication with streaming replication for high availability and disaster recovery.
Tablespaces
Store database objects across different storage devices for better storage management.
Data Files
Stores tables, indexes, WAL files, and system catalogs within the PostgreSQL data directory.
Query Flow
Client → Postmaster → Backend Process → Query Planner → Shared Buffers → WAL → Data Files → Results
Understanding PostgreSQL architecture is essential for performance tuning, query optimization, replication, backup & recovery, troubleshooting, and building scalable, enterprise-grade database systems.

07/06/2026

06/17/2026

**SQL Server Tip: Monitor Query Performance**

Use Extended Events to track long-running queries and optimize them. Create a session to capture query ex*****on details that exceed a specific duration.

Example command:
```sql
CREATE EVENT SESSION LongRunningQueries ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlserver.sql_text)
WHERE duration > 10000000) -- 10 seconds
ADD TARGET package0.event_file(SET filename='LongRunningQueries.xel');
GO
ALTER EVENT SESSION LongRunningQueries ON SERVER STATE = START;
GO
```

06/17/2026

**SQL Server Tip:**

Regularly update statistics to ensure the query optimizer has current data distribution information, which can improve query performance. Use the `UPDATE STATISTICS` command for specific tables.

Example:
```sql
UPDATE STATISTICS YourDatabase.YourSchema.YourTable;
```

06/17/2026

**SQL Server Tip: Monitor TempDB Usage**

Regularly monitor TempDB usage to prevent performance bottlenecks. Use the following query to check space usage:

```sql
SELECT SUM(size) * 8 / 1024 AS UsedMB
FROM tempdb.sys.database_files;
```

Adjust TempDB file size and number as needed based on your workload to optimize performance.