Building on our combined decades of involvement as senior SQL database administrators, we have put together a short guide book, that allows us to spread the shared wisdom that we have gained from our experience.  We are happy to open this for your use, and welcome feedback.

Our own top rules for DBAs

  • Backup before you make any changes.
  • A SNAPSHOT is not the same as a BACKUP. Backup can restore you to a specific point in time – important where even a second’s loss can a big issue (for example for financial transaction applications)
  • AlwaysOn & Mirror secondary databases can replace a production database only if they are in sync
  • A server restart while there are existing open transactions may lose many transactions. Setting Non-Commit will rollback all transactions first.
  • Restore of a database will rollback all open transaction. Remember to set log shipping standby
  • Don’t forget “Begin Tran” at the start of a procedure, so that you can rollback if it fails to COMMIT
  • When using SELECT {CASE … WHEN} AS [] FROM GROUP BY – calculate all options
  • When using SELECT {SUBQUERY} AS [] FROM GROUP BY – calculate all options
  • Function ON SELECT (acting on many rows) can cause CPU load problems
  • JOIN between different datatypes can cause a full table scan
  • When using TABLE parameter  – remember one row showing on statistics may be acting on multiple rows in execution
  • One click can operate multiple queries. Individual queries may run quickly, but the whole time to complete may be very large – like a single execution time of 160ms over 500 cycles will result in 80 seconds total.
  • Check the settings in SQL Server Agent Properties window so that jobs history is not being purged too soon. Use dbo.sysjobhistory to access jobs history
  • On setup of a new server, before running Index Maintenance, run checkDB.
  • Reorganize can open a pandoras box of unexpected results.
  • Keep in mind the difference between statistics & index
  • Log growth setting – never leave on percent
  • When query performance has decreased, it may mean several things:
    • The plan has been replaced
    • blocked by query
    • blocked by isolation level
    • resource bottleneck
    • max DOP change
    • data reader/network bottleneck
    • compatability level replace
  • Act like a good doctor; the patient always knows his own body best. Listen to the complaint, see the problem with your own eyes, pay attention to row counts.
  • Think about the design of any change and test/verify before starting to develop.