1 / 0

Database Optimization & Maintenance

Database Optimization & Maintenance. Tim Richard. Agenda. SQL Configuration OnBase DB Planning Backups Integrity Optimization Tips and Recommendations. SQL Server: Databases. Five system databases on a SQL Server instance master model msdb TempDB Resource database.

varick
Download Presentation

Database Optimization & 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. Database Optimization & Maintenance Tim Richard
  2. Agenda SQL Configuration OnBase DB Planning Backups Integrity Optimization Tips and Recommendations
  3. SQL Server: Databases Five system databases on a SQL Server instance master model msdb TempDB Resource database
  4. System Database Recommendations Pre-size TempDB and the transaction log Create multiple files for TempDB Resist shrinking the database
  5. SQL Server Configuration Parallelism Memory TempDB Create multiple files ODBC drivers Verify client version vs. server Verify most recent version
  6. Agenda SQL Configuration OnBase DB Planning Backups Integrity Optimization Tips and Recommendations
  7. The OnBase DB What information does it store? Critical component in an OnBase solution Data Performance
  8. Before you create the database… Where should the files be located? This is a business decision If you’re not sure where to put them… Refer to the Database Installation Guide on Community Where should the files ideally be located? On drives that can provide the necessary response time (1-5 ms log writes, > 10 ms data writes) RAID 1+0, 15K
  9. Sizing Sizing guide from DRG Check existing utilization Pay attention to size on disk vs. % utilized
  10. Estimating Database Size
  11. Hyland Customer DB Size Small < 20 GB < 10,000,000 documents Medium/Large < 100 GB < 100,000,000 documents Enterprise > 100 GB > 100,000,000 documents
  12. Auto-File Growth Settings What should File Growth be for each DBSpace? 5 for 3*, 4, 5, 3i* 100 for 7, 8 512 for 1, 2, 6, 9, 10, 2i, 6i, 9i
  13. Agenda SQL Configuration OnBase DB Planning Backups Integrity Optimization Tips and Recommendations
  14. Backups: what you need to know first Evaluate your current backup process How often are backups taken (full, diff, log)? Where are copies stored on site? Where are copies stored off site? How often are tapes/disks/etc. rotated?
  15. Backups: what you need to know next Determine your recovery strategy RPO = Recovery Point Objective RTO = Recovery Time Objective Do you need High Availability? Establish a SLA Test, test, test, and then test again
  16. Backup Tasks When was last backup? When was last transaction log backup? Creating a backup Full vs. differential vs. log Set up a maintenance plan Restore the backup
  17. Agenda SQL Configuration OnBase DB Planning Backups Integrity Optimization Tips and Recommendations
  18. Integrity checks: what you need to do Create a job to check integrity on a regular basis Create alerts to monitor for errors: 823: Page not read 824: Torn page 825: Impending trouble Regularly check hardware for problems
  19. Integrity Tasks Run CHECKDB Page verify option Set up a maintenance plan Set up alerts
  20. Agenda SQL Configuration OnBase DB Planning Backups Integrity Optimization Tips and Recommendations
  21. Optimizations: what you need to do Rebuild indexes on a regular basis Update statistics with 100% sample Update index statistics AND column level statistics
  22. Updating Statistics As part of Index Rebuilds Only updates index statistics 100% sample Automatically When 20% of the rows + 500 change Not 100% sample Enables query optimization in 2005 & 2008* Manually (UPDATE STATISTICS or sp_updatestats)
  23. Optimization Tasks - Fragmentation Rebuild >40% fragmentation Offline Reorganize >10% and <40% fragmentation Online Typically fragmented tables Itemdata10 Itemlc Keyword tables
  24. Optimization Best Practices Rebuild indexes regularly Use 100% sample for statistics Differences in SQL 2000 vs. 2005/2008 Revisit maintenance plans
  25. Agenda SQL Configuration OnBase DB Planning Backups Integrity Optimization Tips and Recommendations
  26. User’s Role in Performance Database is a shared resource Each activity consumes part of the resource Best Practice for User Searches Retrieve < 100 documents Selective search criteria Use Keywords and Dates Avoid Leading Wildcards
  27. Suggestions Keyword creation Smart keywords, use Keyword Type Groups if possible Educate users on negative effects of actions Be cautions with API programs/VB Scripts (performance) Utilize Custom Queries Disable Document List Refresh Enable Document Type Query Restrictions
  28. More Suggestions Setup required keyword for retrieval Enable option to select user range of batches in Committed queues Security Keywords EQUAL vs. NOT EQUAL Perform Security Keyword Checking During Database Query Other Database Settings (Utils) Tuning Parameters (Client and ini file)
  29. Hyland Communities
  30. Thank You!
More Related