SQL Server 2008/2008R2: My Favorite Feature

1. My New Crush: PowerPivot

When it comes to R2, PowerPivot is the big charmer for me. It’s as much fun as my spiralizer, and that’s saying a lot. If you don’t know what a spiralizer is, it’s a magical little piece of plastic with a few blades that lets you turn a zuchinni into super long curly strands of veggie pasta, which makes it suddenly more than normal squash. You can also make curly fries, slice onions, and do all sorts of crazy cool things a normal person can’t make with an ordinary kitchen knife.

PowerPivot is just as fun. You can suck in tons and tons of rows with no worries– I’ve already done 850K from my preproduction environment in less than 30 seconds (and the disks in Pre-Prod ain’t exactly smokin fast). And then you can not only slice and dice your data, but create pivot graphs and charts to drill through it super-quickly. With PowerPivot, all those rows become more than just data pretty quickly. And so far I’ve only scratched the surface.

But is PowerPivot really my choice? Sure, it’s really fun and powerful and the newest and the shiniest, but SQL 2008 had a lot of great features. When it comes to making the biggest difference in processing my data and delivering it to reporting systems, a lot has changed lately.

2. My Hot Date: Data Compression

I am a big fan of Data Compression. As a DBA, data compression gives me faster reads, lower storage costs, and helps reduce IO bottleneck– and I deal with lots of systems with so much data that this is a really big deal. After only a short while spending time with my new friend, Data Compression, I can’t imagine being apart.

As befits data compression, I’ll keep this section short: there’s something else I love even more.

3. My True Love: The Data Collector

I have a problem. I’m a little bit obsessed with the Data Collector and the Management Data Warehouse (also sometimes called Performance Data Collection).

At first meeting, I didn’t love this feature. When I heard “performance data collection,” I thought, “but I already have performance counter information collected by my monitoring system. We should invest time in building out the right monitoring thresholds and reporting for what we already have.”

But I just didn’t get it.

First, the built in collections cover a great deal of trending which production monitoring can’t and shouldn’t capture, because monitoring should be targeted and fast so it can quickly identify problematic conditions to generate tickets/pages. When this happens, the information collected by the data collector, much of which is cached and configured to upload regularly, will allow you to drill into the conditions at previous times in a way which fully complements the monitoring sytem: what queries were running? Which were the longest, which used the most cpu or IO? What were the wait statistics at the time? What were the file wait statistics? Was this normal for this system, or abnormal? The default collections and built in reports let you drill through to find this information, as well as much more.

That’s really cool, but what’s won my heart for the long term is that you can easily set up custom data collections. When you do this, the data collector figures out the schema for the information you’re going to return, and automatically creates tables to record that data in the management data warehouse. You can configure your polling frequency and your retention period on the collection package, and whether or not the data should be cached. The rest is taken care of for you: no linked servers, no custom sql jobs, no additional security required. The data collector will automatically create and manage the agent jobs for you.

What wins me over here is a few things:

• A consistent, supported framework for collecting custom data
• The ability to combine multiple queries into a single custom collection
• The ability to individually configure and enable/disable individual collections
• Unlimited potential to define reports, using SQL Reports, PowerPivot, or embedded sharepoint widgets.

So to me, the Data Collector has to be the winner. It’s the tool that I can keep with me to get me out of difficult situations, and also it will stay up late with me and create big plans for the future.

In other words, the Data Collector is pure coffee. And coffee and DBAs will be together forever.

I will be blogging a series of posts on my adventures with the data collector. I will also be reading Bill Ramos' series on MDW Reports for the Data Collector– check out the latest one here.