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....
@Blakmk