What you will learn
Maintenance should address a measured need. This lesson corrects the old slide sequence that treated shrink, rebuild, and cleanup as automatic recurring tasks. A useful routine starts with recoverability and integrity, then considers statistics and index work based on query behavior and available resources.
Before you begin
Use an isolated SQL Server 2022 Developer lab on host1.sample.com and a current SQL Server Management Studio client. Developer edition is for nonproduction use. Connect with a dedicated lab administrator for setup; application access is tested separately. The examples use TutorialDB and synthetic rows only. GO is a client batch separator, not a Transact-SQL statement. Sample paths are on the database server, not your workstation. Use TutorialDB with dbo.Events from the creation lesson. A one-row fixture demonstrates syntax but does not justify index maintenance on performance grounds.
1. Check integrity and preserve evidence
DBCC CHECKDB(N'TutorialDB') WITH NO_INFOMSGS;
Schedule integrity checks with enough CPU, I/O, and temporary space. If errors appear, preserve the output, investigate storage and recent backups, and assess restore options. A repair mode that permits data loss is not a normal maintenance step.
2. Inspect index characteristics
USE TutorialDB;
GO
SELECT index_id,page_count,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(),OBJECT_ID(N'dbo.Events'),NULL,NULL,'LIMITED');
Interpret fragmentation alongside page count, page density, query patterns, and storage behavior. Tiny indexes rarely benefit from rebuilding. Fixed percentage thresholds alone do not establish that a maintenance operation will improve a workload. Capture a query baseline before changing anything.
3. Practice targeted syntax
UPDATE STATISTICS dbo.Events;
-- Optional syntax exercise on the isolated fixture only:
ALTER INDEX PK_Events ON dbo.Events REORGANIZE;
Statistics help the optimizer estimate cardinality. Reorganizing an index does not update statistics. A rebuild can consume substantial log and temporary space and may block; online capabilities depend on edition, index type, and operation. To practice rebuild syntax separately use ALTER INDEX PK_Events ON dbo.Events REBUILD, not the malformed table-only statement in the older material.
4. Keep growth and retention deliberate
Exclude recurring shrink and AUTO_SHRINK from the routine. Capacity consumed during normal workload is generally needed again. Define backup retention from the full recovery chain and recovery objective; a differential or log backup can depend on older media. Deleting msdb history does not delete backup files and should not be mistaken for reclaiming backup storage.
5. Read the example output
The following is illustrative, normalized output, not a transcript from a customer system. Values depend on your release and lab state.
Illustrative maintenance record:
Integrity check: no errors reported
Fixture query: unchanged
Index maintenance decision: skipped for tiny index
Routine shrink: disabled
This is an acceptance record to fill from observations, not a SQL Server-generated transcript.
Verify the result
Read the fixture after any index operation and compare representative query duration and logical reads. Confirm backups still complete and the log has adequate headroom. Keep a failed integrity check visible rather than allowing later successful steps to hide it.
Troubleshooting
A maintenance job that succeeds without improving queries may be unnecessary. Investigate changed plans and data distribution before rebuilding everything. For long-running work, inspect blocking and log space before scheduling additional maintenance on top of it.
Recovery and next steps
Cancel unnecessary work using the established operations procedure and allow rollback to finish where applicable. Reschedule only after measuring resource needs. If corruption is found, prefer a verified recovery path and expert diagnosis over an automatic repair.