“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)