SQL Server Health Check

Posted: December 17, 2012 in dbUtilities, SQLServerPedia Syndication

It is that time of the year, where everyone is starting to think about what they need to do for next year. Many of us have time between Christmas and New Year’s day, where our systems are not working as hard as they do the rest of the year. I know that I like to take advantage of that time to get a lot of my maintenance completed when the impact to the end users is even less likely to occur. One of the tasks that I ensure I complete that week is my SQL Server Health Check. There are many aspects to these Health Checks:

Backups/Restores – I validate that all the backups are working as they should, granted it is one thing to make sure the backups are still kicking off like they should be, but just as important I validate that the backups meet the current recovery requirements. I validate the max potential data loss and ensure it meets business requirements.

Growth – Often SQL Server databases are hosted by hardware that just meets the requirements for performance and redundancy. As a database grows and more users rely on it the requirements may change. It is important to validate that the current host meets the current requirements.

Performance Baselines – Something that I prefer to do during busy times on the server. Perf Mon Counters and Monitoring Products give you part of the picture, but ensure you understand the system as a whole. Talk with the end users and watch performance indicators inside the database such has how fast the stored procedures are running. It is critical to know what these indicators are, what they mean and when they are considered good or bad. If you are looking for these indicators reflect back to when someone calls you to inform you that the system is slow, what are they using to make that determination?

Alerts/Notifications – Are you watching for all the alerts that you should be, things like free disk space, database growth, severity errors over 17? What happens when these alerts are fired, are you notified via email is the email configuration correct?

Security – Has the permissions of your users changed, what about new logins, or group membership?

Documentation – One of the more helpful tools is documentation; it can help when recovering a server, troubleshooting problems along with many other uses. Yet it is so easy for this to become outdated, and forgotten about. In your health check, make sure that your documentation is current.

Configurations – Validate the current configuration, has there been any unexpected changes? Are you seeing anything that could improve or hurt your performance?

POC’s – A current list of all your points of contacts is helpful when trying to complete tasks quickly. Do you have all the information you need to have backup tapes returned from off-site storage, or do you know all the technical support numbers for all the products that you use?

Service Packs and Patches – Are you testing these, and staying on top of keeping your system updates with the most recent releases?

I spend many hours each year performing health checks for many different clients, and this is just a quick insight into how I complete these studies. Yet I am always surprised at how many problems could have been avoided just by performing these simple checks. I hope you enjoy your holidays.

 

Comments
  1. Chris Yates says:

    Fantastic topic my man. Two pain points for myself has been documentation and POC’s. People rarely like to get down and dirty and complete a thorough document but they are extremely necessary for future reference. POC’s; it’s been nice to incorporate POC’s into the “Utility Database” schema. Breaking it down for contacts of server owners, business owners, and in my case database owners.

    Great post man thanks for sharing this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s