SQLU DBA Week – Set It And….

Welcome to the third day of DBA week at SQL University. On day one, I kicked off the “back to basics” journey I’ve chosen for fellow students with my post reminding you that you’re only as good as your ability to restore. Microsoft Certified Master Robert Davis brought us a good reminder on Day 2 of some mechanics around restoring and backing up. Our coach, Tom LaRock, also gave us some good reminders earlier in the week. Today we are going to continue in a lesson that steals thunder from myself and a presentation I really enjoy giving to new or reluctandt DBAs – As a DBA Where Do I Start while we focus on the fact that SQL Server is not like the Ronco Rotisserie Chicken –

it isn’t…

Set It And Forget It

This is a drum I beat a lot. Not because I enjoy its sound. I beat this drum because I often see the results of the “Set It and Forget It” mentality. I am often called in for a cleanup in aisle 32767 because someone, somewhere in the early life of a database thought you could just install SQL (but they make it so easy!), drop a database on there that is critical to the company, pat themselves on the back and walk away to tackle new challenges. I beat this drum because I hear things like the following (true quote from a large ERP system vendor, by the way) quo

te from vendor:

Oh! You’re on SQL. I wouldn’t worry about index rebuild jobs or statistics updating, just keep auto update stats on. SQL shops are easy, anyone can walk in off the street and play the role of DBA!

Sure it is a bit of a paraphrase but the main points are 1.) He really did say the part about coming in off the street and 2.) He was dead serious about not doing any index or statistics maintenance. Ever.

I’ll let you cut this class now, if you want – Do you ever talk to your SQL Server? Do you ever just ask it, “Hey, SQL instance, how’s it going? You feeling alright?” Do you do you look at the logs? Perform Index Maintenance? Take Benchmarks? Basically, do you love your SQL Server instance? If you keep saying “YES!” Go ahead. You can play a flash game again. If you felt a little lump in your throat because you know your SQL Server instance is sad, lonely and set and forgotten then keep reading – there is no time like today to restore that relationship. Forget about apologies… Actions speak louder than words and that is what we’ll focus on. Actions like –

“How are you? I know we haven’t talked much lately”

Spend some time on your SQL Servers. They’ve been working hard all this time for you and you can’t even remember the last time you just stopped and asked it how it felt. That’s a good first step, no? You don’t have to go out and spend a lot of money on it (though if you have the budget, consider a monitoring tool! There are several out there at great price ranges). Jump onto SQL Server Management Studio (if you are using SQL Server 2000, nicely tell your server you’re thinking of a newer model). Expand Management, Then SQL Server Logs. Double click on the current log and have a look around. What do you see? Any problems? Look them up and get them taken care of. You hopefully see backups being taken (look at the homework for a tip here), if you don’t you might want to stop reading here and go back to my first post of the week. Want to make this relationship work? Make a new habit – Do this every day – take a quick look at your log for the entries since the last day.

Want to automate this? Sure you can automate things, roll your own monitoring process, etc. I would say don’t waste your time, just buy a SQL monitoring tool that has support teams, development teams and keeps up with SQL Server releases so you don’t have to change your code when you upgrade. 

“Can I help you with that?”

Your SQL Server does a great job all by itself. That doesn’t mean you can’t take an interest in it and offer to help it run better – We need to make sure we are maintaining our SQL Server as we move forward. I have a standard set of maintenance tasks I run on SQL Servers I own or inherit. These tasks do things like –

• Recycle The SQL Server and Agent Error Logs – Sounds silly and useless but it sure saves time when trying to open an error log for troubleshooting purposes when you don’t restart your instances often. (Learn about sp_cycle_errorlog and sp_cycle_agent_errorlog)

• Maintain Your Indexes – I use Michelle Ufford’s Index Rebuilding/Reorganizing script (don’t let the baby picture scare you. Keep reading). Basically the goal here is to ensure that your indexes are doing the best job they can – they can become less helpful over time due to index fragmentation. Get in the habit of performing regular index maintenance (how regular will depend on your environment, pick a time of lower – or no if possible – activity)

• Maintain Your Statistics – Statistics are updated “automatically” by default when a certain amount of data change on the underlying table causes them to be marked as invalid. If that amount of change hasn’t happened, they will remain valid and won’t be updated. This could be good (updating statistics after each row of change would have a negative impact on your system) but it could also be bad (you may never hit the percentage of rows changed but they could be enough to have new statistics make a difference in query execution). These statistics are, in a nutshell, used to help SQL Server determine how best to access the data for a given query. If they are not accurate enough, the approach chosen by SQL won’t be the best it could be. I like to update statistics about once a week for most databases. Choose a schedule that works that balances lower periods of activity or no activity with update and data change frequency to update your statistics.
  

