SQL

Understanding Fragmentation and its effects on SQL

Index fragmentation in SQL Server arises from INSERT/UPDATE/DELETE operations, leading to uneven page fullness, extra I/O, and slower scans—especially on large tables. Detect it using sys.dm_db_index_physical_stats; resolve with ALTER INDEX REBUILD (prefer over REORGANIZE for most cases), SORT_IN_TEMPDB=ON, MAXDOP=1, and appropriate fillfactor to rebuild pages contiguously. Prevent excessive fragmentation via pre-allocating data/log files, batching DML, and avoiding OS-level NTFS defragmentation

SQL Server Always On Availability Groups: Worker Thread Capacity Planning

Worker thread capacity is a critical factor when planning SQL Server Always On Availability Groups, as replicas share an HADR pool while using unshared log capture, send, and redo threads per database. Minimum threads required = (Database Count × (1 + Secondary Replicas)) + 1, often consuming a significant portion of available threads (max worker threads minus 40). Poor planning risks thread exhaustion, failed database joins, and the need to drop/recreate AGs in production.