際際滷

際際滷Share a Scribd company logo

Index Reorganization and Rebuilding for Success

Download as PPTX, PDF
Dean Willson
Dean Willson

A process and accompanying tools to make Index reorganization/rebuilding successful. Applicable on small databases and multi-Tb databases with over 20,000 tables (including examples of what can go horribly wrong without the right preparation). Bonus: PowerShell monitoring script included.

1 of 32
Downloaded 10 times
INDEX REORGANIZATION AND REBUILDING FOR SUCCESS An end-to-end Enterprise re-indexing process
ABSTRACT Dean will introduce a process and accompanying tools to make Index reorganization/rebuilding successful. Applicable on small databases and multi-Tb databases with over 20,000 tables (including examples of what can go horribly wrong without the right preparation). Bonus: PowerShell monitoring script included.
AGENDA Indexing - What can go wrong? Process Overview Pre-requisites (initial setup/configuration) Pre-index preparation Running Index optimization Index progress monitoring (awesome PowerShell script)
WHAT COULD POSSIBLY GO WRONG?
WHAT COULD POSSIBLY GO WRONG? Lose your job BAD Disk out of space Tlog or Database files Tlog backup files Exponential Tlog growth Temporarily pain Tlog or database growth initialization causing application timeout
CONSIDERATIONS Enterprise Edition Online Index rebuild Tlog auto-growth settings % or size Size planning Full backups during re-index Tlog will not truncate during a full backup even if log backups are being taken Big database (4 hr backup + recovery) use differentials during re-index? TODO: Add log_reuse_wait_desc to Powershell monitoring tool when log used percent is > warning threshold. Re-Index vs. Re-Organize MaxDOP? TempDb
PROCESS OVERVIEW
PROCESS OVERVIEW
INITIAL SYSTEM SETUP/CONFIGURATION Pre-requisites to using the proposed (demo) process
OLA HALLENGRENS INDEX MAINTENANCE SCRIPT https://ola.hallengren.com/ Optional, but recommended CommandLog table
OLAS INSTALL SCRIPTS Demo uses a DBA AdminUtility database (instead of master) @CreateJobs set to Yes @LogToTable set to Yes
SQL AGENT JOBS Olas scripts will add these jobs The IndexOptimize USER_DATABASES job is the one of interest for indexing
ADMINISTRATIVE DATABASE OR TABLES
SP_WHOISACTIVE Adam Machanics sp_WhoIsActive script is a new and improved version of sp_who2 http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx Shows current connections, % progress of index re-organization, and much more
SCHEDULED TASK
SCHEDULED TASK - TRIGGER How often to run during indexing
SCHEDULED TASK - ACTION ExecutionPolicy Bypass
SCHEDULED TASK - RUN
PRE-INDEX PREPARATION
PRE-INDEX PREPARATION FOR VLDB Tlog backup schedule Shorten duration Full backup schedule Temporarily switch to differential backups Open monitoring tools or schedule the Sweet Powershell script
INDEX MAINTENANCE
INDEX MAINTENANCE Ola Hallengren Index Maintenance Solution (recommended) Queryable maintenance database (read: Sweet PowerShell monitoring) Post maintenance review (% fragmentation, re-org vs. rebuild, etc.) Maintenance Plans
PROGRESS MONITORING
INDEX MONITORING OLD SCHOOL DMV Disk Usage Windows Explorer .trn backup file sizes Windows Explorer tlog backup duration sp_who2
INDEX MONITORING LIKE A BOSS Powershell Script sp_WhoIsActive (by Adam Mechanic) Queries Completed indexes Current index rebuild progress (%, rows remaining) Current index reorg progress (%) Disk space used (Database files and Tlog Tlog backup size and backup duration Windows Scheduler Email
TLOG ABOUT TO GROW Transactions are paused while TLOG file is growing Truncation should prevent growth depending on initial size Autogrowth settings
DATA FILE ABOUT TO GROW
TLOG ACTIVITY Re-org (~1 Gb/minute) Re-build (~200Kb/minute)
TLOG BACKUP DURATION Durations shown in the query dont include the WithVerify time I usually use a 3X multiplier for estimating actual tlog backup time Look at start time of next backup
INDIVIDUAL INDEX PROGRESS Re-Org Re-Build
WARNING SIGNS VLDB data file about to grow TLOG file about to grow TLOG backup duration + Verify taking longer than backup schedule schedule Log file not truncating (excessive growth) Wait times on sp_WhoIsActive File sizes growing larger than available disk space
DEAN WILLSON Director of Application Development, Aunt Millies Bakeries Long time member of NUFW Former President of fwPASS Twitter: @deanwillson Blogs: dean-o.blogspot.com, practicalhoshin.blogspot.com LinkedIn: http://linkedin.com/in/deanwillson

More Related Content

Index Reorganization and Rebuilding for Success

  • 1. INDEX REORGANIZATION AND REBUILDING FOR SUCCESS An end-to-end Enterprise re-indexing process
  • 2. ABSTRACT Dean will introduce a process and accompanying tools to make Index reorganization/rebuilding successful. Applicable on small databases and multi-Tb databases with over 20,000 tables (including examples of what can go horribly wrong without the right preparation). Bonus: PowerShell monitoring script included.
  • 3. AGENDA Indexing - What can go wrong? Process Overview Pre-requisites (initial setup/configuration) Pre-index preparation Running Index optimization Index progress monitoring (awesome PowerShell script)
  • 5. WHAT COULD POSSIBLY GO WRONG? Lose your job BAD Disk out of space Tlog or Database files Tlog backup files Exponential Tlog growth Temporarily pain Tlog or database growth initialization causing application timeout
  • 6. CONSIDERATIONS Enterprise Edition Online Index rebuild Tlog auto-growth settings % or size Size planning Full backups during re-index Tlog will not truncate during a full backup even if log backups are being taken Big database (4 hr backup + recovery) use differentials during re-index? TODO: Add log_reuse_wait_desc to Powershell monitoring tool when log used percent is > warning threshold. Re-Index vs. Re-Organize MaxDOP? TempDb
  • 9. INITIAL SYSTEM SETUP/CONFIGURATION Pre-requisites to using the proposed (demo) process
  • 10. OLA HALLENGRENS INDEX MAINTENANCE SCRIPT https://ola.hallengren.com/ Optional, but recommended CommandLog table
  • 11. OLAS INSTALL SCRIPTS Demo uses a DBA AdminUtility database (instead of master) @CreateJobs set to Yes @LogToTable set to Yes
  • 12. SQL AGENT JOBS Olas scripts will add these jobs The IndexOptimize USER_DATABASES job is the one of interest for indexing
  • 14. SP_WHOISACTIVE Adam Machanics sp_WhoIsActive script is a new and improved version of sp_who2 http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx Shows current connections, % progress of index re-organization, and much more
  • 16. SCHEDULED TASK - TRIGGER How often to run during indexing
  • 17. SCHEDULED TASK - ACTION ExecutionPolicy Bypass
  • 20. PRE-INDEX PREPARATION FOR VLDB Tlog backup schedule Shorten duration Full backup schedule Temporarily switch to differential backups Open monitoring tools or schedule the Sweet Powershell script
  • 22. INDEX MAINTENANCE Ola Hallengren Index Maintenance Solution (recommended) Queryable maintenance database (read: Sweet PowerShell monitoring) Post maintenance review (% fragmentation, re-org vs. rebuild, etc.) Maintenance Plans
  • 24. INDEX MONITORING OLD SCHOOL DMV Disk Usage Windows Explorer .trn backup file sizes Windows Explorer tlog backup duration sp_who2
  • 25. INDEX MONITORING LIKE A BOSS Powershell Script sp_WhoIsActive (by Adam Mechanic) Queries Completed indexes Current index rebuild progress (%, rows remaining) Current index reorg progress (%) Disk space used (Database files and Tlog Tlog backup size and backup duration Windows Scheduler Email
  • 26. TLOG ABOUT TO GROW Transactions are paused while TLOG file is growing Truncation should prevent growth depending on initial size Autogrowth settings
  • 27. DATA FILE ABOUT TO GROW
  • 28. TLOG ACTIVITY Re-org (~1 Gb/minute) Re-build (~200Kb/minute)
  • 29. TLOG BACKUP DURATION Durations shown in the query dont include the WithVerify time I usually use a 3X multiplier for estimating actual tlog backup time Look at start time of next backup
  • 31. WARNING SIGNS VLDB data file about to grow TLOG file about to grow TLOG backup duration + Verify taking longer than backup schedule schedule Log file not truncating (excessive growth) Wait times on sp_WhoIsActive File sizes growing larger than available disk space
  • 32. DEAN WILLSON Director of Application Development, Aunt Millies Bakeries Long time member of NUFW Former President of fwPASS Twitter: @deanwillson Blogs: dean-o.blogspot.com, practicalhoshin.blogspot.com LinkedIn: http://linkedin.com/in/deanwillson