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’...

Zstandard Backup Compression in SQL Server 2025  In SQL Server 2025, Microsoft has introduced a highly anticipated enhancement - support for the Zstandard (ZSTD) compression algorithm in native database backups. This marks a major leap forward in backup efficiency, delivering faster processing speeds and smaller file sizes without significantly increasing CPU usage. Whether you're managing on-premises SQL Servers or hybrid cloud environments, understanding and implementing Zstandard compression can help you optimize both performance and storage costs. What is Zstandard? Zstandard (ZSTD) is a modern, open-source lossless data compression algorithm developed by Facebook. It’s designed to offer: High compression ratios (comparable to zlib/gzip) Lightning-fast compression and decompression Low memory and CPU usage Unlike legacy algorithms like MS_XPRESS , Zstandard excels at balancing speed and size—making it ideal for high-throughput, enterprise-grade workloads. Why SQL...

Optimized Halloween Protection in SQL Server 2025 The Halloween Problem  is a classic and subtle issue in relational database systems that arises during certain data modification operations—specifically UPDATE, INSERT, DELETE, or MERGE statements. The core of the problem is that, under specific circumstances, a row can be modified more than once during a single statement execution, even though the intention is for each row to be updated only once. How Does the Halloween Problem Occur? The issue typically surfaces when: * An update operation modifies a column that is also used in the index being scanned to identify the rows to update. * As the update progresses, changing the indexed column can cause the physical location of the row in the index to change. * This movement can cause the row to reappear ahead of the scan cursor, meaning the same row is encountered and updated again—potentially leading to multiple updates or even infinite loops. Halloween Protection: The Traditional So...

Accelerated Database Recovery (ADR) is one of the most transformative features in recent SQL Server releases, and with SQL Server 2025, it’s more powerful and flexible than ever. ADR is a SQL Server feature designed to drastically improve database availability by reengineering the recovery process. Traditional recovery could take a long time, especially with long-running transactions, because SQL Server had to scan the transaction log from the oldest uncommitted transaction. ADR changes this by introducing a new architecture that makes recovery and rollback nearly instantaneous, regardless of transaction length or database activity. Key Benefits : Fast and consistent recovery : Recovery time is no longer affected by the number or size of active transactions. Instantaneous rollback : Even very long-running transactions can be rolled back instantly. Aggressive log truncation : The transaction log is truncated more aggressively, preventing log growth even with active long transactions. H...

Standard Developer and Enterprise Developer edition in SQL Server 2025 In SQL Server 2025, two new Developer editions are introduced: Standard Developer and Enterprise Developer. These editions are designed for development and testing purposes and are licensed for free. The Standard Developer edition includes all the features of the Standard edition, while the Enterprise Developer edition offers the complete functionality of the Enterprise edition.  Standard Developer Edition Free licensing: It can be used without incurring any licensing costs.  Matches Standard edition: It mirrors the features and capabilities of the SQL Server Standard edition.  Ideal for teams: It's particularly valuable for teams whose production workloads run on Standard edition.  Narrowing the gap: It helps to minimize the difference between development and production environments, especially for applications that rely on Standard or Web/Express editions.  Improves code predictability: It ...

 Copilot Feature in SQL Server 2025: A Game-Changer for Database Management As organizations continue to embrace digital transformation, the demand for smarter, more efficient database management tools is growing. Enter SQL Server 2025, Microsoft's latest iteration of its flagship database system, which introduces a revolutionary feature—Copilot. Powered by advanced AI capabilities, Copilot is set to redefine how database administrators (DBAs) and developers interact with SQL Server. What is Copilot in SQL Server 2025? Copilot is an AI-powered assistant integrated into SQL Server 2025. Designed to augment productivity, it leverages natural language processing (NLP) and machine learning (ML) to assist users in managing databases, writing queries, optimizing performance, and ensuring data security. Built on Microsoft's Azure OpenAI service, Copilot brings intelligence directly into the SQL Server ecosystem. Key Features of Copilot Natural Language Querying : Users can write quer...

