SQL Tuning w/Kevin Kline

In my role, I have to stay abreast of both leading and/or emerging technologies, but also current best practices; everything from software development methodologies to infrastructure maintenance/optimizations to keep our systems performing and most importantly, directing our staff when they aren’t. Sure, its easy to argue that nobody in tech today can master all the increasing complex technologies within IT, however, when you’re in a small (but growing) company like ours you’re forced to be resourceful. To get by, I do alot of reading and take opportunities to participate in as many industry outlets as possible. If I can’t draw from my own experience to solve a problem, I’ve atleast been exposed to enough channels (products, companies, individuals, etc) to find the experts that can – Kevin Kline is one of those experts.

While I’ve never had to call upon Kevin for help, I have followed his work over the past couple years, both through Quest and a few of the books he’s written and magazines he writes for. It was nice to finally meet him and validate some internal tuning techniques we’ve been utilizing compared to those used by himself, or companies he’s worked with. The session ran about 3 hours and was divided primarily into two sections, managing data avalanches (Very Large DataBases or VLDBs) and SQL Tuning. Some highlights of the session are below:

What is a VLDB?

In years past it may have been about size, for example:

1980 = 100mb

1990 = 1gb

2000 = 1tb

2005+ = 10tb

Today however, its more about what Kevin calls, “Is it easy?” meaning, is it easy to manage? VLDBs are by default, complex, lots of data, lots of moving parts.

Kevin then went on describing how & why were are storing more data today, many of which we’re well aware of:

SOX

online archives/backups/retention

more sophistacted business processes

Regarding VLDBs, its equally important to understand disk I/O as much as it is about transactional throughput/sec or DB size. Know your maximum burst reads/sec & trans/sec requirements per SLA before sizing your DB.

He, later went on to describe, most DBAs spend a LOT of time fixing bad code of developers! Gartner published a study showcasing where sql performance issues typically were recognized, irregardless of DB flavor:

80% poorly writen tSQL

15% poor architecture/design

5% hardware

Progressive organizations are even moving away from tape all together, since disks have become so cheap, some companies are deploying scenerios such as: