SQL Server Mastery
1. SQL Server Architecture & Basics
SQL Server Internals: How the Storage Engine works Relational Database Design & Normalization (1NF to 3NF) Data Types Mastery: Choosing the right type for performance
2. Advanced T-SQL Querying
Joins Deep Dive: Inner, Outer, Cross, and Self Joins Subqueries vs CTEs: Writing readable, high-performance code Window Functions: ROW_NUMBER, RANK, and LEAD/LAG Aggregations & Grouping Sets: Building complex reports Set Operators: UNION vs UNION ALL, INTERSECT, and EXCEPT
3. Indexing & Performance Tuning
Clustered vs Non-Clustered Indexes: The physical storage reality Covering Indexes & Included Columns: Reducing I/O costs Index Fragmentation: Why it happens and how to fix it Execution Plans: Reading the Query Optimizer's mind Statistics: Why 'Out of Date' stats kill performance SARGability: Writing queries that actually use indexes
4. Database Programmability
Stored Procedures: Security, Performance, and Best Practices User Defined Functions (UDF): Scalar vs Table-Valued Triggers: Auditing changes and the dangers of hidden logic Views & Indexed Views: Abstraction with performance Error Handling: TRY/CATCH and XACT_STATE()
5. Transactions & Concurrency
Transaction Isolation Levels: Read Uncommitted to Snapshot Locking & Blocking: Analyzing Deadlocks like a Pro Optimistic vs Pessimistic Concurrency
6. Administration & Security
SQL Server Security: Logins, Users, and Roles SQL Injection Prevention: Beyond simple parameterization Backup & Recovery Models: Full vs Simple vs Bulk-Logged Automating Maintenance: SQL Agent Jobs & Rebuilding Indexes
7. Modern SQL & Cloud
SQL Server & JSON: Storing and Querying semi-structured data Temporal Tables: Keeping track of data history automatically Introduction to Azure SQL: Database as a Service (PaaS) SQL Server Developer Interview: Junior to Senior Architect Level
SQL Server Mastery

Index Fragmentation: Why it happens and how to fix it

1 Views Updated 5/4/2026

Index Fragmentation

As data is modified, deleted, and inserted, your indexes become "Fragmented." This means the physical order of pages on the disk no longer matches the logical order of the index. This results in extra disk I/O and slower query performance.

1. Internal vs External Fragmentation

  • Internal (Page Fullness): Pages have too much empty space (low density). Happens when you delete many rows.
  • External (Logical Order): Pages are scattered across the disk in random order. Happens due to 'Page Splits' (inserting data in the middle of a sorted index).

2. The Solution: Reorganize vs Rebuild

  • REORGANIZE: Low impact. Defragments the leaf level of an index. It is online and doesn't lock the table. Use when fragmentation is 5% - 30%.
  • REBUILD: High impact. Drops and recreates the index from scratch. Much faster defrag but locks the table (unless using Enterprise edition). Use when fragmentation is > 30%.
ALTER INDEX ALL ON Users REBUILD;

4. Interview Mastery

Q: "How does 'Fill Factor' help prevent fragmentation?"

Architect Answer: "Fill Factor tells SQL Server to leave a certain percentage of empty space (e.g., 20%) on each page during a rebuild. This space acts as a 'Buffer'. When you insert a new row in the middle, it can fit on the existing page instead of forcing a costly **Page Split**. Setting a Fill Factor of 80 is common for tables that have frequent random inserts."

Previous Part Next Part
SQL Server Mastery
1. SQL Server Architecture & Basics
SQL Server Internals: How the Storage Engine works Relational Database Design & Normalization (1NF to 3NF) Data Types Mastery: Choosing the right type for performance
2. Advanced T-SQL Querying
Joins Deep Dive: Inner, Outer, Cross, and Self Joins Subqueries vs CTEs: Writing readable, high-performance code Window Functions: ROW_NUMBER, RANK, and LEAD/LAG Aggregations & Grouping Sets: Building complex reports Set Operators: UNION vs UNION ALL, INTERSECT, and EXCEPT
3. Indexing & Performance Tuning
Clustered vs Non-Clustered Indexes: The physical storage reality Covering Indexes & Included Columns: Reducing I/O costs Index Fragmentation: Why it happens and how to fix it Execution Plans: Reading the Query Optimizer's mind Statistics: Why 'Out of Date' stats kill performance SARGability: Writing queries that actually use indexes
4. Database Programmability
Stored Procedures: Security, Performance, and Best Practices User Defined Functions (UDF): Scalar vs Table-Valued Triggers: Auditing changes and the dangers of hidden logic Views & Indexed Views: Abstraction with performance Error Handling: TRY/CATCH and XACT_STATE()
5. Transactions & Concurrency
Transaction Isolation Levels: Read Uncommitted to Snapshot Locking & Blocking: Analyzing Deadlocks like a Pro Optimistic vs Pessimistic Concurrency
6. Administration & Security
SQL Server Security: Logins, Users, and Roles SQL Injection Prevention: Beyond simple parameterization Backup & Recovery Models: Full vs Simple vs Bulk-Logged Automating Maintenance: SQL Agent Jobs & Rebuilding Indexes
7. Modern SQL & Cloud
SQL Server & JSON: Storing and Querying semi-structured data Temporal Tables: Keeping track of data history automatically Introduction to Azure SQL: Database as a Service (PaaS) SQL Server Developer Interview: Junior to Senior Architect Level