• Clean Up Back Up History – Each time you take a backup in SQL, a record of it and its location is stored in your MSDB database. This is there mainly (only?) to make life easier for you if you chose to do a restore through the GUI – your backups would already be listed for you. This history can grow over time to the point of slowness with the restore/backup dialogs and bloat in your MSDB database. Clean this up on a regular basis. Denny Cherry posts about a way to do that here – though pay attention to my warning in the c comments and don’t go for cleaning up a half decade of neglect all at once.

• DBCC CHECKDB – Corruption kills. The only thing worse than corruption in a SQL Server database is corruption when you forgot that you’re only as good as your ability to restore and you have the lack of backups to prove it. Running regular consistency checks is a good way to find corruption before your users do. Hopefully you’ve ensured you are following the SQL Server IO best practices, virus scan best practices and treat your server well. Corruption can still happen. Running DBCC CHECKDB won’t prevent corruption but it can let you know about it or a trend towards a serious problem a lot earlier than waiting for symptoms to show up. Read Paul’s post from a survey about performing this check and then get working on scheduling a job to do it, don’t be that DBA.

“Are we growing closer?”

How can you know if things are going bad if you no longer have any concept of what normal is? Maybe you forget that first time you saw your SQL Server? You forget what things used to be like when you were both younger and more fit. Your SQL instance has had a lot of baggage added to it, its databases are bigger now. How is it doing? How much longer does it have? It is good to watch how things grow over time. Perform periodic benchmarks! Even if you just use the PAL tool as I described in a previous post and grab some performance data every month, quarter or even year, it helps to see how things are going. You can’t know you are having a deviation from normal if you don’t have a normal to compare to! One side benefit of going with a monitoring tool? Some provide a means for capturing benchmark information quickly.

“You can talk to other people”

But with all of the neglect you’ve given your server, I think no one will fault you for approaching it with the attitude of a paranoid control freak. Alright so maybe this one doesn’t work with the whole real world relationship thing where trust is important. I guess what I am getting at is – do a security audit. Find out who else can talk to your server and hunt them down and… verify that they should have access and at the level they are at. Make sure security best practices are being followed. When I think of SQL Server security I think of my friend K. Brian Kelley who happened to blog a lot about securing SQL Server for this semester at SQL University. Since you are starting to do all these nice things for your server you don’t want someone else to come in and undo all the love you’ve poured into your server. Make sure they can’t or at least only those who are supposed to can.

“Watch out for the parking lot perfume robbers!”

If you really loved your server, you’d help watch its back for all in the world trying to get it. So maybe there really aren’t perfume wielding bandits waiting to render you unconscious at the mall. There are software vendors waiting to do something similar to your SQL Servers! Well okay, maybe that isn’t their intention but sometimes it happens. You don’t have to spend long in twitter to find a victim’s advocate spouting off about how it happened to a SQL instance of theirs. Do some due diligence. Ask your vendors some questions before they move in on your environments. Otherwise you might have a mini rant of your own someday while frantically looking for your backup files.

Quick Homework

This post was quite long. I could have kept going but I think you get the idea… SQL Server isn’t a set-it-and-forget-it application. It requires care and checking on. So the homework is easy –

• Implement Database Maintenance – We talked about some above. Ola Hallengren has a great set of scripts to do much of this. Brad McGehee blogged about Ola’s scripts here as well.
• Check Your Logs – Even if you are just manually looking at your SQL Server error logs daily for now, that is better than being blind.
• Review Access – Just take a look at who has access to your servers. Does it make sense? Do their permissions make sense? Follow up on the ones that don’t.
• Look at Monitoring Tools – Like  I said, there are a lot of vendors. I’ve worked with a few and have my preference. Others have theirs. You can ask about it on twitter or ask me in an e-mail for thoughts on a few of the ones I’ve used and like. Let your server talk to you for a change 😉

I’ll keep Friday’s post smaller so feel free to subscribe to my blog’s feed if you want more DBA Week learning. Also don’t forget to look for Robert Davis’ post on Thursday.

No chickens were actually harmed in the making of this post (real or @SQLChicken)