Microsoft on 19 Nov 2024 has officially announced SQL Server 2025, a revolutionary update that positions itself as an AI-ready, enterprise-grade database built to address the growing needs of modern data and AI-driven applications. Here's an overview of what makes this release groundbreaking for developers, data professionals, and businesses. Key Features and Enhancements-  AI Built-In Native Vector Indexing: Enables hybrid searches that combine structured SQL data with vector-based queries, crucial for modern AI models. Integration with AI Frameworks: Seamless support for LangChain, Semantic Kernel, and Entity Framework Core facilitates advanced AI application development. RAG (Retrieval-Augmented Generation): Built-in tools make it easier to incorporate advanced AI into applications using T-SQL. Performance and Reliability Enhanced Query Optimization and batch mode processing improve performance for analytical workloads. Optimized locking mechanisms reduce contention in high-conc...

When upgrading the operating system (OS) in a Windows cluster, there are a few methods you can consider. The appropriate method depends on the specific requirements, cluster configuration, and the available resources. Here are some common approaches: In-place upgrade : This method involves directly upgrading the existing OS on each node of the cluster. It can be suitable when the cluster nodes are capable of supporting the target OS version and when preserving applications, data, and configurations is a priority. To perform an in-place upgrade, follow the official documentation from Microsoft, which provides detailed instructions and considerations for each specific Windows version. Rolling upgrade : Similar to the rolling upgrade method described earlier, you can perform a rolling upgrade of the Windows cluster. In this case, you upgrade the OS on one node at a time while the other nodes continue to operate. By sequentially upgrading the nodes, you can maintain service availability th...

The "Rolling Upgrade" method is a commonly used approach for upgrading a cluster while minimizing downtime and maintaining service availability. It involves sequentially upgrading individual nodes or components of a cluster, one at a time, while the rest of the cluster continues to operate. Here's an overview of the steps involved in a Rolling Upgrade: Plan the upgrade : Determine the scope and requirements of the upgrade, including the target version or configuration changes. Consider factors like compatibility, dependencies, and any potential impact on applications or services running on the cluster. Prepare the upgrade : Ensure you have a backup or snapshot of the cluster's current state in case you need to roll back. Review documentation and release notes for the new version or configuration changes. Prepare any necessary upgrade scripts or tools. Start with a node or component: Select an initial node or component for the upgrade. This could be a single machine, a...

WAIT TYPES refer to the specific events or conditions that cause a query or transaction to wait for a resource to become available. Understanding wait types is essential for troubleshooting and optimizing SQL Server performance. SQL Server wait types can be broadly categorized into two group s: External waits : These waits occur when SQL Server is waiting for an external resource, such as I/O operations or network requests. Examples of external wait types include PAGEIOLATCH_EX, NETWORK_IO, and ASYNC_NETWORK_IO. DMVs related to External Waits : sys.dm_io_virtual_file_stats : This DMV provides information on I/O statistics at the file level. It can be used to identify which database files are experiencing high I/O activity. sys.dm_exec_requests : This DMV provides information on currently executing queries, including the wait type, wait time, and resource utilization. Internal waits : These waits occur when SQL Server is waiting for an internal resource, such as a latch or a lock. Exam...

Split Brain is a term used in SQL Server clustering to describe a scenario where the nodes in the cluster lose communication with each other, and each node believes that it is the only active node in the cluster. This can cause multiple issues, including data corruption, duplicate transactions, and other unexpected behaviors. Here's an in-depth detail of split brain in SQL Server clustering with examples: In a SQL Server clustering environment, there are multiple nodes that are connected to each other via a network. Each node has access to the same shared storage, which contains the SQL Server database files. In a normal situation, the nodes communicate with each other to ensure that only one node is active at any given time. This is achieved through a mechanism called a cluster heartbeat, where the nodes send signals to each other to indicate that they are still alive and functioning. However, in certain scenarios, the cluster heartbeat may fail, and the nodes may lose communicat...

