Differences between Clustered and Non-clustered Indexes ?

What all required to setup Azure load balancer ?

1. Setup Frontend IP configuration ( Floating IP=AG Listener IP)
2. Backend Pools ( Add VM's )
3. Setup Health Probes and Rules

Health  Probe = 10 seconds 

Interval =5,

Unhealthy threshold =2 (Bare minimum)

Disadvantages :  

Azure Load Balancers has to wait minimum of 10 Seconds to trigger the action 

What are the types of Load Balancers SKU's ?

Basic   =  If your Azure VM's part of Azure high availability Sets

Standard = If your Azure VM's part of Azure high availability Zone

 

What happens if Jr DBA disabled cluster Index ?

You cannot read data from the table. The query processor is unable to produce a plan because the index is disabled. 

Non-clustered indexes on the table are also unavailable because they depend on the clustered index (for clustered tables).

If Index is deleted, still you can read data but query performance is slower .

What are the Differences between Covering Index and Included Index in SQL server?

Differences between Business-critical service tier & General-purpose service tier in Azure SQL

What are the differences between Business-critical service tier & General-purpose service tier in Azure SQL ?

Why Load Balancer required in Azure VM AAG ?

In an on-premises SQL Server Always On Availability Group, the Windows Server Failover Cluster moves the Listener's virtual IP between cluster nodes during failover using standard network protocols such as ARP, so no load balancer is needed. 

In Azure, VMs cannot move or claim a virtual IP in the same way because Azure uses software-defined networking. Therefore, an Azure Load Balancer (or a DNN listener in newer deployments) provides a stable listener endpoint and routes client connections to the current primary replica after failover.

Here's why it's needed:

1. Availability Group Listener needs a single endpoint

Applications should not connect directly to individual SQL Server VMs because the primary replica can change during failover.  Instead, they connect to an Availability Group (AG) Listener, which provides:

• A single DNS name (for example, SQLAGListener)
• A single IP address

The listener always points to whichever SQL Server instance is currently the primary replica.

2. Azure networking works differently from on-premises

In an on-premises Windows Server Failover Cluster:

• The cluster can move a virtual IP (VIP) between nodes during failover.
• The network automatically updates ARP tables, so clients reach the new active node. (ARP =Address Resolution Protocol , kind of mapping b/w IP with MAC/Physical address)

In Azure

• You cannot move an IP address between VM network interfaces the same way.
• Azure virtual networking does not support the traditional floating IP mechanism used by Windows Failover Clustering.  (Azure never exposes MAC/Physical address to public, that's reason we need Load balancer 

Because of this limitation, Azure uses an Azure Load Balancer to present a stable frontend IP while directing traffic to the active SQL Server VM.

3. Health probes identify the active node

The Azure Load Balancer continuously checks a configured health probe.

• Primary replica → responds to the probe.
• Secondary replica → does not respond.

The load balancer sends SQL traffic only to the VM that is healthy (the current primary).

Example:

Application
      |
AG Listener (listener.contoso.com)
      |
Azure Load Balancer
      |
-----------------------------
|                           |
VM1 (Primary)         VM2 (Secondary)
Probe: Healthy        Probe: Unhealthy

If failover occurs:

Application
      |
AG Listener
      |
Azure Load Balancer
      |
-----------------------------
|                           |
VM1 (Secondary)       VM2 (Primary)
Probe: Unhealthy      Probe: Healthy

The application continues using the same listener name without needing any connection string changes.

4. Enables automatic failover

Without a load balancer:

• Clients would connect directly to a specific VM.
• After failover, connections would fail until the application was reconfigured or DNS updated.

With a load balancer:

• The frontend IP remains the same.
• Traffic is automatically redirected to the new primary.
• Existing applications continue using the same listener.

5. Why Floating IP (Direct Server Return) is enabled

The load-balancing rule is typically configured with Floating IP (Direct Server Return) enabled because:

• SQL Server Availability Group listeners require this behavior.
• It allows the listener IP to function correctly with the Windows Failover Cluster.

Resource DB in SQL Server

Resource DB in SQL Server 

It is introduced in SQL Server 2005.

It is one of the SQL Server system Databases but we can’t see it use SQL Server management studio.

It is a read-only database that contains all the system objects that are included with SQL Server.

The physical file are mssqlsystemresource.mdf and mssqlsystemresource.ldf.

This Files of Resource DB are located in the SQL Server default installation path (<drive>:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Binn\)

Resource Database makes upgrading SQL Server to a New Version an easier and a faster procedure. In the previous versions of SQL Server, upgrading to a new version required dropping and creating system objects. However, since the resource database contains all system objects, an upgrade can now be achieved simply copying the resource database (mssqlsystemresource.mdf and mssqlsystemresource.ldf) files to the local server

This Files should not be moved.

Changing the location of the resource database is not supported or recommended.

DBAs should backup Resource Database along with other System Databases in SQL Server as part of Disaster Recovery and most importantly document the location of each and every system and user databases.

SQL Server cannot backup Resource Database hence DBA will have to perform file-based or disk-based backup by considering mssqlsystemresource.mdf and mssqlsystemresource.ldf files as if they are .EXE files. Using the XCOPY, ROBOCOPY or COPY command you can copy the .MDF and .LDF files even when SQL Server is up and running.

Restoring Resource Database means copying mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the respective location which you have documented.

In case if there is a hardware failure and you need to rebuild your environment by restoring master database on to a new drive location. Then before restoring master database using WITH MOVE option a copy of Resource Database’s .mdf and.ldf files should be present.

Conclusion:

The resource database is the fifth, "hidden" database that is a read-only database and contains all system objects of SQL Server. It is used to make the upgrade process of SQL Server faster and easier. This database cannot be backed up and restored (using traditional SQL Server methods) and also, the location of its data and log files cannot be changed. It has a fixed database ID of 32767, which is the maximum number of databases per instance.

Ghost records and cleanup task

Ghost records

Records that are deleted from a leaf level of an index page aren't physically removed from the page - instead, the record is marked as 'to be deleted', or ghosted. This means that the row stays on the page but a bit is changed in the row header to indicate that the row is really a ghost. This is to optimize performance during a delete operation. Ghosts are necessary for row-level locking, but are also necessary for snapshot isolation where we need to maintain the older versions of rows.

Ghost record cleanup task

Records that are marked for deletion, or ghosted, are cleaned up by the background ghost cleanup process. This background process runs sometime after the delete transaction is committed, and physically removes ghosted records from pages. 

The ghost cleanup process runs automatically on an interval (every 5 seconds for SQL Server 2012+, every 10 seconds for SQL Server 2008/2008R2) and checks to see if any pages have been marked with ghost records. If it finds any, then it goes and deletes the records that are marked for deletion, or ghosted, touching at most 10 pages with each execution.

Disable the ghost cleanup

On high-load systems with many deletes, the ghost cleanup process can cause a performance issue from keeping pages in the buffer pool and generating IO.As such, it is possible to disable this process with the use of trace flag 661. However, there are performance implications from disabling the process.

Disabling the ghost cleanup process can cause your database to grow unnecessarily large and can lead to performance issues. Since the ghost cleanup process removes records that are marked as ghosts, disabling the process will leave these records on the page, preventing SQL Server from reusing this space. This forces SQL Server to add data to new pages instead, leading to bloated database files, and can also cause page splits. Page splits lead to performance issues when creating execution plans, and when doing scan operations.

WARNING: Disabling the ghost cleanup process is not generally recommended. Doing so should be tested thoroughly in a controlled environment before being implemented permanently in a production environment.

T-log file and Virtual Log Files (VLFs) and Log blocks and Write-ahead transaction logging

Transaction log file : The database transaction log maps over one or more physical files. Conceptually, the log file is a string of log records. Physically, the sequence of log records is stored efficiently in the set of physical files that implement the transaction log. There must be at least one log file for each database.

The SQL Server transaction log operates logically as if the transaction log is a string of log records. Each log record is identified by a log sequence number (LSN). The log sequence number (LSN) of this first log record is known as the minimum recovery LSN (MinLSN).

The basic structure of an LSN is [VLF ID:Log Block ID:Log Record ID]

Here's an example of an LSN:00000031:00000da0:0001, look at the output of sys.dm_db_log_info DMV and examine the vlf_create_lsn column.

Many types of operations are recorded in the transaction log. These operations include:

• The start and end of each transaction.

• Every data modification (insert, update, or delete). Modifications include changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.

• Every extent and page allocation or deallocation.

• Creating or dropping a table or index.

Virtual Log Files (VLFs)

The SQL Server Database Engine divides each physical log file internally into several virtual log files (VLFs). Virtual log files have no fixed size, and there's no fixed number of virtual log files for a physical log file. The Database Engine chooses the size of the virtual log files dynamically while it's creating or extending log files. The Database Engine tries to maintain a few virtual files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. The size or number of virtual log files can't be configured or set by administrators.

VLF's creation formula has been changed for SQL Server 2014 (12.x) and later versions

·       If growth < 64 MB, create 4 VLFs that cover the growth size (for example, for 1-MB growth, create 4 VLFs of size 256 KB).

·       If growth > 64 MB up to 1 GB, create 8 VLFs that cover the growth size (for example, for 512-MB growth, create 8 VLFs of size 64 MB).

·      If growth > 1 GB, create 16 VLFs that cover the growth size for example, for 8-GB growth, create 16 VLFs of size 512 MB).

What is the impact of too many VLF’s? 

One or more databases take a very long time to finish recovery during SQL Server startup.

Restoring a database takes a very long time to complete.

Attempts to attach a database take a very long time to complete.

When you try to set up database mirroring, you encounter error messages 1413, 1443, and 1479, indicating a timeout.

You encounter memory-related errors like 701 when you attempt to restore a database.

Log blocks: Each VLF contains one or more log blocks. Each log block consists of the log records.

A log block is the basic unit of I/O for transaction logging.

In summary, a log block is a container of log records that's used as the basic unit of transaction logging when writing log records to disk.

Log Truncation: Log truncation deletes inactive virtual log files from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the physical transaction log. If a transaction log is never truncated, it will eventually fill all the disk space that is allocated to its physical log files. 

However, before the log can be truncated, a checkpoint operation must occur. 

A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk. When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable. Thereafter, a log truncation can free the inactive portion.           

Write-ahead transaction logging : SQL Server uses a write-ahead logging (WAL) algorithm, which guarantees that no data modifications are written to disk before the associated log record is written to disk. This maintains the ACID properties for a transaction.

To understand how the write-ahead log works, it's important for you to know how modified data is written to disk. SQL Server maintains a buffer cache (also called a buffer pool) into which it reads data pages when data must be retrieved. When a page is modified in the buffer cache, it isn't immediately written back to disk; instead, the page is marked as dirty. A data page can have more than one logical write made before it's physically written to disk. For each logical write, a transaction log record is inserted in the log cache that records the modification. The log records must be written to disk before the associated dirty page is removed from the buffer cache and written to disk. 

How differential backup works and impact of copy_only backup on Differential backup

Overview of differential backups

A differential backup captures the state of any extents (collections of eight physically contiguous pages) that have changed between when the differential base was created and when the differential backup is created.

The following illustration shows how a differential backup works. The figure shows 24 data extents, 6 of which have changed. The differential backup contains only these six data extents. The differential backup operation relies on a bitmap page that contains a bit for every extent. For each extent updated since the base, the bit is set to 1 in the bitmap.

Note 

The differential bitmap is not updated by a copy-only backup. Therefore, a copy-only backup does not affect subsequent differential backups.

Query Store

Query Store:  This is used for performance troubleshooting and check the optimal execution plan used and if a query is taking longer than expected time.

Description

It is a relatively new feature introduced in SQL 2016 (every SQL Server edition). 

It is basically a SQL Server “flight recorder” or “black box”, capturing a history of executed queries, query runtime execution statistics, execution plans etc. against a specific database.

Query Store is per-database-level feature which means that it can be enabled on every SQL database separately by using SQL Server Management Studio or T-SQL. It is not an instance level setting.

This information helps in identifying performance problems caused by query plan changes and troubleshooting by quickly finding performance differences, even after SQL Server restart or upgrade.

All data that SQL Server Query Store capture are stored on disk.

Here are the common scenarios where the SQL Server Query Store feature can be useful:

 Find the most expensive queries for CPU, I/O, Memory etc.

Get full history of query executions.

Get information about query regressions (a new execution plan generated by query engine is worse than the older one).

Quickly find performance regression and fixing it by forcing the previous query plan whose performances are much better than a newly generated plan

Determine how many times a query was executed in the given range of time.