Transcript Document
Re-Indexing - The quest of ultimate automation – sponsored by Dell Software PASS Database Administration Virtual Chapter – Oct 22 2014 Prakash Heda Database Team Lead at Advent Software Blog: www.SQLfeatures.com Twitter @PrakashHeda Email/Facebook: [email protected] MCDBA/MCITP Agenda Why Scope of session Review current process What this process handles Practices to be avoided Demo Why Space full on data drive Space full on log file Simple recovery during re-indexing SQL restarts taking very long time to recover Log backup fails, Backups drive full Re-indexing caused blocking to multiple session Scope Not performance tuning but maintenance Automating the process for highest uptime Top to bottom approach DBA provide recommendations Review current process Selective re-indexing - All or nothing? Rebuilding multiple databases Hardcoded (Do not manage new tables/ fill factor) Rebuild Vs Reorganize Online vs Offline indexing Job fails if one index fails Weekly schedule, maintenance period Read only database Separate script for each server What this process handles Smart re-indexing • Selective Reindexing • Handles online vs offline indexing • Reorganize Vs Rebuild • Default set to 90% for fill factor • Clustered and non-clustered index sequencing • Handles blocks/Locks efficiently • Automatically stop itself beyond a threshold What this process handles Job continues if one index fails Handles Read only database Post troubleshooting logs Can be scheduled daily Minimize system downtime due to log disk full • Automated log backups • Network backups Centrally managed Can be implemented on most of the SQL servers regardless of application type or data size Practices to be avoided Simple Recovery is not an option Truncating transaction Log Shrinking data file Shrinking log file Summary Don’t take everything you hear as true Not tested against XML/Spatial indexes Better with each release Keep up to date Engage with user groups Blog: www.SQLfeatures.com Twitter @PrakashHeda Facebook/Email: [email protected]