SQL Server by Harsh Kumar

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

The number of CPU cores can have a significant impact on the performance of SQL Server. Here is a explanation of how the number of CPU cores affects SQL Server performance, along with examples: Parallel query processing : SQL Server can take advantage of multiple CPU cores to process queries in parallel, which can result in faster query processing times.  For example , suppose you have a query that takes 10 seconds to run on a system with 4 CPU cores. If you double the number of CPU cores to 8, the same query may only take 5 seconds to run, assuming the system has enough memory and disk I/O capacity to handle the increased workload. Concurrent user activity : SQL Server can handle more concurrent user activity with more CPU cores. Each user connection can be assigned to a separate CPU core, which can result in more efficient processing of queries and requests.  For example , suppose you have a system with 4 CPU cores and 50 users accessing SQL Server simultaneously. If each us...

Prefetching is a technique used by SQL Server to improve query performance by anticipating the data that will be needed for a query and reading it into memory in advance. This reduces the number of I/O operations required to fetch data from disk, which can significantly improve query performance. There are several mechanisms that SQL Server uses for prefetching, including: Read-Ahead : This mechanism anticipates the data pages that will be needed for a query and reads them from disk into memory in advance. This is based on a predictive algorithm that uses information from the query execution plan to determine which pages are likely to be needed. Pre-Fetching : This mechanism anticipates the related data that will be needed for a query and reads it from related tables into memory in advance. This is based on the relationships defined in the query and can significantly reduce the number of I/O operations required during query execution. Parallelism : SQL Server can use multiple threads ...

Transport Layer Security (TLS) is a cryptographic protocol that provides secure communication over a network. TLS 1.3 is the latest version of the protocol, released in 2018, and it provides several improvements over the previous versions, including stronger encryption algorithms and a streamlined handshake process. In the context of SQL Server, TLS 1.3 can be used to secure the communication between the SQL Server instance and client applications, such as database management tools or web applications. When TLS 1.3 is enabled, all data exchanged between the client and server is encrypted and authenticated, protecting it from eavesdropping and tampering. To enable TLS 1.3 for SQL Server, you need to follow these steps : Install a certificate on the SQL Server instance: To use TLS 1.3, you need to have a certificate installed on the SQL Server instance. This certificate is used to authenticate the server to the client and to establish the encryption keys. Enable TLS 1.3 on the SQL Serv...

The Lock Manager in SQL Server is responsible for managing the locks that are acquired by transactions to ensure data consistency and integrity. When a transaction wants to access a data resource, it must first acquire a lock on that resource. The Lock Manager controls the acquisition and release of locks and ensures that concurrent transactions do not conflict with each other. The Lock Manager works by using different types of locks to manage the transactions. The two main types of locks used by the Lock Manager are Shared locks and Exclusive locks. Shared locks: A shared lock allows multiple transactions to read the same data resource simultaneously, but it prevents any transaction from modifying the data resource while the shared lock is held. Exclusive locks: An exclusive lock allows a single transaction to modify a data resource, preventing any other transactions from accessing or modifying the same resource until the exclusive lock is released. The Lock Manager uses a variety of...

The Log Manager in SQL Server is responsible for managing the transaction log, which is a critical component of the database. The transaction log records all the transactions that are performed on the database, including insertions, updates, and deletions, and it also records the time at which the change was made. In case of a system failure or error, the transaction log is used to recover the database to a consistent state. The Log Manager performs several tasks to ensure the integrity and consistency of the transaction log. These tasks include: Writing log records to the transaction log file: Whenever a transaction is executed, the Log Manager writes a log record to the transaction log file. The log record contains information about the transaction, including the type of transaction, the time at which it was executed, and the data that was changed. Flushing the log buffer: The Log Manager maintains a log buffer in memory, which stores the log records before they are written to the t...

In SQL Server, a " LOCK " is a mechanism used to ensure data consistency and prevent data corruption when multiple transactions access the same data concurrently. When a transaction needs to access a piece of data, it requests a lock on that data. If the data is already locked by another transaction, the requesting transaction may have to wait until the lock is released. There are several types of locks in SQL Server, including: Shared Lock : A shared lock allows multiple transactions to read the same data simultaneously, but it prevents any transaction from modifying the data until the lock is released. For example, when multiple transactions are reading from the same table, they acquire shared locks on the table to ensure that they don't interfere with each other. Exclusive Lock : An exclusive lock is acquired when a transaction needs to modify data, and it prevents any other transaction from reading or modifying the same data until the lock is released. For example, wh...

