Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

John Sansom - SQL Server DBA in the UK

John Sansom (Blog | Twitter) is a Microsoft Certified Master (MCM) of SQL Server and publisher of the free SQL community ebook DBA JumpStart, an inspiring collection of advice for Data Professionals, written by 20 SQL Server experts. Awarded the Microsoft Community Contributor(MCC) award, John is a prolific blogger and can be found regularly writing about SQL Server and Professional Development over at www.johnsansom.com.

10 Things Every DBA Should Do

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.

If you enjoyed this post then you'll love Email inbox or favourite RSS Reader.

Other Things That Every DBA Should Do

Comments

Posted by ThomasLL on 8 February 2011

Good Stuff

TheSmilingDBA

Posted by Jason Brimhall on 8 February 2011

Excellent list - I can't think of anything to add at the moment.

Posted by Dukagjin Maloku on 8 February 2011

Cool stuff, thanks for sharing!

Posted by Jason Strate on 8 February 2011

Great list.

Posted by John Sansom on 9 February 2011

Thanks for the feedback gents, I really appreciate it!

Posted by Anonymous on 10 February 2011

Pingback from  Twitter Trackbacks for                 SQL Server Central, 10 Things Every DBA Should Do - John Sansom - SQL Server DBA in the UK         [sqlservercentral.com]        on Topsy.com

Posted by pankaj_jha_85 on 13 February 2011

Nice article.

Posted by Leela Krishna on 13 February 2011

Useful tips for New and reminders for others. Nice article.

Posted by chetanr.jain on 1 March 2011

Very userfull.

Posted by SQLQuest29 on 7 June 2012

Gr8 Post .. but some how link in no 7 does not work.

The resource could not be found

Sorry, we could not find the resource at /blogs/johnsansom/2011/02/08/10-things-every-dba-should-do/Finding%20Indexes%20Not%20In%20Use

If you followed a link on SQL Server Central, please contact us to let us know.

Posted by John.Sansom on 8 June 2012

Thanks, glad you enjoyed it!

I'll go ahead and get the link corrected on the article, in the mean time you can use the links from the original source here:

www.johnsansom.com/10-things-every-sql-server-dba-should-do

Leave a Comment

Please register or log in to leave a comment.