A Service Principal Name (SPN) is a unique identifier for a service instance in a Windows environment. In SQL Server, SPNs are used to specify the identity under which a particular SQL Server service runs, which is necessary for Kerberos authentication and delegation in a Windows Active Directory environment. Here is a detailed explanation of SPNs in SQL Server 1. What is an SPN? An SPN is a unique name that identifies a service instance running on a computer. It is used by Kerberos authentication to associate a service instance with a service account. An SPN is made up of the service class, hostname, and port number (if applicable) that the service is running on. 2. Why are SPNs important in SQL Server? In SQL Server, SPNs are important because they are used to enable Kerberos authentication, which provides a more secure and efficient method for authenticating users than NTLM authentication. Kerberos authentication allows users to authenticate once and then reuse their credentials ...

Having thousands of databases on a single SQL Server instance can lead to several problems, including: Resource contention : Each database requires its own set of resources, such as memory, disk I/O, and CPU time. With thousands of databases, these resources can quickly become scarce and lead to performance problems. Maintenance challenges : Maintaining thousands of databases can be a daunting task, especially when it comes to tasks such as backups, restores, and monitoring. It can be difficult to keep track of which databases need maintenance and ensuring that all databases are properly maintained can become an overwhelming task. Security risks : With thousands of databases, it can be challenging to maintain proper security protocols. Each database may have its own set of users and permissions, making it difficult to manage access and enforce security policies across all databases. Increased risk of downtime : If one database experiences issues, it can potentially affect other databas...

Physical memory , also known as RAM (Random Access Memory), is a type of computer memory that is used to temporarily store data and instructions that the CPU (Central Processing Unit) needs to access quickly. Virtual memory, on the other hand, is a technique that allows the operating system to use a portion of the hard disk as if it were RAM. In SQL Server, physical memory is used to cache data and instructions that are frequently accessed by the server. This is known as the buffer pool, and it is used to improve the performance of the database by reducing the number of disk reads that need to be performed. When SQL Server needs to access data that is not in the buffer pool, it must retrieve it from disk, which can be slower than accessing it from memory. Virtual memory is used by SQL Server as a backup when the physical memory is not sufficient to store all the data and instructions that are needed by the server. In this case, the operating system will move some of the data and instr...

Memory is a critical component that can significantly impact the performance of SQL Server. When SQL Server processes data, it stores and retrieves information from memory, and the amount of memory available to SQL Server can directly affect its performance.  Buffer Cache Hit Ratio : The Buffer Cache Hit Ratio is a critical performance metric for SQL Server, which represents the percentage of data pages that are found in memory. A high buffer cache hit ratio indicates that the SQL Server is finding the data it needs in memory, which leads to faster query execution times. A low buffer cache hit ratio means that the SQL Server is having to fetch data from disk, which is slower and can lead to poor performance. For example , let's say that a SQL Server has 32 GB of memory, and the buffer cache hit ratio is 99%. This means that 99% of the data that SQL Server needs is in memory, and only 1% of the data needs to be fetched from disk. This results in faster query execution times and impr...

I/O (Input/Output) can have a significant impact on SQL Server performance as it involves the read and write operations to and from disk. Here are some factors that can impact SQL Server I/O performance: Storage Type : The type of storage you use for SQL Server can impact performance. Solid State Drives (SSDs) offer faster I/O compared to Hard Disk Drives (HDDs), but they can be more expensive. RAID Configuration : A Redundant Array of Inexpensive Disks (RAID) configuration can improve performance and provide redundancy for data protection. RAID configurations can improve read and write performance by striping data across multiple disks. Disk Layout : The way that data is laid out on disks can impact I/O performance. Using multiple disks for data, logs, and backups can help to avoid contention and improve performance. Indexing Strategy : Indexing can impact I/O performance by affecting the amount of data that needs to be read from disk. A well-designed indexing strategy can reduce the ...