Recently I spoke about how The Best Database Administrators Automate Everything, as well as Document Everything and this got me to thinking in general about things that every Database Administrator (DBA) should be doing.
No two Database Administration jobs are the same that’s for sure but there are definitely things that I believe every DBA should be doing. In no particular order of importance then, here are 10 of them…
1. Database Backups
If you’ve been working with database technology for any reasonable amount of time then I really don’t think this one needs much explanation. For those that are new to the game read The Database Administrators Primary Responsibility.
2. Test Every Change
The only way that you can even remotely begin to feel a degree of confidence in a data tier change that is planned for the production environment is if it has been tested thoroughly and preferably by you. If you are going to be implementing the change, you want to make sure that you know how it is “supposed” to play out in production and the only way that you can really do that is if you have performed a dry run yourself. It’s important that you Think Defensively in your role as a DBA. If you work in an environment where testing is the responsibility of a completely separate team or department that’s fine but you should still do your own testing. It’s not uncommon for change implementation to occur at unsociable hours and you don’t want to be thinking on the fly at 3am. You want to have drilled ahead of time exactly how the change is going to play out.
3. Baseline
When the call comes in that Server X is performing like a dog, how are you going to be able to tell if it’s actually performing outside of the norm if you have no data or historical information to reference? In order for a SQL Server DBA to be able to accurately asses where in the overall scheme of things a servers current performance profile is, you need to have a point of reference from which to start. CPU running at 60% is not necessarily a problem in it’s own right but if the server typically runs at 30% then clearly something has changed. It’s important that you take the time to create performance baselines for the SQL Server servers that you manage. There are many off the shelf products that can do this for you or you can also easily roll your own. Brent Ozar has a fantastic tutorial on how you can use Windows Performance Monitor (PerfMon) to monitor your sql server servers, create baselines and the even how to slice and dice the metrics collected in Excel.
4. Monitor
Baselining of course is only a part of a monitoring solution. You are going to want to proactively monitor your SQL Server environments so that you can react to things before they become a problem. If a database backup fails, you want to know about it. If the database data drive is approaching capacity, you wanted to now about it weeks ago. Make sure that you have an adequate monitoring strategy in place for your environment so that you are not caught out by unexpected events and are on the case working towards a resolution before your users are at your desk asking why they can no longer access the database.
5. Plan for Growth
The Pillars of DBA Faith
At what point in time is your database server going to run out of disk storage? If you don’t know the answer then how are you going to guarantee that you can provide sufficient storage for the databases that you manage. It’s important that you get a handle on not only how much space your SQL Server servers and their databases currently require but also what their future requirements are going to be. Start recording the size of your database data files regularly and you can begin to forecast your growth requirements for your environment.
6. Run CHECKDB regularly
We all know that data is our most valuable asset, so it’s vitally important that we regularly check to make sure that our databases are in good health. Here’s a tip on how you can Simplify Your DBCC CHECKDB Output. When your large production database suddenly goes offline, your requested to run a CHECKDB and your manager wants to know how long it’s going to take, what are you going to say? If you’ve been running CHECKDB regularly you’ll have the answer. I often hear people claim that they cannot run CHECKDB because they have a very large database (VLDB) but there are a number of options available to you. Read Paul Randal’s excellent CHECKDB From Every Angle: Consistency Checking Options for a VLDB for a number of strategies to consider.
7 . Identify Unused Indexes
Indexes are only useful if they’re being used, so every once in a while it’s a good idea to review your index usage information to identify potential candidates for removal. Those clever folks over at SQL Server Pedia have some great content to help you with this, such as Finding Indexes Not In Use.
8. Identify Missing Indexes
Did you know that SQL Server actually does this automatically for you! Every time the query optimizer encounters a query that could have benefited from the existence of an index, statistical information regarding this lost opportunity is recorded. You can access this information by reviewing the appropriate Dynamic Management Views(DMV’s) as described in the excellent tutorial Find Missing Indexes. You should make a point of regularly reviewing(not blindly implementing) this information, in order to identify potential performance improvement opportunities for you to investigate further.
9. Proactively Query Tune
Don’t leave your query performance tuning until you actually have a performance problem, it’s too late by then. You want to be reviewing the performance of your SQL Server queries regularly in order to identify those that could benefit from a tune up. Some of the great ways that you can review your query performance include using the freely available SQL Server Performance Dashboard Reports, using DMV’s to Identify Your Most Costly Queries or perhaps using one of the many great third party products available for this purpose. However you decide to query tune, just make sure you are doing so proactively.
10. Install sp_WhoIsActive
This incredibly awesome creation is my go to weapon of choice when it comes troubleshooting a SQL Server instance. If I want to get an immediate view of what’s happening on a given server right now, this is how I like to get it done. If you are not using sp_WhoIsActive as part of your DBA tool kit then you are missing out big time! Get yourself a copy now absolutely free courtesy of author Adam Machanic. Be sure to also leave your comments and thanks.
Other Things That Every DBA Should Do
