SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

Optimize SQL Server Performance and Backup Strategies: Best Practices Guide

neve-finley avatar
neve-finley
38 diapositives · 531 Views · Nov 13, 2014

This comprehensive guide by Richard Kokoski focuses on optimizing SQL Server 2000 and 2005 databases through reindexing, updating statistics, and database shrinking. Learn to create efficient backup routines with email and pager notifications for SQL administrators, ensuring effective disaster recovery. Gain insights into full, differential, and transaction log backups and the importance of regular database integrity checks. Perfect for SQL and technical administrators seeking best practices for maintaining high-performance databases and preventing data loss.

Optimize SQL Server Performance and Backup Strategies: Best Practices Guide - slide 1
01 / 38Optimize SQL Server Performance and Backup Strategies: Best Practices Guide
1 / 17

Télécharger la présentation

Optimize SQL Server Performance and Backup Strategies: Best Practices Guide
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

  1. SQL Server Best Practices Keep Your Database In Top Performance Shape and Maintain Effective Backups Richard Kokoski September, 2007

  2. SQL Server Best Practices Abstract Learn to keep your SQL 2000 and/or SQL 2005 database optimized with Reindexing. You’ll learn how do Reindexing, update statistics (for optimized queries) and “shrink” the database to eliminate unused or deleted data. Also learn how to create scheduled backup routines that include notifications (e-mail and paging) so SQL administrators know that their database is properly backed up. If you’re a SQL administrator (stakeholder) or a technical administrator (network and database), you’ll gain great insight and learn best practices necessary for effective disaster prevention and recovery.

  3. SQL Server Best Practices Richard Kokoski Fox D Tech LLC rkokoski@foxdtechllc.com • Microsoft Certified System Engineer (MCSE) • Microsoft Certified Solutions Developer (MCSD) • Microsoft SQL Server Product Specialist • B.S./M.B.A • 19 Years Technology Experience • 13 Years SQL Server Experience • 6 Years Starbuilder Experience/Customized Solutions

  4. SQL Server Best Practices Topics of Discussion • Backup Database • Backup Types • Scheduling • Notifications • Optimize Database • Database Integrity Checks • Rebuild/Reorganize Indexes • Update Statistics • “Shrinking”

  5. SQL Server Best Practices Backup Database – Backup Types • Full Backup • Complete Backup of Database at Point in Time • Definitive Restore Point • Easy Copy of Database to Separate Server • Most Common for Smaller Size Databases • Lost Data Minimized to Last Full Backup

  6. SQL Server Best Practices Backup Database- Backup Types • Differential Backup • Backup of Database Data Since Last Full Backup • Full Backup + Differential Backup Restores to Point in Time • Faster & Smaller Backup than Full Backup • Most Common for Larger Size Databases • Lost Data Minimized to Last Full + Differential Backup

  7. SQL Server Best Practices Backup Database- Backup Types • Transaction Log Backup • Backup of Database Data Since Last Transaction Log Backup • Full Backup + Transaction Log “Rolling Forward” Restore to Point in Time • Faster & Smaller Backup than Full Backup and/or Differential Backup • Most Common for Databases Needing “Real Time” Backups • Lost Data Minimized to Last Full + Series of Transaction Log Backups

  8. SQL Server Best Practices Backup Database- Scheduling • Full & Differential Backups • Usually Scheduled Every Night • SQL Backup Files Part of Overall Network Backup • Ability to “Ad-Hoc” vs. Schedule • Definitive Restore Point • May Loose Some Data Because of Scheduling

  9. SQL Server Best Practices Backup Database- Scheduling • Transaction Log Backups • Usually Scheduled More Often then Every Night • Made for “Real Time” • Definitive Restore Point • Best Used to Reduce Lost Data Because of More Frequent Scheduling

  10. SQL Server Best Practices Backup Database- Notifications • Operators • Usually Network and/or SQL Server Administrators • Multiple Operators May be Defined • Notification Types • Email • “Net Send” • Pager • What to Send • Text Messages • Customized Notes & Logs

  11. SQL Server Best Practices Optimize Database • Database Integrity Checks • Overall “Health” of your database • Consistency Checks of Data and Indexes • Structurally and Physically Sound • Identify Possibly Corruption Issues • Advance Warning in Order to Fix • Attempt to Correct “Minor” Issues (Single User Mode)

  12. SQL Server Best Practices Optimize Database • Rebuild/Reorganize Indexes • What are Indexes? • Descriptors to Data in Tables & Views • How Are/Who Uses Indexes • Querying Database • Stored Procedures/Query Plans • Why Rebuild Indexes • Corruption Issues • Speed Enhancements • Adjust “Free Space per Page” to Optimize Data Inserts

  13. SQL Server Best Practices Optimize Database • Update Statistics • What are Statistics? • Information & Distribution of Data for Table/View Indexes • How Are/Who Uses Statistics? • Query Planning/SQL Server Query Optimizer • Why Update Statistics? • Auto Update vs. Manual Update • SPEED - Overall Benefit of Faster Querying for the Database • Let’s SQL Optimizer Make Better Judgments About Data Access

  14. SQL Server Best Practices Optimize Database • “Shrinking” • What is “Shrinking”? • Free Wasted Disk Space in Database of “Inactive” Data • Reduce Disk Space for SQL Log Files As well (After Truncate) • Why “Shrink” The Database? • Improved Database Performance • Eliminate Unnecessary Disk Space/Backup Space • Improve Backup Times

  15. SQL Server Best Practices Demonstration

  16. SQL Server Best Practices Session Review • Backup Database • Backup Types • Scheduling • Notifications • Optimize Database • Database Integrity Checks • Rebuild/Reorganize Indexes • Update Statistics • “Shrinking”

  17. SQL Server Best Practices Questions?

  18. SQL Server 2000 SQL Server 2000 Enterprise Manager

  19. SQL Server Best Practices

  20. SQL Server Best Practices

  21. SQL Server Best Practices

  22. SQL Server Best Practices

  23. SQL Server Best Practices

  24. SQL Server Best Practices

  25. SQL Server Best Practices

  26. SQL Server Best Practices

  27. SQL Server Best Practices

  28. SQL Server 2005 SQL Server 2005 Management Studio Express Edition Download MSXML 6.0 (http://www.microsoft.com/downloads/details.aspx?familyid=993c0bcf-3bcf-4009-be21-27e85e1857b1&displaylang=en) Download Microsoft SQL 2005 Management Studio Express Edition (http://go.microsoft.com/fwlink/?linkid=65110)

  29. SQL Server Best Practices

  30. SQL Server Best Practices

  31. SQL Server Best Practices

  32. SQL Server Best Practices

  33. SQL Server Best Practices

  34. SQL Server Best Practices

  35. SQL Server Best Practices

  36. SQL Server Best Practices

  37. SQL Server Best Practices

  38. SQL Server Best Practices