I have been suddenly put-in charge to review, recommend and resolve database related issues. Specifically, performance issues, recommend maintenance plans. What should I immediately start to look at in the short run and in the long run? Any suggestions would be greatly appreciated.
The first thing that I would do is to document the servers and databases, know what the recovery mode is for each database, and what the current backup strategy is. You need to be able to know to recover each database in the event of a failure. Know how much data loss might be at risk.
Are the databases stand alone, mirrored, replicated, log-shipped or clustered.
Once you get a feel for what it would take to recover the database – you’ll definitely want to test it and make sure you’re backups are good.
Do those databases have the latest service packs? If not, they should. Once you get a grasp on how to make sure you can restore the database, you’ll need to start looking at security, is it Windows or Mixed Mode, who has access to what, does everyone in the world have the sa password, or do people have the minimal amount of access that they need to get the job done.
Paul Randal and Kimberly Tripp, are both very well know and very well respected, you can find a number of blogs, whitepapers and articles they offered for the Accidental DBA. Here’s a good starting point: http://www.sqlskills.com/blogs/kimberly/post/Catching-up-on-resources-the-Accidental-DBA-workshops!.aspx
The response to thispost, http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/2cefcdcb-b562-4d6e-a461-2b7e84eb72a3/, by Johathan Kehayias on the MSDN Forums has some tremendous reference links.
And one more reference link would be http://serverfault.com/questions/22061/resources-for-the-accidental-dba
Finally, keep posting questions to get a better understanding, or for a validation of what you're thinking.
Your question does not mention your experience and i give you my answer from a beginners perspective.
My recommendation is to read the tips at http://www.sql-server-performance.com/tips/performance_main.aspx to give you a quick start on how to track down any performance issues. I also have some stored procedures based on the dynamic management views in SQL Server 2005/2008, that could give you detailed information on about "resource consuming" statments and index statistics, but you still need to learn the basis of the tuning tasks. Tuning is not the easiest part of a DBA:s job and you need to know how SQL server handles your queries to be able to solve any performance issues. Like, if you find IO bottleneck it can be slow disk system or very bad performing queries.
I can share with you some of the procedures I use if you like.
You can get a lot of performance tips at the same site i recommended above. When you want to dig deap into T-SQL i can recommend the books from Itzik Ben-Gan.
A also think you should recommend your boss to in investment is I3 from www.precise.com to monitor the SQL servers in your environment. It keeps track of the resource usage over time and you can drill down to see how the resources are used at the statement level.
Another important part of a DBA:s job is to monitor any SQL agent jobs.
The answers here are excellent, but I just wanted to mention a couple more things:
Change management: How are changes in the environment (such as production roll-outs, patches) etc handled? Are there clear lines of responsibility for functional and performance testing before change scripts get to production?
Hardware resilience & warranty: Is the hardware that the production environment is running on resilient (i.e. clustered with redundant hardware)? Are all the items of hardware covered by a current service warranty? You will want to know that if a disk in an array fails, you have someone to call who will come and fix it quickly.
Security & applicable procedures: If your environment is such that you need to maintain compliance with security standards, then is that compliance being followed? For example, handling credit card information requires PCI-DSS compliance - is that being followed?
Maintenance windows: When are your systems active, and when is it OK to take them off-line? For example, I used to run a few national loyalty schemes' databases - and they could be safely taken off-line between 1AM and 5AM for maintenance, so we set up scripts to automatically and incrementally re-index database tables in that time window.
Reporting channels: Are reports being run and generated from your databases? If so, who do they go to, and what do they call it? You need to know what you need to do if 'Customer X' phones up and says 'I didn't receive my Cosmic Spangbobulation Report this morning'. You will also probably need to know that 'Customer X' in fact relates to the database 'DBMFPP01', and that the 'Cosmic Spangbobulation Report' is actually the 'Sales By Day Report'.
Just a couple of things from experience really.
That's a large task. If this is really your first time out, you need to be very cautious. In fact, you can't be too paranoid.
First, be sure you have a test environment, somewhere that you can screw up everything without affecting production. Once that's done, use the environment religiously. Always test what you're going to do to your production system ahead of time.
That done, the first and most important thing you can do is set up backups. Depending on the business needs of your system you may also need to worry about log backups. On top of that, I would strongly suggest you get DBCC checks running, best of all, run them before your backups and make the backup a dependency of a successful consistency check. Get all this in place before you start worrying about performance. You can use the built in maintenance routines (although I don't like them personally). Just be sure that you don't accept the defaults (which include shrinking your database automatically, always a bad idea).
Once that's done you can begin to worry about performance on the system. But, in order to fix performance, you need to capture a baseline. If you have third party tools, great. If not, use performance monitor to capture counters and server-side traces to capture queries. There's a lot to both these tools, you'll need to track down some good reference material to help out. You can't start fixing performance until you know where things are running slow. The best way to identify this is to look at wait stats. Do a search on wait stats and Andrew Kelly. He has some great articles on how to use these for performance improvements.
I'd strongly suggest reading a few books. Ross Mistry's book on SQL Server 2008 Administration is great. Joe Sack's book on TSQL Recipes is likely to help you out quite a bit.
