SQL Server by Harsh Kumar

SQL Server is known for its efficient I/O management system. One of the performance-boosting features that often works silently behind the scenes is Read-Ahead Reads. If you've ever wondered how SQL Server optimizes data retrieval before it's even requested by the query engine, read-ahead reads are a key part of the magic. What are Read-Ahead Reads? Read-Ahead Reads refer to the mechanism by which SQL Server proactively fetches data pages from disk into the buffer cache before the query processor actually needs them. This helps minimize latency and improves query performance. In simple terms : SQL Server tries to anticipate what data your query will need and loads it into memory in advance. Why Does SQL Server Use Read-Ahead Reads? Disk I/O is one of the slowest operations in any database system. To reduce query wait times caused by disk reads, SQL Server:      * Pre-fetches data pages that are likely to be needed.      * Loads them into the buffer pool. ...

When it comes to high availability in SQL Server, Always On Availability Groups offer a robust solution for ensuring data resilience and fault tolerance. At the heart of this technology is a sophisticated log-based replication mechanism distinct from traditional database mirroring or snapshot replication. Let’s walk through exactly how data moves from the Primary Replica to the Secondary Replica, step by step. High-Level Process Flow  Primary Replica → Log Generation → Log Transport → Log Reception → Redo → Acknowledgement. Step-by-Step Explanation 1. Client Writes Data to Primary Replica * A client application sends a transaction (e.g., INSERT, UPDATE) to the Primary Replica. * SQL Server writes this transaction to the transaction log of the primary database. * This log write is made to the log buffer and flushed to disk before the transaction is considered committed. 2. Log Block is Captured by the Always On Transport Manager * The HADR_LOG_SEND_MANAGER process monitors the trans...

Degree of Parallelism (DOP) feedback is a feature in SQL Server 2025 that automatically tunes the number of threads used for query execution, optimizing performance and resource usage. This feature is now generally available and enabled by default in SQL Server 2025 as well as in Azure SQL Database. How  Degree of Parallelism (DOP) Feedback  Works - Dynamic Adjustment : DOP feedback dynamically adjusts the degree of parallelism for queries based on actual runtime performance metrics such as CPU time and elapsed time. Self-Tuning : If a query consistently underperforms due to excessive parallelism (e.g., too many threads causing overhead), DOP feedback will reduce the DOP for future executions of that specific query. No Manual Tuning Required : This process is automatic, eliminating the need for DBAs to manually adjust parallelism settings for recurring queries. Persistent Feedback : The feedback and adjustments are stored in the Query Store, ensuring that optimizations persist...

SQL Server 2025 significantly improves how SQL logins and contained database users store and handle passwords. Instead of storing simple hashes, Microsoft now uses a Password-Based Key Derivation Function (PBKDF) a cryptographic function that makes it computationally expensive to guess or crack passwords. Password-Based Key Derivation Function  (PBKDF)- A PBKDF is a function that: * Takes a password and a cryptographic salt. * Applies a hashing algorithm repeatedly (e.g., 100,000 times). * Produces a derived key or hash, which is securely stored. Note - The goal of multiple iterations is to slow down brute-force attacks . Even if an attacker gets access to password hashes, trying millions of combinations becomes expensive and time-consuming. What’s new in SQL Server 2025? * SQL Server 2025 uses a PBKDF algorithm, similar to PBKDF2 (though Microsoft hasn’t explicitly named it). * The password is hashed with 100,000 iterations, which dramatically increases the time required to guess...

With SQL Server 2025, Microsoft introduces a groundbreaking capability: native support for calling REST APIs directly from T-SQL using the new system stored procedure " sp_invoke_external_rest_endpoint ". This feature opens the door for integrating SQL Server more easily with external systems, cloud APIs, microservices, and more without needing CLR, SQL Agent jobs, or external scripting. Sp_invoke_external_rest_endpoin is a built-in stored procedure introduced in SQL Server 2025 (v17.x) that allows SQL Server to make HTTP REST API calls directly from T-SQL and receive JSON responses. This turns SQL Server into a more connected platform, enabling: * Real-time integration with external services * Triggering webhooks or third-party APIs * Pushing data to Power BI, Microsoft Graph, or Azure Functions * Querying external systems and merging results in T-SQL Prerequisites  - * Before you can use sp_invoke_external_rest_endpoint, ensure: * SQL Server 2025 is installed (17.x). * You’...