Rebuilding Index with Compression is a process in SQL Server that involves rebuilding an existing index on a table with the addition of data compression. This process can help reduce the size of the index, which in turn can help improve query performance and reduce storage costs. Here is an example of how to rebuild an index with compression in SQL Server: Assuming we have a table called "SalesData" with an index called "IX_SalesData_SalesDate", which we want to compress. 1) First, we need to identify the index we want to compress:     SELECT name, type_desc, is_disabled, compression_desc      FROM sys.indexes      WHERE object_id = OBJECT_ID('SalesData')      AND name = 'IX_SalesData_SalesDate'; This query retrieves information about the index, including its name, type, whether it is currently disabled, and its compression settings. 2) Next, we need to rebuild the index with compression:     ALTER INDEX IX_SalesData_Sa...

XACT_ABORT is a setting in SQL Server that specifies whether a transaction should be automatically rolled back if a run-time error occurs within the transaction. When XACT_ABORT is set to ON, if a run-time error occurs within a transaction, the entire transaction is immediately rolled back and an error message is returned to the client. This can be useful to ensure data consistency and to prevent partially completed transactions from leaving the database in an inconsistent state. When XACT_ABORT is set to OFF (which is the default setting), a run-time error within a transaction will cause the transaction to be rolled back only if the error is severe enough to cause the batch to terminate. This means that it is possible for the transaction to partially complete before an error is encountered, which could leave the database in an inconsistent state. To enable XACT_ABORT in SQL Server, you can use the following syntax at the beginning of your query or stored procedure:     ...

MSDTC stands for Microsoft Distributed Transaction Coordinator , and it is a Windows service that provides transaction management services for distributed applications. In the context of SQL Server, MSDTC enables distributed transactions across multiple instances of SQL Server or other resource managers. Let's consider an example to understand how MSDTC can be used in SQL Server. Imagine you have two databases on separate servers - Server A and Server B. You need to perform a transaction that involves updating data in both databases. Without MSDTC, you would need to manually handle the transaction in each database and ensure that they are committed or rolled back consistently. However, with MSDTC, you can use a distributed transaction to ensure that the transaction is completed atomically, even if there are failures or network interruptions. Here is a step-by-step example of how to use MSDTC in SQL Server: Enable MSDTC on both Server A and Server B. This involves configuring MSDTC ...

Buffer pool parallel scan in SQL Server 2022 When a table or index scan is performed in SQL Server, the pages are read into the buffer pool. With buffer pool parallel scan, the pages are scanned in parallel, which allows the scan to complete more quickly and efficiently. This is achieved by dividing the work of scanning the pages into multiple tasks, each of which is performed by a separate processor. The number of processors used for the scan is controlled by the MAXDOP option. MAXDOP stands for "maximum degree of parallelism" and specifies the maximum number of processors to use for the scan. For example, if you set MAXDOP to 4, SQL Server will use up to 4 processors to scan the pages in parallel.                SELECT * FROM SalesData OPTION (MAXDOP 4) The decision to use buffer pool parallel scan is made by the query optimizer, based on the size of the table, the available resources, and other factors. The query optimizer will use buffer ...

SQL Server 2022 introduces new granular permissions that provide more fine-grained control over database objects and actions. It improved the existing set of permissions, by making them more granular. This has happened in 3 distinguishable areas: Access to System Metadata : 10 new permissions (5 on Server- and 5 on Database level) Extended Events : 18 new permissions (9 on Server- and 9 on Database level) Security-related objects : 4 new permissions (3 on Server- and 1 on Database level) Access to System Metadata : The new "ACCESS ANY SYSTEM METADATA" permission allows a user to view any system metadata, including system tables, views, and functions. This permission can be granted at the server or database level, and it provides a more fine-grained control over who can access system metadata. There is a split in existing permissions into two separate sub-permissions below VIEW SERVER STATE/VIEW DATABASE STATE respectively VIEW ANY DEFINITION/VIEW DEFINITION. The diagram below...

Latch contention is a common issue in SQL Server, and each version has introduced various enhancements to improve latch management and reduce contention. Here are some examples of enhancements in each version:                                                   SQL Server 2017: Latch-Free IO Completion Latch : This latch is used to improve performance when reading data from disk. It reduces contention on latches by allowing multiple threads to perform IO operations without contention. For example, when reading data from a table, multiple threads can read data without contention, which can improve performance.                                                   SQL Server 2019: AdaptiveLatch : Thi...

