Top 10 OPERATIONAL Mistakes TO AVOID
Helping keep your Sanity Intact
Mike Hillwig
� AKA The Cranky DBA
� SQL Server DBA
� Working with SQL Server since SQL 7
� Specializes in Operational Issues and Scalability
� Contract DBA at hosting division of a financial software company
� Resume includes Acme Packet, Shawmut Design and Construction, Equitable Resources
Mike Hillwig
� crankydba.com
� sqlserverpedia.com
� twitter.com/mikehillwig
Avoiding Messes
Before We Begin A little common sense advice…
Beware of Advice from Random Blogs
� Anybody can put bad advice on the internet
� Trust People You Know
� I Don’t Trust Anyone Who Says “ALWAYS” or “NEVER”
� If it comes from Paul or Kimberly, it’s true.
Top Ten Rules
#10: Not Planning to Scale or Grow
� “It’s just temporary”
� “We only need it for proof of concept.”
� “Can’t we use the existing server?”
� “We’re just going to demo this product.”
“Oh, by the way…”
� “Our sales volume just doubled.”
� “Didn’t we tell you?”
� “We just bought a competitor.”
� “We’re now running the business with that product.”
� “You’re backing that up, right?”
#9: 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
� Not taking advantage of free monitoring
#7: Not Allowing Instant File Initialization
� Prevents rapid growth and creation of database (or any) files
#6: Using your Server as a Workstation
� Avoid Remote Desktop
� Never use SSMS directly from your server
� Instead: Run from a workstation
� RUNAS is your friend
#5: Improper Use of Traces
� Avoid in Production
� Creates more work for your server
� Never store on your Database Server
� Instead: Run from a workstation and store on another machine
#4: Allowing Data Files to Grow in 1 MB or 10 Percent Increments
� Defy the Defaults!
� Causes disk fragmentation
� Every growth will ALTER the database
� Disk is cheap!
� Causes high number of VLFs in transaction log files
� Instead: Grow in larger increments
#3: 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
� Don’t forget to test recovering from long-term storage
#2: 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
� Very important in virtual environments
#1: Enabling Auto Shrink
� You can’t control when it runs
� What you shrink will just grow again
� Fragments your indexes
� Instead: Use DBCC Shrinkfile
Bonus Round
Not Configuring Model Database
� This is a template for newly created databases
� Helps enforce standards
Keeping System Databases on OS Drives
� Major performance killer
� Avoid putting TempDB and the Windows Pagefile on the same spindles/LUNs (Notice I didn’t say “never”)
� Avoid using default file paths in the SQL Installer
Not managing virtual log files inside transaction log files
Schema Changes at Peak Times
� ALTERing TABLEs causes locks on the entire table
� High Risk
� Difficult to roll back at peak times
� Instead: Use a Maintenance Window
For More…
� crankydba.com/go/topten
� twitter.com/mikehillwig
Top Related