1 / 25

Module 16 Performing Ongoing Database Maintenance

Module 16 Performing Ongoing Database Maintenance. Module Overview. Ensuring Database Integrity Maintaining Indexes Automating Routine Database Maintenance. Lesson 1: Ensuring Database Integrity. Discussion: Ensuring Database Integrity Overview of DBCC CHECKDB DBCC CHECKDB Options

arawn
Download Presentation

Module 16 Performing Ongoing Database 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. Module 16 Performing Ongoing Database Maintenance

  2. Module Overview • Ensuring Database Integrity • Maintaining Indexes • Automating Routine Database Maintenance

  3. Lesson 1: Ensuring Database Integrity • Discussion: Ensuring Database Integrity • Overview of DBCC CHECKDB • DBCC CHECKDB Options • DBCC CHECKDB Repair Options • Demonstration 1A: DBCC CHECKDB

  4. Discussion: Ensuring Database Integrity • What is database integrity? • What techniques are you currently using to check and maintain database integrity?

  5. Overview of DBCC CHECKDB • Checks logical and physical integrity in the database • Allocation of all pages in the database • Consistency of tables and indexes • Consistency of the catalog of the database • Link level consistence for FILESTREAM objects • Service Broker objects • Offers repair options • Some options permit data loss • Runs online using an internal database snapshot • Should be run frequently • Synchronize executions with your backup strategy, to be able to recover corruption

  6. DBCC CHECKDB Options

  7. DBCC CHECKDB Repair Options • Database needs to be in SINGLE_USER mode • DBCC CHECKDB output shows minimum option for recovery • REPAIR_REBUILD for repairs that can be done without data loss • REPAIR_ALLOW_DATA_LOSS involves data loss • Consider restoring a database instead of allowing data loss

  8. Demonstration 1A: DBCC CHECKDB • In this demonstration, you will see how to use the different options for the DBCC CHECKDB command

  9. Lesson 2: Maintaining Indexes • How Indexes Affect Performance • Types of SQL Server Indexes • Index Fragmentation • FILLFACTOR and PADINDEX • Ongoing Maintenance of Indexes • Online Index Operations • Updating Statistics • Demonstration 2A: Index Maintenance

  10. How Indexes Affect Performance Table Scan SQL Server reads all table pages Index SQL Server uses index pages to find rows

  11. Types of SQL Server Indexes • Indexes are organised as B-Trees (Balanced Trees) • Clustered index has data pages in the leaf level • Nonclustered index has pointer to data rows in leaf level Root Index Page Intermediate Level Index Pages Leaf Nodes

  12. Index Fragmentation How does fragmentation occur? • SQL Server reorganizes index pages when data modification causes index pages to split Types of fragmentation: • Internal – pages are not full • External – pages are out of logical sequence Detecting fragmentation • SQL Server Management Studio – Index Properties • System function - sys.dm_db_index_physical_stats

  13. FILLFACTOR and PADINDEX • Free space can be left in indexes, including clustered indexes • FILLFACTOR • PADINDEX ALTERTABLEPerson.Person ADDCONSTRAINTPK_Person_BusinessEntityID PRIMARYKEYCLUSTERED ( BusinessEntityIDASC ) WITH (PAD_INDEX=OFF,FILLFACTOR= 70); GO

  14. Ongoing Maintenance of Indexes • REBUILD • Rebuilds the whole index • Needs free space in database • Performed as a single transaction with potential requirement for a large amount of transaction log space • REORGANIZE • Sorts the pages and is always online • Less transaction log usage • Can be interrupted but still retain work performed to that point ALTERINDEXCL_LogTimeONdbo.LogTime REBUILD; ALTERINDEXALLONdbo.LogTime REORGANIZE;

  15. Online Index Operations • Indexes can be created, rebuilt and dropped online • Allows concurrent user access to the underlying table and indexes • Only needs short term shared locks at begin and end of the operation and Schema locks during the operation • Typically slower than equivalent offline operation but allows user access ALTERINDEXIX_Contact_EmailAddress ON Person.Contact REBUILD WITH (PAD_INDEX=OFF, FILLFACTOR= 80, ONLINE=ON, MAXDOP= 4 );

  16. Updating Statistics • As data changes, distribution statistics become outdated • Statistics can be updated automatically or on demand • Automatic update set through database option and should be enabled

  17. Demonstration 2A: Index Maintenance • In this demonstration you will see: • How to view index fragmentation • How to reorganize indexes

  18. Lesson 3: Automating Routine Database Maintenance • Overview of SQL Server Database Maintenance Plans • Monitoring Database Maintenance Plans • Demonstration 3A: Configuring a Database Maintenance Plan

  19. Overview of SQL Server Database Maintenance Plans • Help database administrators to schedule core maintenance tasks • Create one or more SQL Server Agent jobs • Use SSIS to perform tasks • Can be created manually rather than via the wizard

  20. Monitoring Database Maintenance Plans • Real time monitoring through Job Activity Monitor • Execution results stored in msdb and can also be • Written to a text file • Sent to an Operator • Cleanup tasks are used to implement retention

  21. Demonstration 3A: Configuring a Database Maintenance Plan In this demonstration, you will see: How to create and execute a Maintenance Plan How to review the history for a Maintenance Plan

  22. Lab 16: Ongoing Database Maintenance • Exercise 1: Check database integrity using DBCC CHECKDB • Exercise 2: Correct index fragmentation • Exercise 3: Create a database maintenance plan • Challenge Exercise 4: Investigate table lock performance (Only if time permits) Logon information Estimated time: 45minutes

  23. Lab Scenario There has been a disk failure in the I/O subsystem. The disk has been replaced but you want to check the consistency of your existing databases. You will execute DBCC CHECKDB to verify the logical and physical integrity of all databases on the Proseware instance. You have identified fragmentation in a number of tables in the MarketDev database and you are sure that performance is decreasing as the amount of fragmentation increases. You will rebuild the indexes for any of the main database tables that are heavily fragmented. You have also identified a degradation of performance in the application when proper index maintenance has not been performed. You want to ensure that there is an early detection of any consistency issues in the MarketDev database and that the index maintenance is automatically executed on a scheduled basis. To make sure this regular maintenance occurs, you will create a Database Maintenance plan to schedule these operations on a weekly basis.

  24. Lab Review • If you need to perform additional options that are not provided in the database maintenance plan wizard, what options could you use? • If you need to execute a maintenance plan with timing that cannot be accommodated by a single schedule, what can you do?

  25. Module Review and Takeaways • Review Questions • Best Practices

More Related