1 / 60

Database Maintenance in Microsoft SQL Server 2005

DAT307. Database Maintenance in Microsoft SQL Server 2005 . Don Vilen Program Manager SQL Server Microsoft C o rporation – Redmond . Who Am I?. Program Manager on SQL Server’s Storage Engine Working on SQL Server at Microsoft for 12+ years

dunn
Download Presentation

Database Maintenance in Microsoft SQL Server 2005

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. DAT307 Database Maintenance in Microsoft SQL Server 2005 Don Vilen Program Manager SQL Server Microsoft Corporation – Redmond

  2. Who Am I? • Program Manager on SQL Server’s Storage Engine • Working on SQL Server at Microsoft for 12+ years • Responsible for Database Mirroring and High Availability • Previously: • PM for Filestream, other features • Training for PSS Engineers on SQL Server around the world • DonV@microsoft.com

  3. Session Objectives And Agenda Hardware with Maintenance in Mind Integrity Checks Database Files Optimizations Backups Scheduling Miscellaneous Tasks

  4. Maintenance – What, Why, When How • What • Which maintenance operations are needed to ensure a reliable, available, performant system? • Why • Understand each maintenance task and why it is needed • Backing up for the sake of backing up • How many of you backup tempdb? • When • Do you have a maintenance window? • Impact of maintenance on workload • Know which operations just add more work for you later • How… and Where

  5. Maintenance Plans • Home-grown or Built-in? • Only you can determine that • Maint Plans are less flexible and can be limiting • Home-grown may require more work up front • Either way you MUST understand what is actually happening

  6. Agenda Hardware with Maintenance in Mind Integrity Checks Database Files Optimizations Backups Scheduling Miscellaneous Tasks

  7. Hardware and Maintenance • Why talk about Hardware? • Maintenance is resource intensive • Rebuilding and reorganizing indexes • What is the primary cause of corruption in SQL Server databases? • Hardware ! • Use SQLIOSim to test out your IO system • Use database page checksums and backup checksums to detect hardware issues

  8. Hardware Configuration • Memory • Too much is better than not enough • CPUs • 2 processors are better than 1 • 4 processors are better than 2 • Beware of Hyper Threaded processors • MAXDOP – Max Degree of Parallelism • Server-level setting • Statement-level setting • Drive Arrays • Know your disk configuration

  9. RAID Uses • RAID 1 – Mirrored • O/S, Logs, Tempdb • RAID 5 – Striped & Parity • Data, Tempdb • Not as preferred as it used to be due to performance • RAID 0+1 or 1+0 - Striped & Mirrored • Data, Tempdb, Logs • RAID 0 – Striped • Temporary or Intermediate Backups

  10. SAN Considerations • Are you in full control of your file and data placement on your SAN? • You likely want to separate data and log for both performance and reliability • But that might not be your Storage Admin’s priority • The virtualization on the SAN can often lead to unmet expectations • Magnified by maintenance operations

  11. Typical SAN

  12. Agenda Hardware with Maintenance in Mind Integrity Checks Database Files Optimizations Backups Scheduling Miscellaneous Tasks

  13. Database Integrity • DBCCs check the following in a database: • SQL Server 2000 and SQL Server 2005 • Consistency between the system tables • Consistency of disk space allocation structures • Index and data pages are correctly linked • Indexes are in their proper sort order • Text Pointers are consistent • Page offsets are reasonable • The data on each page is reasonable • Low-level checks on critical system tables • New to SQL Server 2005 • Data Purity checks • Row Overflow Pointers are consistent • Checks Service Broker internal objects • Checks indexed view and XML index contents • Torn page and Checksum errors

  14. Example: What Gets Checked For a Leaf Index Page? • Page and record audits • B-tree checks • Page linkages and key ranges • Partitioning • Key ordering between records • Per-record checks • Complex columns • Computed columns • Data purity • 1-to-1 mapping with base table row • Page / row counts

  15. DBCC CHECKDB • SQL Server 2000 • CHECKALLOC • CHECKTABLE • Does NOT include CHECKCATALOG • SQL Server 2005 • CHECKALLOC • CHECKTABLE • CHECKCATALOG • Now allows you to specify a database ID! • No need to run CHECKALLOC, CHECKCATALOG or CHECKTABLE if running DBCC CHECKDB in SQL Server 2005

  16. DBCC CHECKDB Locking • SQL Server 2000 • Uses table-level Schema locks by default • Reads log entries and can block log truncation • SQL Server 2005 • Uses an internal database snapshot to get transactionally consistent view of the database • Replaces Log reading behavior found in SQL Server 2000

  17. Databases to Check • Master • SQL Server 2005 also checks the resource database • Model • MSDB • Tempdb? • SQL Server 2005 does not run CheckAlloc or CheckCatalog and uses Shared table locks, since it cannot use snapshots • User databases

  18. When and Where to Run DBCC? • Run DBCC CHECKDB as often as needed for you to feel good about your data • If there is a problem you’d like to know as soon as possible • Checksums! • Use PHYSICAL_ONLY option for faster or intermediate checks • Use TABLOCK option for fasterresults but may block • Run against a recently restored backup instead of the live server • Use NO_INFOMSGS option

  19. Agenda Hardware with Maintenance in Mind Integrity Checks Database Files Optimizations Backups Scheduling Miscellaneous Tasks

  20. VB Word .mdf .mdf .ldf File Doc VB Word .mdf .ldf File Doc File-Level Fragmentation

  21. Minimizing File-level Fragmentation • Create your DB files large enough to suit your needs in the future • No penalty having files too large, but big penalty if files are too small • Don’t let AutoGrow kick in. Be proactive instead. • Always grows at busiest time of the day  • Avoid auto-growing during maintenance window • Do enable AutoGrow, but don’t rely on it • Keep other apps off your DB Server • Use an OS level file defragmenter if needed • Can SQL Server be running?

  22. Shrinking the Database • When should you shrink the Database? • Almost never… • Why not? • Very costly and will fragment your data & indexes • Fully Logged Operation!

  23. Shrinking (cont’d.) • If you have to shrink, what are your options? • DBCC SHRINKDATABASE • Shrinks the Database and Log combined • DBCC SHRINKFILE • Used on each data or log file independently • Allows more control • Online and stoppable (doesn’t cause corruption) • But .. You should probably remove Shrinking from Maintenance Plans

  24. Agenda Hardware with Maintenance in Mind Integrity Checks Database Files Optimizations Backups Scheduling Miscellaneous Tasks

  25. OptimizationsFragmentation Overview • There are essentially two aspects of Optimization • Logical Fragmentation • When the Physical order does not match the Logical order • Rows or pages become non-contiguous • Caused mainly by Page-Splits • Affects range scans, therefore more DW than OLTP • Page Fullness (Fill Factor) • When the amount of free space on a page is more or less than ideal • Caused by Inserts / Updates and Deletes • Causes more IOs than necessary to read given amount of data

  26. Page Header Page Header Azinger Duval Free Space Jones Kelly Lehman OMeara Pavin Free Space Rose Woods Lehman Extent Extent Extent Extent Extent ... ... ... 1 2 3 2000 2001 Page Splits Page Header Azinger Duval Jones Kelly OMeara Pavin Rose Woods

  27. How Logical Fragmentation Occurs Index leaf level of newly-built index Green arrow is the physical order White arrows are following the logical order

  28. How Logical Fragmentation Occurs (cont’d) Newly-built index leaf after a single page split Green arrow is the physical order White arrows are following the logical order

  29. How Logical Fragmentation Occurs (cont’d) Index leaf level after random inserts/deletes Green arrow is the physical order White arrows are following the logical order

  30. OptimizationsRe-indexing • Why rebuild indexes? • Primarily to remove fragmentation and improve performance • Not to recover from corruption as many may think

  31. OptimizationsRe-indexing • SQL Server 2000 • DBCC DBREINDEX • DBCC INDEXDEFRAG • DBCC SHOWCONTIG • SQL Server 2005 • ALTER INDEX REBUILD • ALTER INDEX REORGANIZE • sys.dm_db_index_physical_stats

  32. ALTER INDEX REBUILD • Pros • Rebuilds one or all indexes on a table • Deals internally with existing dependent indexes • Adheres to Fill Factors & Pad Indexes • Updates the statistics • Best chance of contiguous indexes when done • Can use parallelism (Enterprise Edition only) • But be aware of impact on other work • SQL Server 2005 can be an online operation

  33. ALTER INDEX REBUILD • Cons • Locks the entire table and indexes for the duration if OffLine Option is used • One big transaction. Cannot clear the log until done. • Can take a while and can eat up a lot of resources • Requires ~1.2x the size of the index in free space

  34. ALTER INDEX REORGANIZE • Pros • Takes minimal locks, essentially online operation • Will fill pages up to the fill factor during the compacting phase • Can be stopped and restarted • The log can be backed up during the operation • Does not require extra free space in the datafiles or tempdb

  35. ALTER INDEX REORGANIZE • Cons • Possible to log several times the size of the index • Does not update the statistics • Single threaded • Rebuilds only the leaf level • Works on a single file at a time • Does not remove interleaving problems

  36. How and When to Run? • How and when you reindex depends on: • Your maintenance window • Size of tables • Number of tables and indexes • Hardware • Online vs. Offline • Data usage (OLTP vs. DW) • How fast they get fragmented

  37. How and When to Run (cont’d.) • Which ones to reindex? • System Tables? • Not supported on System Tables • User Tables? • Absolutely • Do not blindly reindex all indexes • Determine based on the fragmentation level • Books Online examples: • SQL Server 2000: DBCC SHOWCONTIG • SQL Server 2005: sys.dm_db_index_physical_stats • Or some other predetermined schedule • Perhaps REORGANIZE for less than a certain percentage of fragmentation, REBUILD for higher

  38. Updating Statistics • Not usually needed, especially if Auto-Update statistics is turned on • Depends on your data changes • Choices are: • sp_updatestats • All tables and indexes • UPDATE STATISTICS • Table or index at a time

  39. Agenda Hardware with Maintenance in Mind Integrity Checks Database Files Optimizations Backups Scheduling Miscellaneous Tasks

  40. Backups • Full • Differential • Log • File and Filegroup • Which one to use? • Of course, that depends • Keep recovery time in mind as well

  41. Backup Guidelines – System DBs • Master / Model / MSDB • Backing up Master also backs up the Resource DB • FULL backup at least once a night • Usually SIMPLE mode, no LOG backups available • So can only recover to last Full or Differential • Master can not do log backups

  42. Backup Guidelines – User DBs • FULL backup once a night if you can • Larger ones may be less frequent • File or File Group in between • DIFF backups can save recovery time • LOG backups depending on how much data you can afford to lose

  43. What About Verify? • RESTORE VERIFYONLY checks to see that backup set is complete and volumes are readable • SQL Server 2005 does more to verify the structure of the data in the backup • Backups are never any good unless you test it • SQL Server 2005 helps with CHECKSUM option

  44. Backup Location • Disk or tape? • I prefer to disk first when ever possible • Usually faster and cleaner • Latest backups are always handy for restores • Backup to tape from disk for long-termand off-site storage • Never backup to the same disk array that holds the data or log files • Many, many people do this ! • If backing up to a remote share, use UNC pathing, not mapped drives • Due to permissions / security

  45. Third-Party Tools • If you want to backup SQL Server to a remote tape device you may want a third-party tool • Consider compression for backups • Faster backup and restore times • Less storage required • Less I/O and network traffic • SQL Server 2008 will have compression built in • Encrypted Backups

  46. Backup Device Cleanup • Simple backups that reuse Devices with INIT require no cleanup • Backups to individual Devices will need to be periodically deleted • Maintenance Plan does this for you • Custom jobs require user code

  47. Backup History • Have you ever noticed that MSDB just keeps getting larger and larger? • Every backup adds history records to MSDB • SQL Server 2000 requires a job to clean up backup history • SQL Server 2005 manages this for you as a Maintenance Plan step .. If you add it

  48. sp_delete_backuphistory DECLARE @DT DATETIME SET @DT = DATEADD(dd, -7, GETDATE()) EXEC MSDB.dbo.sp_delete_backuphistory @DT

More Related