In SQL Server, there are several DBCC commands that can be used to manage the SQL Server buffer cache, plan cache, and other system caches. Here are some examples of commonly used DBCC commands: DBCC FREEPROCCACHE : This command clears the plan cache, which is a portion of memory that stores execution plans for SQL Server queries. To clear the cache, simply execute the following command in a SQL Server query window: DBCC FREEPROCCACHE; DBCC FREESYSTEMCACHE : This command clears the specified system cache, such as the buffer cache, the procedure cache, or the service broker cache. To clear the buffer cache, for example, execute the following command: DBCC FREESYSTEMCACHE('ALL'); This command clears all caches. You can also specify a specific cache to clear, such as the buffer cache or the procedure cache, by replacing 'ALL' with the appropriate cache name. DBCC FREESESSIONCACHE : This command clears the cached cursors and temporary tables for a specific session. To clear...

Page Life Expectancy (PLE) is a performance metric used in Microsoft SQL Server to measure the average amount of time that a page remains in the buffer pool without being flushed to disk. The buffer pool is a region of memory that is used to cache data pages in SQL Server, and PLE is an indication of how long a page can remain in the buffer pool before it is replaced with another page. PLE is measured in seconds, and it is calculated by dividing the total amount of time that all pages have remained in the buffer pool by the total number of pages in the buffer pool. In general, a higher PLE indicates that the buffer pool is efficiently used and that data pages are not being flushed to disk too frequently. The recommended value for PLE is typically above 300 seconds, which means that pages are staying in the buffer pool for at least five minutes before being replaced. However, the optimal value for PLE depends on the specific workload and hardware configuration of the SQL Server instanc...

Tabular Data Stream (TDS) is a protocol used by Microsoft SQL Server to communicate with its client applications. TDS is a binary protocol that allows for efficient and secure communication between SQL Server and its clients. In this example, we will look at how TDS is used in SQL Server to retrieve data from a table. Let's say we have a table named "Employees" in our SQL Server database, with the following structure and data: CREATE TABLE Employees (    ID INT PRIMARY KEY,    Name VARCHAR(50),    Age INT,    Department VARCHAR(50) ); INSERT INTO Employees VALUES (1, 'John', 30, 'Sales'); INSERT INTO Employees VALUES (2, 'Mary', 25, 'Marketing'); INSERT INTO Employees VALUES (3, 'Bob', 40, 'IT'); Now, let's assume that we have a client application that needs to retrieve data from the "Employees" table. The client application sends a request to SQL Server using the TDS protocol. The request may look something l...

In SQL Server, the " lazy writer " is a background process that is responsible for flushing dirty pages from the buffer cache to disk. It operates on a low-priority basis and is designed to avoid interfering with user queries and other system processes. The lazy writer process periodically scans the buffer cache for dirty pages that have been modified but not yet written to disk. It then decides which pages to flush based on factors such as their age and frequency of access. The goal is to free up memory in the buffer cache for other data while also minimizing the impact on system performance. Here's an example to illustrate how the lazy writer works in SQL Server: 1) A user executes a query that modifies data in a table, such as updating a record. 2) SQL Server loads the relevant data pages into memory and modifies them. 3) The modified pages become dirty pages and are stored in the buffer cache. 4) If the buffer cache becomes too full, the lazy writer process scans ...

In SQL Server, a " dirty page " refers to a data page that has been modified in memory but not yet written back to disk. This occurs when a user or application modifies data in a database and those changes are made in memory, but the changes have not yet been persisted to the physical storage medium (such as a hard disk). Dirty pages are stored in the buffer cache until they are flushed to disk. When SQL Server needs to read data, it first checks if the data is already in the buffer cache. If the data is not in the cache, it must be read from disk and loaded into memory. Once the data is in memory, it can be modified by user or application queries. If the data is modified, it becomes a dirty page until it is flushed to disk. There are several reasons why SQL Server may delay flushing dirty pages to disk. For example, if there is sufficient memory available, SQL Server may choose to keep the dirty pages in memory to improve performance. Additionally, SQL Server uses a techniqu...

