SQL Performance Tuning: Database Maintenance

Create a Maintenance Plan

Design a weekly database maintenance plan including index management and statistics updates for a sample database.

Expected output: A documented maintenance schedule with tasks and frequencies.

Hint: Consider fragmentation thresholds and update frequency.

Analyze Index Fragmentation

Write a query to check index fragmentation levels on a given table and decide whether to rebuild or reorganize.

Expected output: A report showing fragmentation percentages and recommended actions.

Hint: Use sys.dm_db_index_physical_stats in SQL Server.

Fragmented indexes cause the database engine to perform more I/O operations to retrieve data, slowing down query performance.

Statistics should be updated regularly, often daily or weekly, especially after significant data modifications to ensure the optimizer has accurate information.

Rebuilding drops and recreates the index to eliminate fragmentation completely, while reorganizing defragments the leaf level of the index without a full rebuild.