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

Posted by blakmk | 5 comment(s)

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