Database Maintenance

Routine database maintenance procedures.

Maintenance Schedule

Task	Frequency	Window
Index maintenance	Weekly	Sunday 2-4 AM
Statistics update	Weekly	Sunday 2-4 AM
Query Store cleanup	Monthly	First Sunday
Log review	Weekly	Monday morning

Index Maintenance

Check Index Fragmentation

SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;

Rebuild Fragmented Indexes

For fragmentation > 30%:

ALTER INDEX [IndexName] ON [TableName] REBUILD;

For fragmentation 10-30%:

ALTER INDEX [IndexName] ON [TableName] REORGANIZE;

Automated Maintenance

Use Ola Hallengren maintenance scripts:

EXECUTE dbo.IndexOptimize
    @Databases = 'ESHARS',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE',
    @FragmentationLevel1 = 10,
    @FragmentationLevel2 = 30;

Statistics Maintenance

Update All Statistics

EXEC sp_updatestats;

Update Specific Table

UPDATE STATISTICS [Visit] WITH FULLSCAN;

Query Store Maintenance

Check Query Store Size

SELECT
    actual_state_desc,
    desired_state_desc,
    current_storage_size_mb,
    max_storage_size_mb
FROM sys.database_query_store_options;

Purge Old Data

ALTER DATABASE ESHARS SET QUERY_STORE CLEAR;
-- Or purge specific queries
EXEC sp_query_store_remove_query @query_id = 12345;

Log Management

Check Log Size

DBCC SQLPERF(LOGSPACE);

Shrink Log (if needed)

Caution

Only shrink after proper backup. Excessive shrinking can cause fragmentation.

-- Backup log first
BACKUP LOG ESHARS TO DISK = 'NUL';

-- Shrink
DBCC SHRINKFILE (ESHARS_Log, 1024);

Performance Monitoring

Check Long-Running Queries

SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.total_elapsed_time / 1000 AS elapsed_sec,
    t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.total_elapsed_time > 30000
ORDER BY r.total_elapsed_time DESC;

Check Blocking

SELECT
    blocking_session_id,
    session_id,
    wait_type,
    wait_time / 1000 AS wait_sec
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

Azure-Specific Tasks

DTU Monitoring

Check Azure Portal → SQL Database → Metrics:

• DTU percentage
• Data IO percentage
• Log IO percentage

Scale Recommendations

If consistently > 80% DTU:

1. Review query performance
2. Optimize expensive queries
3. Consider scaling up

Automatic Tuning Review

1. Azure Portal → SQL Database
2. Automatic tuning
3. Review recommendations
4. Apply beneficial indexes

Pre-Maintenance Checklist

• Notify stakeholders
• Verify backup exists
• Check current performance baseline
• Confirm maintenance window

Post-Maintenance Checklist

• Verify database accessible
• Check application connectivity
• Review performance metrics
• Document completed tasks

---

Run maintenance during scheduled windows to minimize user impact.