Our Top 20 DBAs Tips

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

Our own top rules for DBAs

1. Backup before you make any changes.
2. 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)
3. AlwaysOn & Mirror secondary databases can replace a production database only if they are in sync
4. A server restart, while there are existing open transactions, may lose many transactions. Setting Non-Commit will rollback all transactions first.
5. Restore of a database will rollback all open transactions. Remember to set log shipping standby
6. Don’t forget “Begin Tran” at the start of a procedure, so that you can rollback if it fails to COMMIT
7. When using SELECT {CASE … WHEN} AS [] FROM GROUP BY – calculate all options
8. When using SELECT {SUBQUERY} AS [] FROM GROUP BY – calculate all options
9. Function ON SELECT (acting on many rows) can cause CPU load problems
10. JOIN between different datatypes can cause a full table scan
11. When using TABLE parameter  – remember one row showing on statistics may be acting on multiple rows in the execution
12. 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.
13. Check the settings in the SQL Server Agent Properties window so that jobs history is not being purged too soon. Use dbo.sysjobhistory to access jobs history
14. On setup of a new server, before running Index Maintenance, run checkDB.
15. Reorganize can open a pandora’s box of unexpected results.
16. Keep in mind the difference between statistics & index
17. Log growth setting – never leave on percent
18. When query performance has decreased, it may mean several things:
    1. The plan has been replaced
    2. blocked by query
    3. blocked by isolation level
    4. resource bottleneck
    5. max DOP change
    6. data reader/network bottleneck
    7. compatibility level replace
19. 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.
20. Think about the design of any change and test/verify before starting to develop.