Problem

Apply best practices for new databases

Solution

Regular Schedule: Every 60 minutes

Solution: Native T-SQL

  1. Run CheckDB before schedule
  2. Run Full backup before schedule
  3. Change database recovery model to full
  4. Change database file growth percent to fixed
  5. Change database file growth size
  6. Enable database QueryStore (QS)
  7. Change database ownership
  8. Change database target recovery time
  9. Change database page verify option
  10. Change database compatibility level
  11. Change database index fill factor
  12. Enable database last query plan stats
  13. Enable database autotuning
  14. Change database service broker
  15. Change database auto update stats async on
  16. Collect Database Data/Page Compression Estimate
  17. Apply database data/page compression
  18. Enable database read committed snapshot isolation (RCSI)
  19. Change database file max size
  20. Enable Accelerated Database Recovery (ADR)
  21. Fix orphan users
  22. Change database user from login
  23. Apply database XML compression
  24. Check database files on the same drive
  25. Enable database Intelligent Query Processing (aka “IQP”)
  26. Disable Database Auto Shrink
  27. Disable Database Auto Close
  28. Enable Database ANSI_NULLs
  29. Enable Database ANSI_PADDING