Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

Posted Apr 27, 2010

Database Monitoring can Save You Money

By Denise Rogers

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

» See All Articles by Columnist Denise Rogers

Latest Forum Threads
DB2 Forum
Topic By Replies Updated
tables get lock during backup in db2 v9.5 Hamsoo 0 May 8th, 01:03 AM
Query to Pull Last 7 days records from a table vgoushik 0 March 20th, 06:05 PM
DB2 Visual studio addin: ANSI/Unicode problem 10Pints 0 October 5th, 02:08 AM
Things that are everbodys labor day ideas? Lipsett197 0 September 1st, 09:42 AM