This morning I attended a breakfast seminar hosted by Quest Software featuring Microsoft SQL Sever MVP, Kevin Kline at the Troy Marriott (Michigan).
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.
- Ensure proper block size (throughout his experience, 64k chunk size blocks are best)
- 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:
- Local (real-time backups) to a RAID 10 SAN
- Short-term archive to RAID 5 online NAS devices
- Longer-term archive to cheap SATA devices
- Place tables and/or indexes in seperate file groups on seperate drives
- Ideally, run backups serially vs. in parallel
- Of course, pre-size, dont rely on autogrow
- Add spindles for read, controllers for write
- Partioning for highly stressed databases
In closing, Monitor -> Tune -> Repeat. A link to his slide deck will be available shortly.
State of the Emerging CIO 