SQL Server Database Engine based Reporting and Analytics

(If you don't want to read the blurb just download

The SQL Team has had a long flirtation with building a Data Warehouse/Analytics system on top of the system information that exists in the Database Engine. The first time I remember discussing this was just after SQL 2000 shipped, we had mapped out all the SQL Agent jobs that needed to run, and what data to collect. The very first version of the product plan for Yukon had a Operations Data Warehouse feature listed but it got dropped pretty quickly as the scope of Yukon became clear.

Part of the reason it was an expensive feature in early days was the lack of consistent info in the server, we were going to have to use Trace, DBCC, System Tables, System Views and a bunch of undocumented stuff to accomplish a meaningful solution. However once DMVs started appearing in the product(that was not their original name) lots of ideas starting floating around again.

In parallel one of the small (I think there were 3 people that worked on the project) central test teams came up with the idea for H2( there was a predecessor but I don't remember its name and it was not broadly released, just select customers and internally) and built it, shipped it internally and then out to the public. There is interesting data in H2 but its mostly config data.

Through the personal perseverance of a couple of folks (mostly Dan Winn and Paul Mestamaker, then an intern, but others as well) with support from Dave Campbell we got a bunch of reports added to what was then the Summary View in Management Studio.

The Summary reports are great but they are more operational than they are analytical, lots of members of the community have filled in the gap by providing richer reports/analysis either plugged into SSMS or as standalone tools.

But as an analytics geek I have always wanted something more. Well last week the folks from the customer advisory team shipped out DMVStats which is a reporting and analytics solution focused on DMV reported perf data. I can't think of anyone better than these guys to ship such a tool as I am sure its based on real world usage. You can get it here.

Hopefully there is more to come in this space.