Configuring heterogenous data sources with SQL Server 2008 R2

Meditation:
The most common cause of problems is ignorance.


Story:


I've been swept up into the SQL 2008 R2 media blitz.   Soon, my end users will be wearing sharp outfits and assembling their own reporting via 'self-serve' business intelligence.   What a happy day that will be.   IT will finally be out of the BI loop, customers will be free to explore the fertile fields of data without a guide, and all will be well.


Central to this is moving towards modeled data.   At present, we use far too many ad-hoc queries against undocumented sources.    Most certainly a centralized, shareable model is the way to go.


This cryptic blog post from 2006 started me thinking about simplifying heterogenous data access- one of our most common, and difficult problems for our relational data.  


So, I fired up BIDS, cracked open the report modeling projects, and proceeded to spend ten hours in a frustrating battle of wits:  Adding, removing objects to the data source view layer.   Changing objects from tables to Named Queries.   Nothing would build to a successful, deployable project.


The ultimate resolution came only after two days of frustration and despair:

As you can see in the properties window- heterogenous data sources must be fully qualified.   By implication, linked servers must be used for off-server datasources.

If you can find this spelled out anywhere in BOL, or any of the MSDN blog posts about R2, I salute your keen observation and attention to detail.    I failed to find this information during the preparation phase of this experiment.

The only cure for my ignorance was proceeding forth stupidly until project building was blocked by BIDS.  

So, that it may be useful:   To build a data source view under 2008 R2- with the intent of deploying a report model to an R2 SSRS instance- you'll need to use named queries (not secondary datasources!) for the non-primary datasets.   Those will need to be fully qualified, and the linked servers required will need to be in place.

I'm grateful this was merely and R&D project and not- for example- a production outage where ignorance can be catastrophic.

I'd done, what I thought was adequate preparation.   Reality proved me wrong.   The correct procedure was discovered only after hours of  flopping about.   

I'm somewhat impressed I managed to power through my own ignorance, and that it only took two days.   I might have spent either 30 seconds or a month on this earlier in my career.   It took just the right amount of time.

There is a cause.

Meditation:


One of the luxuries of our practice in IT is a rational, empirical framework.   There is always a root cause.


Story:


You are wearing your lucky socks.  You have been issued your security badge.   The HR paperwork is complete.


The friendly smiles from the interview process are strained, this Monday morning.   There have been 37 application timeouts since 6 AM.   Cash posting is delayed.  


The department has been informed, that you, the new DBA are the miracle cure for all existing application problems.


Within five minutes of sitting down at your new desk, you get a call from the VP of Finance.   They really need to figure out what's going on- and get it fixed- immediately if possible.   It's costing hours per day.   The folks in Order Entry have come to hate the slow, malperforming system.  


You get another call.    There's been another five timeouts in the last few minutes.  Things are getting worse.


You pull up the familiar outlines of SQL Management Studio.


Nothing.


Your phone rings again.  The comptroller is getting security warnings trying to run a critical report.  


Right-click connect.   At this point, you know none of the architectural internals of any applications.   In fact, all you know are server names.   No one knows which databases are exactly where, or even what is being hosted per database.    The world is unknown, sea monsters lurk:

The room is very, very small, and very very hot.   It takes 30 seconds to connect to the primary server and login.  


Nothing is documented anywhere.


What now?


Thus began my first pure DBA assignment.   I'd been working as a DBA for about ten years, but always as a member of a team, or in a secondary role.   This was my first morning of total ownership.  


I could feel the dissatisfaction growing with every passing moment.    50 time outs.   The cash posting group was wondering if maybe they should go home.


This particular gig hadn't had a DBA onsite for several years.  


Breathe.


At the root, our practice is about getting data to and from disk, then into memory, and into the CPU for processing.   All problems stem from blocked flow in one of these areas.   This is always true.


Breathe.


SSMS showed no blocked sessions.   Perfmon showed some disk activity- but only moderate memory use.   The CPU was pegged at 95 %.


An hour has passed;  Nothing is better.  You are sweating.

An investigation of wait state DMV's shows hundreds of CX_PACKET waits.   You think you read a paper about that once.


Can't that be caused by intraquery parallelism gone bad?  Poor indexing?


You right click on 'Reports->Index Physical Statistics.'


You stifle a scream.   Everything is fragmented to 99 %.


A few emergency rebuilds later, concentrating on the indexes with the highest utilization, and the timeouts start dying off.


By lunchtime, there hasn't been a timeout in an hour.


'Thank god you're here,' a colleague says.


It took three months to solve in its entirety.    The root cause was a series of bad/missing indexes coupled with poor query design.    This was leading to excessive CPU utilization due to bad query plans, under unconstrained parallelization.


The fix was some new indexes- but most importantly- weekly maintenance to make sure that indexes were unfragmented and statistics were correct.


Which I guess yields the takehome:  begin at the beginning.

Beginning with open hands.

This project is to augment my daily working notes.    

I am not selling anything.   I have no secret TSQL scripts that will improve performance on your server by 400 %.   I am not looking for consulting opportunities.    I am not grinding any axes about the death of relational databases.

My target audience is my fellow DBA's, primarily.   My secondary audience is my friends, and if I imagine a tertiary audience, it's folks with way too much time on their hands.

My intent is for it to be very much like a lab notebook or research journal.    Possibly augmented with recipes and book reviews and other narcissistic debris.


If it is helpful to anyone, then I have succeeded.

Comments and feedback are very welcome.   Thank  you for your attention.