week 7 : chapter 7 agenda maintenance plan: why do maintenance? overview maintenance plan wizard...
Post on 05-Jan-2016
228 Views
Preview:
TRANSCRIPT
Week 7 : Chapter 7 Agenda
Maintenance Plan:
• Why do maintenance?
• Overview
• Maintenance Plan wizard
• DBCC maintenance commands
Why do Maintenance?
• Routinely want to make a backup copy of database in case of hardware problems or data corruption
• Data in a database becomes fragmented over time as data is added, modified and deleted
• Should verify integrity of data occasionally • Normally want to perform these tasks when
you choose – not as the result of a crisis (just like car maintenance; don’t want to be stuck on the side of a highway with no oil in engine)!
Maintenance Overview
• Day-to-day operation• Very important responsibility of a DBA• If you do maintenance nobody notices but if
you don’t do it everyone knows• 2 types of maintenance:
- to keep database running most important
- to improve performance• Database maintenance can be fully
automated – there is a wizard for almost every task
Database Maintenance Wizard
Primary tool for database maintenance Graphical tool Can create a scheduled maintenance
plan for each database that performs:• Backups (most important!)• Optimization• Integrity Check• Reporting
Maintenance Planning
• Normally use wizard to create a maintenance plan for each database
• Best to have one plan per database but can choose more than 1 database per plan
• Plans are scheduled separately as required by application
• Very important to create a separate maintenance plan for critical system databases: master, msdb
Maintenance Plan
• A Maintenance Plan consists of a group of jobs and schedules called a plan
• A plan can be edited after you create it• Very simple tool to use - no excuse not to
use it• Within a job you can perform:
• Backups• Optimization• Integrity Check• Reporting
Database Backups
Most important part of maintenance plan: permits recovery of data from backup copy
Backs up database files or logs to a tape or hard drive
Hard drive location can be broken into several directories for each database
Can automatically delete older backups after a certain time
Wizard records all activities and sends notification
Report can be sent to disk, a central server, a history table or to an operator
Optimization
• Improves database performance• Database gets fragmented when data is
inserted and deleted (same as files on a hard drive, see last slides for illustration)
• Optimization causes the following to be performed that can improve database performance:• Examine and minimize fragmentation of
data• Update database statistics• Remove excess free space from
database
Data Fragmentation
• Database get fragmented when data is inserted or deleted
• You can select how much free space for new data will be left when defragmenting is done
• If database is primarily for data entry (OLTP) then it is best to leave significant free space at the end of each page so that related data stays together
• If database is composed of data that is read more often than being written (OLAP) such as a report server then leave lower percentage of page space free.
Update database statistics
Statistics are samples of data used to assist SQL Server search engine in locating data
As data is added and deleted these statistics become outdated
Can keep statistics updated 2 ways:• Use wizard to schedule update of statistics
periodically (this may slow performance if statistics are out of date because data would not be retrieved efficiently)
• Set database option that automatically updates statistics (this slows performance because statistics are continually updated)
Remove excess free space
• This setting in the wizard permits shrinking the database at a specific recurring time.
• Automatic shrink of database can be set as an option
Database Integrity Check
• SQL Server is a very stable RDBMS
• However should check integrity of a database periodically
• Integrity checks validity of defined database constraints (NN, PK, FK, CK, UN)
• Integrity check can correct minor errors automatically
• May have to restore data from backup if major integrity error is found (very rare!)
T-SQL Maintenance Commands
• T-SQL maintenance commands are the Database Consistency Check commands (DBCC)• Command line utility• Maintenance wizard actually uses these commands• Perform maintenance on Database Index and file groups• Commands include:
• DBCC CHECKDB• DBCC CHECKTABLE• DBCC DBREINDEX
DBCC CHECKDB Command
• CHECKDB command examines an entire database for corruption (checks all tables and indexes in database)
• Command can be run in diagnostic mode
• To correct any problem it has to be run in single user mode
• Can use REPAIR_FAST or REPAIR_ALLOW_DATA_LOSS switch
CHECKDB Switches
REPAIR_FAST Switch:• Least amount of damage to database• Quickly fixes any inconsistency• Don’t lose any data
REPAIR_ALLOW_DATA_LOSS Switch:• Most harmful switch but could save overall
database• You can use this command on a database
that you plan to recover from tape backup
DBCC CHECKTABLE Command
• Checks for database corruption against a table.
• You can use this command while people are using the table except while performing repair
DBCC DBREINDEX Command
Command updates the indexes on a server
Indexes are used for faster data access
Indexes are stored like data pages and become fragmented over time
DBCC DBREINDEX command defragments indexes
Time required depends on:• Number of indexes• Size of indexes• Load on server when running command
Physical structure of database
Insert Order 1 orderlines on Page 1:
Order 1 Item 1 Quantity
Order 1 Item 2 Quantity
Physical structure of database (ctd)
Insert Order 2’s orderlines on Page 1(blue) and page 2 (white)
Order 1 Item 1 Client 1
Order 1 Item 2 Client 1
Order 2 Item 1 Client 2
Order 2 Item 2 Client 2
Order 2 Item 3 Client 2
Order 2 Item 4 Client 2
Order 2 Item 5 Client 2
Order 2 Item 6 Client 2
Order 2 Item 7 Client 2
Physical structure of database (ctd)
Add order 1 orderlines – no room on page 1 so place on page 2 – data for order 1 is fragmented
Order 1 Item 1 Quantity
Order 1 Item 2 Quantity
Order 2 Item 1 Quantity
Order 2 Item 2 Quantity
Order 2 Item 3 Quantity
Order 2 Item 4 Quantity
Order 2 Item 5 Quantity
Order 2 Item 6 Quantity
Order 2 Item 7 Quantity
Order 1 Item 3 Quantity
Order 1 Item 4 Quantity
Physical structure of database (ctd)
Order 2 cancelled- delete Order 2’s orderlines – leaves Order 1’s orderlines fragmented
Order 1 Item 1 Quantity
Order 1 Item 2 Quantity
Order 1 Item 3 Quantity
Order 1 Item 4 Quantity
top related