Whats Your Carbon Footprint?

Last wednesday I had the privilege to attend the Sql Master Class with Kimberly L Tripp and Paul S Randal. A lot was discussed about the causes of poorly performing systems. The session as a whole thought  left me with one overwhelming thought, "the choices we make as DBA's and Developers can have a huge impact on our Carbon Footprint".

Kimberly talked about incorrect sizing and use of datatypes, like using big int instead of tiny int and datetime instead of small datetime when additional precision is not needed.  Or the hideous use of GUID's and their impact on storage and performance requirements. The attitude of "disk space is cheap" seems to dominate as an attitude when we design databases and few us even give a second thought to the impact our storage choices make.

However its not so cut and dry, the bigger the row size, the less pages fit in memory. The more times Sql has to read a page from disk, which is an expensive operation in terms of time and resources (power etc). When GUID's are used as primary keys they take 4 times as much storage as a conventional int. Every index stores the GUID's separatley if the GUID is actually the primary key. While this may not be a problem on small databases as the database grows these differences become noticeable . Kimberly discusses this issue in more detail here.

It doesnt just end there though, as DML is applied to the rows or even just index rebuilds, bigger rows means bigger log records being generated, bigger logs on disk, backed up to tape, replicated or mirrored. Oh yes and then theres the reporting, data warehouse and the DR server which also grow at the same rate. The power and network latency that it takes to move all this data around and back it up. 

And its not just table design that effects the resources we use: badly written queries hammer system resources like CPU and disk unneccesarily. Lazy DBA's doing full backups instead of a combination of full and differential also have their own impact in terms of time and resources. Things in the electronic world tend to grow exponentially not sequentially, so what may have started as a very minor decision can turn into something with a major consequences during the lifetime of a system.

I used to see TV advertisments in England telling you to turn off light switches to help prevent global warming, but as DBA's and Developers the impact of a light switch is miniscule compared to the impact we have in the course of our careers. If the Perfect Storm of 2030 where we will experience chronic global shortages of food, water and energy doen't scare you, it should. But whether you are driven by a desire to lower your carbon footprint, or a desire to reduce the financial cost of the resources you  consume, you can make a difference....


Published Sunday, June 20, 2010 12:46 PM by blakmk


No Comments