Backup, Integrity Check and Index and Statistics Maintenance - PowerPoint Presentation

Slide1

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 errors

http://

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

Questions?