June 2010 - Posts
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
There are so many cool new features in Sql 2008 release
2 it was difficult for me to pick a topic for T-SQL
Tuesday
. But the one that I am
now a secret fan of, I once resented for its creation.
Let me explain, for years I have encountered reporting
systems cobbled together in tools like Access and Excel built by "database
hobbyists" who had no formal training in database design or best practices. They
would take their monstrosities as far as they could go before ultimatley
it stopped working or the person that wrote it left the company.
At that point it would become the resident DBA's problem to support it
as a Live application.
So when I first heard of Power Pivot, a sense of Deja Vu
overtook me and I felt like the guy in the Ausin Powers movie
, knowing the inevitable is coming but somehow unsure how to get
out of the way.
But when I eventually saw it in action, I quickly
realised that it is a very powerful tool. It has a much smaller "time to market"
than traditional BI architectures. Combined with the new features of Excel, some
pretty impressive dashboards can be produced.Of course PowerPivot is not a magic
bullet and along with potential scalability issues there are the usual
issues such as master data management and data quality that cannot be overcome
easily with power pivot. As a tool though, it has potential.
Traditional BI is expensive, both in terms of time and
the amount of resources it takes to deliver the system. The time lag between an
analyst or a commercial accountant requesting reports and the report being
delivered can make a huge commercial difference. I have observed companies where
empowered end users become extremely productive when allowed to plough in to
various disperate datasets. It may not be the correct way or the most
sustainable but its cheap and quick. In these times when budgets are being
slashed and we are forced to deliver more with less, why not empower the end
user in a tool that is designed for exactly this task....
@blakmk