1.What is resource database?
The Resource Database (mssqlsystemresource) in SQL Server is a hidden, read-only system database that
contains all the system objects (such as system stored procedures, views, and functions) that come with
SQL Server.Key Features of the Resource Database
Stores System Objects: It contains definitions of system objects but not user data or user objects.
Hidden from Users: It does not appear in sys.databases or sys.master_files and cannot be accessed
directly.
Facilitates Upgrades: When SQL Server is upgraded, the Resource Database file
(mssqlsystemresource.mdf) is replaced instead of modifying system tables, making upgrades faster.
Shared by All Databases: The system objects defined in the Resource Database appear in each database
under sys. schema but are physically stored in the Resource Database.
Resource Database File LocationN
Importance in Disaster Recovery
Restoring the Resource Database is not possible directly; you must restore it by replacing the
mssqlsystemresource.mdf and mssqlsystemresource.ldf files from a backup.
A corrupted or missing Resource Database can prevent SQL Server from starting.
2.Why Can We Have Only One Clustered Index in SQL Server?
A clustered index determines the physical order of data rows in a table. Since a table’s rows can only be
stored in one physical order, SQL Server allows only one clustered index per table.
3.SQL Server Backup Failures – Causes and Solutions
Insufficient Disk Space- - (ERROR MESSAGE) Operating system error 112 (There is not enough space on the disk)
-Permission Issues- - (ERROR MESSAGE) Cannot open backup device. Operating system error 5 (Access is denied.)
-Backup File in Use / Locked- (ERROR MESSAGE) Exclusive access could not be obtained because the database is in
use.
-Database in Use (Full Recovery Mode and Log Backup Issues)- - (ERROR MESSAGE) BACKUP LOG cannot be
performed because there is no current database backup.
- Backup Device Failure (Corrupt or Read-Only Disk)- (ERROR MESSAGE) Write failure on backup device. The device
is not ready.
4. Steps to Improve Disk Performance
· Use SSD or NVMe drives instead of HDDs.
· Separate Data, Log, and TempDB on different disks.
· Use RAID 10 for best performance.
· Increase TempDB files (1 per core, max 8).
· Preallocate database files & set proper growth settings.
· Enable index maintenance & page compression.
· Monitor disk latency & optimize queries.
5.What is a Boot Page?
The Boot Page is a special system page in SQL Server that stores critical metadata about a database. It
contains essential information such as the database version, creation date, compatibility level, and
recovery settings.
Boot Page Location:The Boot Page is always stored in Page ID = 9 of the Primary Data File (MDF).It is
present in every database and is crucial for SQL Server to recognize and manage the database.
Importance of the Boot PageThe boot page is essential for SQL Server because:
It identifies and initializes the database at startup.
It helps in database recovery in case of corruption.
It stores SQL Server version compatibility for upgrades.
If the Boot Page is corrupted, the database may fail to start or become inaccessible.
6.How to Recover from Boot Page Corruption?
1 Check for Corruption
Run DBCC CHECKDB to verify if the boot page is damaged:
2 Restore from Backup
If corruption is detected, restore from a recent backup:
3Use Emergency Repair (If No Backup Available)
If no backup is available, try setting the database to EMERGENCY mode and repair it
7.What is a Deadlock?
A deadlock occurs when two or more queries compete for the same resources and end up waiting
indefinitely for each other to release the locks. SQL Server detects this situation and automatically
terminates one query (the deadlock victim) to allow the other to proceed.
✅ Detect Deadlocks using Extended Events, Error Logs, or Trace Flags.
✅ Prevent Deadlocks by following consistent table access order and keeping transactions short.
✅ Use NOLOCK, UPDLOCK, or Snapshot Isolation for performance tuning.
✅ Monitor Deadlocks Regularly to improve system performanc
8.What is a Shared Lock?
A Shared Lock (S Lock) in SQL Server allows multiple transactions to read a resource (row, page, or table)
simultaneously but prevents modifications while the lock is held.
By query-
SELECT * FROM Employees WITH (HOLDLOCK);
-Key Characteristics of Shared Locks
✅ Multiple transactions can read the same data at the same time.
✅ Prevents writes (updates, deletes) until the shared lock is released.
✅ Automatically released after the read operation (unless inside a transaction).
✅ Upgradable to an Exclusive Lock if a transaction decides to modify the data.
What is Blocking in SQL Server?
Blocking in SQL Server occurs when one session (SPID) holds a lock on a resource (row, page, table) and
another session waits for that lock to be released.
-Blocking is normal in SQL Server but can cause performance issues if not handled properly.
-Difference Between Blocking & Deadlock:
Blocking: One query waits for another query to complete.
Deadlock: Two queries wait for each other → SQL Server forcefully terminates one.
Causes of Blocking in SQL Server
1. Long-Running Transactions
2 .Missing Indexes (Causing Table Scans)
What is Lock Escalation?
Lock escalation is the process where SQL Server automatically converts multiple fine-grained locks (row
or page locks) into a coarser-grained lock (table lock) when too many locks are acquired.
Why Does SQL Server Escalate Locks?
To reduce memory usage (tracking too many row/page locks consumes memory).
To improve performance by reducing lock management overhead.
However, it can cause blocking if one session holds a table lock while others need access to different
rows.
9.What are Wait Types in SQL Server?
In SQL Server, Wait Types indicate why a query or session is waiting before execution continues.
Why are Wait Types Important?
Help diagnose performance bottlenecks.
Identify resource contention issues (CPU, I/O, Locks, etc.).
Provide insights into query tuning and optimization.
10.What is Data Masking?
Data Masking is a security feature in SQL Server that hides sensitive data from unauthorized users while
allowing them to work with the data for testing, development, or reporting purposes.
11.How Instant File Initialization Works
Normally, when SQL Server creates or grows a database file, it fills the new space with zeros (to prevent
reading old deleted data).
IFI skips this zeroing process, allowing SQL Server to use the new space immediately.
Transaction log files (LDF) are not affected—they are always zeroed out for crash recovery safety.
1️2.What is an Isolation Level?
Isolation levels control how transactions interact with each other to maintain data consistency and
concurrency.
Higher isolation = More consistency, less concurrency
Lower isolation = More concurrency, potential data anomalies
13.What is Database Compatibility Level in SQL Server?
Database Compatibility Level determines how SQL Server interprets and executes queries for a specific
database. It allows databases to run on a newer SQL Server version while maintaining behavior from an
older version.
Why is Compatibility Level Important?
✅ Ensures backward compatibility when upgrading SQL Server.
✅ Allows gradual migration from older versions.
✅ Enables new SQL Server features when using the latest level.
Types of pages
Data Page (1) Stores table row data
Index Page (1 & 2)-- Stores index key values and pointers
IAM Page (10)(Index Allocation Map Tracks extents allocated to tables or indexes
PFS Page (11)(Page Free Space) Tracks space availability in pages
GAM Page (8)(Global Allocation Map Tracks allocated/free extents
SGAM Page (9)Shared Global Allocation Map) Tracks mixed extents with free space
Boot Page (13) Stores database metadata
ML PageMixed Extent Allocation Stores mixed extent allocation info
Text/Image Page (3) Stores LOB data (VARCHAR(MAX), XML, etc.)
DCM Page (6) Differential Changed Map -- Tracks modified extents for differential backups
BCM Page (7) Bulk Channged map Tracks bulk-logged changes
File Header Page (15) Stores metadata for database files