Dynamic Data Masking (DDM) is a security feature introduced in SQL Server 2016 that allows database administrators to hide sensitive data in query results while the data remains in the database. It is a way to prevent unauthorized access to sensitive data by obscuring it from view in query results. Here is an example of how to implement DDM in SQL Server: 1) First, let's create a sample table that contains some sensitive data: CREATE TABLE Customers (    CustomerID int PRIMARY KEY,    FirstName varchar(50),    LastName varchar(50),    EmailAddress varchar(100) ); 2) Next, we can use the DDM feature to mask the sensitive data in the "EmailAddress" column. We can use the "email()" function to mask the data with a generic email address: ALTER TABLE Customers ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION = 'email()'); This will mask the data in the "EmailAddress" column with a generic email address such as "****@*****.com" in quer...

Master Data Services (MDS) is a feature of Microsoft SQL Server that provides organizations with a platform for managing and maintaining their master data. Master data refers to the critical data entities that are common across an organization, such as customer information, product information, or financial information. MDS allows organizations to create a central repository for their master data, where the data can be standardized, validated, and enriched. This helps to ensure that the master data is accurate, consistent, and up-to-date, which can in turn improve business processes, increase efficiency, and reduce costs. Here is an example of how Master Data Services (MDS) can be used with SQL Server: Let's say a multinational corporation wants to centralize and standardize its product information, which is currently spread across multiple systems and departments. The corporation decides to use MDS to create a centralized repository for product data. Data Modeling : The corporat...

Synonyms in SQL Server are database objects that provide an alias or alternate name for an existing database object. Synonyms are similar to views, but instead of defining a SELECT statement to return data, they provide a pointer to the actual object. A synonym can be created for a variety of database objects, including tables, views, stored procedures, functions, and even other synonyms. Synonyms can be used to simplify database object naming, to provide more meaningful names, to abstract the underlying database structure, and to simplify code maintenance. Here are some examples of how to create and use synonyms in SQL Server: 1) Creating a table synonym : CREATE SYNONYM MyCustomers FOR SalesDB.dbo.Customers; This creates a synonym named "MyCustomers" that points to the "Customers" table in the "SalesDB" database. You can now refer to this table using the synonym name: SELECT * FROM MyCustomers; 2) Creating a view synonym : CREATE SYNONYM MySalesReport FO...

Linked Servers is a feature in Microsoft SQL Server that allows you to connect to another database server and perform queries against it as if it were a local database. Linked Servers can be created for various data sources, including other SQL Server instances, Oracle databases, MySQL databases, and even Excel spreadsheets. A linked server consists of several components:  Local server : The SQL Server instance where the linked server is created. Remote server : The data source that you want to access. This can be another SQL Server instance, or a different database platform. OLE DB provider : This is the software that allows SQL Server to communicate with the remote server. SQL Server supports several OLE DB providers, such as Microsoft OLE DB Provider for SQL Server, Microsoft OLE DB Provider for Oracle, and others. Linked server object : This is an object in SQL Server that defines the linked server and its properties, such as the name of the remote server, the OLE DB provider ...

Extended Events (XEvents) is a feature in SQL Server that provides a lightweight and flexible event infrastructure for monitoring and troubleshooting SQL Server. Extended Events allows users to collect and analyze performance data, diagnose issues, and troubleshoot problems in a highly customizable and granular way. Extended Events provides several benefits over traditional monitoring tools, including: Low overhead : Extended Events has a low overhead on the SQL Server instance, which allows it to be used for continuous monitoring without negatively impacting performance. Flexible : Extended Events allows users to define custom events and actions, which can be used to capture highly specific data related to SQL Server activity. Scalable : Extended Events can be used to monitor multiple SQL Server instances and can be configured to collect data from specific events, processes, or users. Extended Events can be used to monitor a wide range of SQL Server activity, including: Queries : Exte...

The Activity Monitor is a built-in feature in SQL Server Management Studio (SSMS) that provides a graphical view of the current SQL Server system activity, including running queries, locks, and resource utilization. The Activity Monitor displays real-time data and can help database administrators identify and troubleshoot performance issues in SQL Server. The Activity Monitor consists of five main sections: Overview : The Overview section displays a high-level view of the current SQL Server activity. It includes information about CPU usage, database I/O, and the number of active users and processes. Processes : The Processes section displays information about the active processes on the SQL Server instance. It includes details such as the process ID, the SQL statement being executed, the database being accessed, and the amount of CPU and memory being used by each process. Resource Waits : The Resource Waits section displays information about the resources that are currently being waite...