Top Ten Operational Mistakes to Avoid

Posted by in SQLServerPedia Syndication on May 10th, 2011 | 0 comments

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.

Meme Monday: I got 99 problems but a disk ain’t one

Posted by in SQLServerPedia Syndication on May 2nd, 2011 | 0 comments

Tom LaRock posted an interesting meme for today.  Here are my nine problems that aren’t disk.

  1. Vendors who make bad recommendations to my user community for “optimizing” SQL server, such as using autoshrink
  2. Other system administrators who insist on using SSMS from the database server
  3. An application that keeps changing the recovery model every time we upgrade
  4. A business application owner who doesn’t understand why I hate to make schema changes in the middle of the day
  5. Applications that create databases in locations other than the default paths I’ve specified in my server’s configuration
  6. A security system that will only run as SA
  7. An application vulnerable to SQL injection attacks that the business won’t spend money to replace/fix/secure
  8. An impatient application that sends an alert if it hasn’t been backed up in exactly 24 hours.
  9. A vendor who is taking their time in certifying and supporting SQL 2008 R2