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]