The Worlds Largest Community
of SQL Server Professionals
How to Perform a
SQL Server Health Check
Brad M. McGehee
SQL Server MVP
Director of DBA Education
Red Gate Software
www.bradmcgehee.com/presentations
My Goal for Today
I only have one main goal for this session, and that
is to persuade you of the importance of conducting
regular health checks on your SQL Server instances
to ensure that they are currently healthy, and
continue to remain healthy.
There is a SQL Server health pandemic.
Most organizations dont realize how sick most of their
SQL Server instances really are.
The Worlds Largest Community of SQL Server Professionals
Can You Honestly Answer
This Question
Are all the SQL Servers you manage healthy, running
optimally and providing the high availability your
organization expects of them?
If your answer is yes, how do you know this?
How would you prove this in an IT audit?
If your answer is no, then you had better find out the
answer quickly if you want to excel as a professional DBA
(and keep your job long term).
My goal of this session is to help you answer yes to the above
question by providing you an easy way to determine if your
servers are healthy, and to be able to prove it during an IT audit.
The Worlds Largest Community of SQL Server Professionals
What We Are Going to Learn Today
What is a SQL Server Health Check
Why Perform a SQL Server Health Check
When Should You Perform a SQL Server Health Check
What Tools Should You Use to Perform a SQL Server
Health Check
Introduction to the SQL Health Check Spreadsheet
The Worlds Largest Community of SQL Server Professionals
What is a Health Check
The Worlds Largest Community of SQL Server Professionals
What is a SQL Server Health Check
Gathering detailed information about your SQL Servers (in effect,
documenting them and also creating a baseline of data for future
comparison). Use a checklist to be methodical.
Comparing collected data to established best practices to identify
potential changes to your SQL Servers so that they meet these best
practices. In other words, identify any health issues.
Examining the differences and determining if the best practices
should be implemented, or, because of special circumstances,
should be ignored. Not all SQL Server should be treated equally.
After determining what changes, if any, should be made, test the
changes on a test server (in most cases).
Roll out the changes to production SQL Servers.
Repeat periodically as SQL Server instances arent static.
The Worlds Largest Community of SQL Server Professionals
SQL Server Health Check Flow Chart
Collect
Health
Check Data
Compare
Against
Best
Practices
Determine
What to
Change
Test
Changes on
Test Box
Implement
Changes in
Production
Todays Focus
The Worlds Largest Community of SQL Server Professionals
Periodically
Repeat
Health
Check
Where Does the Checklist Come From
From past experience, and research on a new
book, I have begun to put together an extensive
checklist about SQL Server configurations,
operations, and best practices.
You can use this checklist as a basis for creating
your own customized checklist that best meets
the needs of your particular environment, and
can be used as proof during an IT audit.
It comes in the form of an Excel spreadsheet to
make it easier for you to use and customize, and
to store the health check data you collect.
The Worlds Largest Community of SQL Server Professionals
Where Do The Best Practices Come From
To compare the data you have collected during your
health check, you must have a point of reference (best
practices) to determine if your server is healthy.
Ideally, you will have created your own best practices
standards guide that you follow for your organizations
SQL Servers. If not, you should create one that acts as
your point of reference.
If you are at the point where you need to create your
own best practice standards guide, then look to
authoritative resources to begin creating the guide.
The Worlds Largest Community of SQL Server Professionals
Authoritative Resources
Books Online
www.sqlcat.com
www.sqlskills.com
www.bradmcgehee.com
Microsoft KB Articles
Microsoft SQL Server Team Blogs
Microsoft MVP Blogs
Books, such as Microsoft SQL Server 2008
Internals , Professional SQL Server 2008 Internals
and Troubleshooting, & my upcoming book.
The Worlds Largest Community of SQL Server Professionals
Why Perform a SQL Server Health Check
Provides documentation and a baseline for:
Performance Tuning
Troubleshooting
Rebuilding, Disaster Recovery
IT Audit
To identify potential problems & fix them early.
To implement best practices (& help develop your
own best practices standards guide). This results in
performance optimization and higher availability.
Helps to standardize your SQL Servers configuration
throughout your organization.
The Worlds Largest Community of SQL Server Professionals
When Should You Perform a
SQL Server Health Check
If you currently administer any SQL Servers and you
have never documented them before.
If you start a new job, to quickly learn about the SQL
Servers you have inherited and now own.
If you are a consultant, and you need to get quickly up
to speed on your clients SQL Servers.
Once an initial health check is performed, it should be
repeated regularly to ensure the continued health of
your SQL Servers. Follow-up health checks will be much
faster because you already have collected the basics.
The Worlds Largest Community of SQL Server Professionals
What Tools Should You Use to
Perform a SQL Server Health Check
OS tools, such as administrative tools and event logs
SSMS, including Standard Reports
T-SQL, PowerShell, DMVs, system views, system stored
procedures, etc.
SQL Trace/Profiler
Performance Monitor (maybe Data Collector)
RML Utilities for SQL Server, PAL, & other freeware
Create your own automated collection, reporting system
Use vendor application: SCOM, SQL Response, etc.
Check out the free eBook SQL Server Tacklebox
The Worlds Largest Community of SQL Server Professionals
Where Should the
Health Check Data be Stored
If the number of SQL Servers you manage are few,
then collecting and storing the data in a spreadsheet
is probably the easiest and quickest way to collect
and store the data.
If you have many SQL Server instances, then you
need to automate the health check process and store
the data in a database. If you dont have the time to
automate your health checks, then doing it manually
with a spreadsheet is better than doing nothing at
all.
The Worlds Largest Community of SQL Server Professionals
Things to Keep in Mind When
Performing a SQL Server Health Check
When collecting data for a health check, keep the
following in mind:
Not collection enough data can lead to bad choices
Not collecting the right data can lead to bad choices
Collecting too much data sometimes can make lead to the
problem of You can't see the forest for the trees.
Misinterpreting the data can lead to bad choices
Not acting on the data is just inexcusible
The Worlds Largest Community of SQL Server Professionals
Introduction to the
SQL Health Check Spreadsheet
Download the spreadsheet from:
www.bradmcgehee.com/healthcheck.zip.
Not all of it applies to every SQL Server instance.
Even if a check item does apply to you, you may
determine that it is unimportant to you.
Doesnt have to be completed in any special order.
Not every possible checklist option is listed.
Modify as necessary to meet your needs.
Keep spreadsheet updated as changes are made.
The Worlds Largest Community of SQL Server Professionals
SQL Server Health Check Checklists
Hardware
Operating System
SQL Server Settings
Database Settings
Security
Database Maintenance
SQL Server Agent Jobs
Logs
Monitoring
Performance (work in progress)
High Availability (included in other checklists for now)
The Worlds Largest Community of SQL Server Professionals
Health Checklist
DEMO
The Worlds Largest Community of SQL Server Professionals
Take Aways From This Session
Obviously, this session has barely scratched the surface
of what it takes to perform a complete SQL Server
health check.
Hopefully, I have persuaded you of the importance of
performing SQL Server health checks.
You should also now have an idea of where to start to
begin performing your own SQL Server health checks.
When you get back to your office, download the
spreadsheet and begin slowly, as you have time, to
perform a health check on each of your SQL Servers.
The Worlds Largest Community of SQL Server Professionals
Q&A
Please speak up so that others can hear you.
If I dont have time to answer your questions
now, please see me after the session, later
today, or you can e-mail me at
bradmcgehee@hotmail.com.
The Worlds Largest Community of SQL Server Professionals
E-books, Websites, Slides & More
Free E-books on SQL Server:
www.sqlservercentral.com/Books
Check these websites out:
www.SQLServerCentral.com
www.Simple-Talk.com
Blogs:
www.bradmcgehee.com
www.twitter.com/bradmcgehee
Contact me at:
bradmcgehee@hotmail.com
The Worlds Largest Community of SQL Server Professionals
The Worlds Largest Community
of SQL Server Professionals
Thanks for Attending
Visit www.sqlservercentral.com for free SQL Server
eBooks, articles, videos, blogs, news, and more.
Please Dont Forget to Turn in Your Evaluations
