180 likes | 509 Vues
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.
E N D
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 • 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!
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
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
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
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
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
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
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
DatabaseBackup - Core • Full, differential and transaction log backup • Verify the backup • Cleanup of old backup files, if the backup and verify was successful • Demo
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
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
DatabaseIntegrityCheck • DBCC CHECKDB • PHYSICAL_ONLY and NOINDEX for very large databases • Demo
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
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
Links • Download script:http://ola.hallengren.com/scripts/MaintenanceSolution.sql • Documentation:http://ola.hallengren.com/Documentation.html • License:http://ola.hallengren.com/License.html