Efficient SQL Database Maintenance for Performance

Learn to effectively clean and optimize your SQL database for optimal performance. Discover tools like Ola Hallengren's Maintenance Solution and Brent Ozar's First Responder Kit, along with best practices for backup, restore, index optimization, and database integrity checks. Ensure a healthy database environment for improved efficiency.

• SQL maintenance
• Performance optimization
• Index management
• Database integrity
• Backup strategies

boc_spi Follow

Uploaded on Sep 07, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. Keep your SQL database clean and performant Part 2 Michael Tobisch dnnWerk.at June 26th, 2023

2. Intro Maintenance Backup/Restore Please support our valuable sponsors June 26th, 2023 Michael Tobisch, dnnWerk.at 2

3. Intro Maintenance Backup/Restore Agenda Implementing Ola Hallengren's Maintenance Solution and Brent Ozar's First Responder Kit on SQL Server Express Maintenance Index optimization Database integrity checks Health assessment Backup/Restore Backup strategies Restore Models Automated restore testing June 26th, 2023 Michael Tobisch, dnnWerk.at 3

4. Intro Maintenance Backup/Restore Maintenance Solution Award-winning SQL Server Backup, Integrity Check, and Index and Statistics Maintenance by Ola Hallengren SQL Server Agent is not available in SQL Server Express Download from https://ola.hallengren.com/downloads.html Install by running the Script MaintenanceSolution.sql June 26th, 2023 Michael Tobisch, dnnWerk.at 4

5. Intro Maintenance Backup/Restore First Responder Kit A free collection of scripts, worksheets and checklists by Brent Ozar helpful for setting up and maintaining SQL Server Download from https://downloads.brentozar.com/FirstResponderKit.zip Install by running the Script Install-All-Scripts.sql June 26th, 2023 Michael Tobisch, dnnWerk.at 5

6. Intro Maintenance Backup/Restore Demo June 26th, 2023 Michael Tobisch, dnnWerk.at 6

7. Intro Maintenance Backup/Restore Database Integrity Check Checks the integrity of Databases Tables Indexes File groups Allocation structures Catalog consitency etc. Detailled description of the parameters: https://ola.hallengren.com/sql- server-integrity-check.html Run this once a week June 26th, 2023 Michael Tobisch, dnnWerk.at 7

8. Intro Maintenance Backup/Restore Index Optimization Rebuilds and reorganizes indexes and statistics Detailled description of the parameters: https://ola.hallengren.com/sql-server-index-and-statistics- maintenance.html Run this daily on all user databases June 26th, 2023 Michael Tobisch, dnnWerk.at 8

9. Intro Maintenance Backup/Restore sp_Blitz Health Check Assessment that flags common issues and gives a link to a web page with more in-depth advice Quick start: EXEC sp_Blitz @CheckUserDatabaseObjects = 0; Detailled description: https://github.com/BrentOzarULTD/SQL-Server- First-Responder-Kit/tree/main#sp_blitz-overall-health-check June 26th, 2023 Michael Tobisch, dnnWerk.at 9

10. Intro Maintenance Backup/Restore Demo June 26th, 2023 Michael Tobisch, dnnWerk.at 10

11. Intro Maintenance Backup/Restore Backup and Restore Important: Backup strategy How much data loss is acceptable? What is the maximum time a recovery can take? Restore Model: Full or Simple? Backup types: Full Differential Transaction Logs Backup intervals June 26th, 2023 Michael Tobisch, dnnWerk.at 11

12. Intro Maintenance Backup/Restore Examples Normal website with not too much change of content, users Recovery Model: SIMPLE Daily FULL Backup or Weekly FULL Backup and daily DIFF backup Keep backup files for 1 week E-Commerce site Recovery Model: FULL Weekly FULL Backup 1-2 DIFF Backups/day LOG backups every 5-10 minutes (even 1 minute!) Keep backup files for 2 3 weeks June 26th, 2023 Michael Tobisch, dnnWerk.at 12

13. Intro Maintenance Backup/Restore Demo June 26th, 2023 Michael Tobisch, dnnWerk.at 13

14. Intro Maintenance Backup/Restore Automated Database Restore Tests Using Brent Ozar s sp_DatabaseRestore @RestoreDatabaseName = Something different! @TestRestore = 1 Drops the database after the Restore @RunCheckDB = 1 Performs a Database Integrity Check June 26th, 2023 Michael Tobisch, dnnWerk.at 14

15. Intro Maintenance Backup/Restore Demo June 26th, 2023 Michael Tobisch, dnnWerk.at 15

16. Intro Maintenance Backup/Restore Questions? June 26th, 2023 Michael Tobisch, dnnWerk.at 16

17. Intro Maintenance Backup/Restore Thank you! June 26th, 2023 Michael Tobisch, dnnWerk.at 17