1 / 18

Backup, Integrity Check and Index and Statistics Maintenance

Backup, Integrity Check and Index and Statistics Maintenance. - Inside Ola Hallengren's Maintenance Solution. About Me. Ola Hallengren http://ola.hallengren.com E-mail: ola@hallengren.com DBA in Saxo Bank, a Danish investment bank SQL Server since 2000. An Overview of the Solution.

liam
Download Presentation

Backup, Integrity Check and Index and Statistics Maintenance

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Backup, Integrity Check and Index and Statistics Maintenance - Inside Ola Hallengren's Maintenance Solution

  2. About Me • Ola Hallengren • http://ola.hallengren.com • E-mail: ola@hallengren.com • DBA in Saxo Bank, a Danish investment bank • SQL Server since 2000

  3. An Overview of the Solution • Based on stored procedures and functions • Supports SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2 (and Denali when it is released) • DatabaseBackup - Backup • DatabaseIntegrityCheck – Integrity Check • IndexOptimize – Index and Statistics Maintenance • It’s free!

  4. Index Maintenance – Challenges • Indexes are getting fragmented over time, which has a negative impact on performance • Rebuilding and reorganizing indexes uses system resources and generates blocking • Large databases and limited maintenance windows

  5. Index Maintenance – Checking the Fragmentation • Checking the fragmentation using sys.dm_db_index_physical_stats • Only rebuild or reorganize indexes that are fragmented • Rebuild heavily fragmented indexes • Reorganize moderately fragmented indexes

  6. Index Maintenance - Rebuild and Reorganize Limitations • Online rebuild only in Enterprise Edition • Xml – indexes and spatial indexes cannot be rebuilt online • Indexes with LOB (Large Object) columns cannot be rebuilt online • An index partition cannot be rebuilt online (a partitioned index can be rebuilt online as a whole) • An index with page locking disabled cannot be reorganized

  7. IndexOptimize - Core • Checking the fragmentation • Three fragmentation groups; low, medium and high • Lets you define the fragmentation thresholds • Lets you define the index maintenance for each group • Secondary index maintenance in case your preferred index maintenance is not possible for an index • Demo

  8. IndexOptimize and Statistics • If an index is rebuilt, the statistics is automatically updated • IndexOptimize lets you update statistics on indexes that were not rebuilt • IndexOptimize also lets you update column statistics • Demo

  9. IndexOptimize – Selecting Indexes • Select a single schema, object or index • Select a list of schemas, objects or indexes • Exclude indexes • Select indexes using wild-cards • Common for very large databases to put large tables in their own jobs and exclude these objects from the main job (gives better control and parallelization) • Demo

  10. IndexOptimize - Advanced • Rebuild or reorganize indexes on a partition level • MAXDOP • SORT_IN_TEMPB • FILLFACTOR and PADINDEX • Time limit if you have a limited maintenance window • Demo

  11. DatabaseBackup - Core • Full, differential and transaction log backup • Verify the backup • Cleanup of old backup files, if the backup and verify was successful • Demo

  12. DatabaseBackup – Handling new databases • Common error messages after adding a new database:"BACKUP LOG cannot be performed … no current database backup.“"Cannot perform a differential backup … a current database backup does not exist.“ • DatabaseBackup is checking if a differential or transaction log backup can be done, before doing the backup • Supports changing the backup type if needed (e.g. from log to full or from differential to full) • New databases are getting backed up quickly after they have been added and no alerts are generated • Demo

  13. DatabaseBackup - Advanced • Backup to multiple files (backup striping) • Backup compression • Backup using Quest LiteSpeed, RedGate SQLBackup, RedGate HyperBac or Idera SQLSafe • CHECKSUM • COPY_ONLY • DESCRIPTION • Demo

  14. DatabaseIntegrityCheck • DBCC CHECKDB • PHYSICAL_ONLY and NOINDEX for very large databases • Demo

  15. Selecting Databases • Common parameter for DatabaseBackup, DatabaseIntegrityCheck and IndexOptimize • Select All Databases, All User Databases or All System Databases • Select a single database or a list of databases • Exclude databases • Select databases using wildcards • Demo

  16. Inside the Maintenance Solution • Shared stored procedure for executing commands – Consistent error handling and logging • TRY CATCH or @@ERROR checking? • Some commands (BACKUP DATABASE) return two errorshttp://blogs.msdn.com/b/sqlprogrammability/archive/2006/04/03/567550.aspx • RAISERROR WITH NOWAIT after each command to clear the output buffer and have instant logging • LOCK_TIMEOUT to prevent index commands from hanging because of blocking • SQL Server Agent CmdExec Job Steps, using sqlcmd and the –b option to continue executing the stored procedure after an error

  17. Links • Download script:http://ola.hallengren.com/scripts/MaintenanceSolution.sql • Documentation:http://ola.hallengren.com/Documentation.html • License:http://ola.hallengren.com/License.html

  18. Questions?

More Related