1 / 21

SQL 710

Week 7 : Chapter 7 Agenda. Maintenance Plan: Why do maintenance? Overview Maintenance Plan wizard DBCC maintenance commands. SQL 710. Why do Maintenance?. Routinely want to make a backup copy of database in case of hardware problems or data corruption

elgin
Download Presentation

SQL 710

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. Week 7 : Chapter 7 Agenda • Maintenance Plan: • Why do maintenance? • Overview • Maintenance Plan wizard • DBCC maintenance commands SQL 710

  2. 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)! SQL 710

  3. 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 SQL 710

  4. 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 SQL 710

  5. 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 SQL 710

  6. 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 SQL 710

  7. 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 SQL 710

  8. 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 SQL 710

  9. 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. SQL 710

  10. 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) SQL 710

  11. 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 SQL 710

  12. 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!) SQL 710

  13. 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 SQL 710

  14. 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 SQL 710

  15. 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 SQL 710

  16. 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 SQL 710

  17. 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 SQL 710

  18. Physical structure of database Insert Order 1 orderlines on Page 1: SQL 710

  19. Physical structure of database (ctd) Insert Order 2’s orderlines on Page 1(blue) and page 2 (white) SQL 710

  20. 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 SQL 710

  21. Physical structure of database (ctd) Order 2 cancelled- delete Order 2’s orderlines – leaves Order 1’s orderlines fragmented SQL 710

More Related