A healthy database saves money, seriously!
What do database administrators that are advocates of database assessment and database monitoring know that you don’t know? Isn’t it time you found out?
So you have a database running well in production and life
is good! Right? Right! Then you’re told, “With the acquisition of Cash
Money, Inc. there will be a new project created to integrate the company’s data
into the current production environment over the next six months!”
What is your reaction? Do you panic and break out into a cold
sweat — or do you welcome the opportunity to work on a cool new project?
Depending on your reaction, it means that you have
been an advocate of database assessment and monitoring or NOT!
What is a database assessment? In many ways, it is almost
like giving your database a “physical”. It was also called a database audit,
back in the day. However, the goal is the same. That is assessing the integrity
and performance of a database and identifying areas of improvement.
Understand and Identify the Level at Which Your Database is Operating
So how does one go about conducting a database “physical”?
Where does one begin?
A good way to approach this is by establishing a base line.
Why? Because you want to understand and identify the level at which your
database is currently operating. It’s also a great way to determine the current
amount of resources being consumed to complete routine operations, etc.
It’s fun being a database administrator because gathering
this information means that we as DBAs have to roll up our sleeves and flex our
muscles. It means we actually get to use the various ancillary tools that were
packaged with the version of the RDMS that contains data and related artifacts!
We also get to do some detailed analysis as we pour through the raw data
generated by these monitors! Yes, I know it sounds very old school. Nevertheless, I think
it’s also pretty cool to roll up our sleeves and “look under the hood”!
How do we do this? Strap a heart monitor on that database
and put it on a on a treadmill! Seriously, I meant to say that it starts with
turning on monitors and traces at all levels, operating system, database and
application. I have to keep my composure so my apologies, I get carried away on
this stuff! Back to being serious, the data being collected should include
specific areas of performance (such as SQL, indexing, I/O throughput, memory
usage) and storage capacity (growth rate of data and the amount of allocation
storage). These monitors should and do have reporting capabilities that
capture the detail in a textual and graphical format.
The data collected frames the baseline. That is, it provides
us with information that says this is what it takes to complete these routine
tasks within this time frame. I am talking about the amount of resources
utilized to complete sorting tasks, elapsed time during I/O activity, the
amount of data being brought into memory, etc.
Taking this data, we should get a good understanding
of the way the database performs. We should also get a good picture on the data
growth rate in the database.
So good people out there in DBA land, do you think that you
would be shocked, depressed or pleasantly surprised at the information that
formed the baseline. For the record, it has been all of the above for me…I am
telling you I have been through it all!
The next step is to meet with the stakeholders on the team
— the team leaders, application developers and business analysts to discuss
the findings. These work sessions should have the following goals:
1) Definition of optimal
performance levels (i.e. the database should be performing within ‘X’
parameters);
2) Cataloging and prioritizing the
findings into actionable items. These goals facilitate the process of
identifying the areas that should be the primary focus, etc.
This is not a isolated activity done in a vacuum…the
database is part of the framework that allows the business areas to complete
routine everyday “keep the lights on” functions and attention should be paid to
its care and feeding and yes, health!
Once a decision is made, execute! Apply the change to the
production database (with all the required testing in the various environments,
following the SDLC, etc). It also means setting/creating another round of
monitors and traces to quantify the net effect of the changes/modifications
made to the database.
Once the performance goals have been met, document the
effort, highlighting the statistics from the baseline to the finish line! In
this way, we can provide upper management with a summarization of the effort,
along with supporting quantitive and qualitive statistics, making sure there
are cool looking diagrams and graphs with lots of color! When the
management team sees the value of doing database assessments, there won’t as
much of a push back in the future! Trust me on this one!
Okay, now I have a couple of experiences to share with you
to put this in a better perspective. These are true stories!
AN Ounce of Prevention…a True Story!
I started this assignment in a major healthcare company. My
role was to work as a data architect and database administrator to support a
number of projects for a data mart application. Oh! By the way this
includes a data mart that is a snowflake schema; the application and
presentation layers are on different flavors of UNIX, the developers are
working off shore with a time difference of 10 hours, there is ETL software to
populate the data mart, with some stored procedures sprinkled on top!
The first thing I did was to gain an understanding of the
projects that were slated for the upcoming year and the impact these work
efforts will have on the data mart application. Armed with the knowledge of the
work being scheduled, I gave my data marts a “physical” that would not soon be
forgotten! I collected data generated from the performance monitors and
information on the amount of storage being utilized to hold current production
volumes. Moreover, the findings were so alarming that I had to take my
contacts out, rinse them off and put them back in again! Not only was the
data mart not completing its routine daily tasks but also the containers
holding the data were at a 98% capacity (the mart was architected where storage
is manually administered). My next steps were to meet with the project manager,
developers and business analysts to present my findings packaged in a way that
enables a good understanding of the “critical condition” of the environment and
to lay out an approach where the end goal is to improve the “health” of the
data layer and all its integrating and intersecting points. After I achieved
buy-in from all the stakeholders, I created a mini-project plan that put the
data mart on a road to recovery, working in phases that improved performance by
using fewer resources in less time and submitted requests for additional
storage with the required configuration so that space was no longer an issue
over the next eighteen months. This entire effort was completed well before
the start of any of the slated projects for the year with the data marts not
even showing up as a blip on the radar relative to issues of any kind! YEA!
That’s how we roll!
Pounds of Cure…Another True Story!
In another chapter of my charmed DBA life, I was called
“CHUM” and thrown into shark-infested waters. Translation: I was assigned to
work on an application that had reeked of that foul odor called failure where
on a good day the finger pointing was always at the DBA. I am being serious
too! On this assignment, I started to toughen up or grow a second skin. I also
become an expert fire fighter and smoke jumper (aka developed into a really good
problem solver).
I was assigned to work as a DBA on a major database
application that contained critical data for healthcare providers, healthcare
networks, hospitals, etc. I attended a kickoff meeting where a new project was
launched that would expand the provider network by adding new healthcare
networks, doctors and new members from a new region of the country. This
project was highly visible and hugely important to the organization as it meant
additional revenue would be generated (AKA more $$$ and more smiles!). My
initial role on the project was to assess the database environment (this was a
good finger pointing experience) that would be impacted by this project. I did
all the things we have been discussing and presented my findings. However, I
ended my presentation by making very strong recommendations that the project
plan be expanded to include tasks that would improve database performance and
strengthen its environments such that not only would the database perform
within current SLAs but it would also be able to be expanded to include more
data without the lights flickering! The first set of changes deployed were
re-coding SQL statements and re-designing the indexes. The result was that one
component of the batch cycle went from running for eight hours down to two
minutes! Yes, that’s right! Count it! Two minutes! The project
management team did resist at first but when we discussed the risk of not doing
anything, everyone agreed! Happy ending! No longer did I have mobs of angry
developers and business analysts with torches and pitch forks rushing my cube!
A Healthy Database Saves Money, Seriously!
Okay, so here’s the BIG FINISH. Conducting database
assessments frequently is not just about ensuring that a database is performing
within the designed parameters. When you apply any combination of changes to
improve performance, you are also doing more with fewer resources. Meaning it costs
less to complete routine processes. You also dramatically reduce the number of
occurrences related to outages. It does really cost more to repair than to
prevent! Take it from me, I have lived it, bought the t-shirt and I have the
soundtrack!
Additional Resources
MySQL Enterprise: Database. Monitoring. Support.
Low-Cost High Availability: Simple Database Monitoring in a Windows Environment
ISACA The Art of Database Monitoring
Microsoft TechNet Monitoring and Enforcing Best Practices by Using Policy-Based Management
IBM Best Practices: Tuning and Monitoring Database System Performance
