Syllabus

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
Lesson 11 of 30 37% of course

Index Fragmentation: Why it happens and how to fix it

17 · 8 min · 5/23/2026

Sign in to track progress and bookmarks.

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

Interview Q&A for this course

Test your knowledge

Quizzes linked to this course—pass to earn certificates.

SQL Server — Practice Quiz
Test your understanding of SQL Server Tutorial. T-SQL, schema design, indexes, and stored …
Intermediate 15 min
PostgreSQL — Practice Quiz
Test your understanding of PostgreSQL Tutorial. Open-source relational database with advan…
Intermediate 15 min
Oracle SQL — Practice Quiz
Test your understanding of Oracle SQL Tutorial. SQL and PL/SQL for Oracle Database develop…
Intermediate 15 min
MySQL — Practice Quiz
Test your understanding of MySQL Tutorial. Install MySQL, write queries, and connect from …
Intermediate 15 min
MongoDB — Practice Quiz
Test your understanding of MongoDB Tutorial. Document databases, BSON, and the MongoDB C# …
Intermediate 15 min
Browse all quizzes
Previous Next
SQL Server Mastery

On this page

1. Internal vs External Fragmentation 2. The Solution: Reorganize vs Rebuild 4. Interview 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