“Green” Database Administration

For the past few years, I have seen tens of sql server boxes added in my work environment every year, meaning more complexity and overhead cost in database administration, I gradually come to a “green” design principle with two points.

1. designing administration framework with a “footprint” as small as possible on servers.

2. designing administration framework with CPU usage as small as possible on servers.

Here “footprint” means the number of database objects that are needed to fulfill the function. My initial target is to reduce table numbers AND sizes on disk storage, but later I think even objects like stored procedure, views etc should be as few as possible because they occupy space too.

CPU usage is easily understood, when CPU goes high, more power will be consumed, I am thinking if I can reduce CPU by average 1% per day, I probably can save at least 1 Wattage /hr * 24 hr / day * 365 day = 8760 W / year. From a world perspective, say we have 1 million sql server boxes, then we can save about 9 million kW power per year, that does not seem too small.

So here are two simple cases where I apply this “green” design principal:

1. I ensure all my monitoring procedures (such as replication / disk / memory / cpu / user connection monitoring processes and backup / restore jobs  share one common error table instead of each using its own.

This simplifies lots of administration work if I want to check error logs, also it serves the purpose of reducing the database objects in my administration framework, thus it is “green”.

2. I use event notification method to reactively detect / report blocking issue instead of using a job to proactively run every <x> seconds to see whether there is any blocking.

This  reduces the unnecessary job executions if there is no blocking and actually in a well tuned environment blocking (under a specified threshold, say 10 seconds) should be rare, like once or twice per week. In my environment, the blocking threshold is 5 seconds (exec sp_configure ‘blocked process threshold’, 5), and we usually see less than 5 such alerts every week.

I hope all DBAs / developers can think of this “green” principal when designing / developing / refactoring systems, and together we may help our mother Earth a little bit.

(I have to admit there are times the two points are against each other, i.e. you need to use more space to exchange for less CPU or vice versa, for example in almost all DW projects, we need to generate lots of staging / intermediate tables from the raw data, but these staging tables serve for the purpose that we can quickly get the final results, i.e. reduce the CPU usage. In such scenario, we have no choice but to follow the common wisdom)

This entry was posted in Uncategorized. Bookmark the permalink.

3 Responses to “Green” Database Administration

  1. way0utwest says:

    Interesting idea. We can make some better design decisions in small ways that add up to big ways. I think looking at virtualization as an option, about combining database onto instances, rather than separate instances for all database, and other small choices, which do not take much time, are good ways to do this as well.

    Great point in event notification. I need to do more of that.

    • jxhyao says:

      Thanks for the comment Steve. I cannot agree more that better design decision in small ways can add to a big impact to the whole “green philosophy”, which at the end will help us DBAs as well in terms of more simplified / robust solutions to our problems.

  2. jxhyao says:

    I am amazed/humbled to see the big hits of this blog, and I believe this is because in the heart of every DBA/Developer, we want to our “actions” are “green” to the environment in our daily life and work as well.

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