Top Ten Operational Mistakes to Avoid

Leave a comment

May 10, 2011 by Mike Hillwig

I’m working on a presentation for SQL Saturday in South Florida. The more I look at this, the more I really like it.

  1. Not Setting Min and Max Memory
    • MIN: ”Use this much so nobody else can have it.”
    • MAX: “Save some for the Operating System” – Prevents Paging
  2. Schema Changes at Peak Times
    • ALTERing TABLEs causes locks on the entire table
    • High Risk
    • Difficult to roll back at peak times
  3. Using your Server as a Workstation
    • Avoid Remote Desktop
    • Never use SSMS directly from your server
  4. Improper Use of Traces
    • Avoid in Production
    • Never store on your Database Server
  5. Enabling Auto Shrink
    • You can’t control when it runs
    • What you shrink will just grow again
    • Fragments your indexes
  6. Allowing Data Files to Grow in 1 MB Increments
    • Causes disk fragmentation
    • Every growth will ALTER the database
  7. Not using a Domain Account for SQL Services
    • Allows you to interact with other servers. Very helpful with backups over the network.
  8. Not Configuring SQLMail Alerts
    • Allows you to know when something fails
    • Allows you to be active instead of reactive
  9. Not Testing Restores
    • The worst time to test a restore is when your production server fails
    • It’s good practice for when something does fail
  10. Not Configuring Instant File Initialization

There are a lot of good people out there who don’t realize they’re doing things to kill their system’s performance. Obviously this is a work in progress, but I refine it more and more all the time.