An interesting annecdote
A while ago I blogged about using the dmvs to identify lookups and scans on tables http://sqlblogcasts.com/blogs/grumpyolddba/archive/2010/05/19/analysing-indexes-reducing-scans.aspx
Well continuing on from this I had discovered that two of the top scanning tables don't actually contain any data. After some investigations within the application database I discovered that one of these tables was designed to hold system lock out times; e.g. you populate the table with when the system will be unavailable and the application warns users of an impending outage. So far so good. Next a collaegue discovered that the data in this table was cached on the application tier so the level of access should be very low, we're actually looking at about 75,000 scans per day over the last 50 days, however what happens is that you cannot create an empty cache object so the cache keeps trying to populate, somewhere around once a second.
No worries then, we'll add old data, say a shutdown for last year, this will populate the cache object and the calls will be reduced. I raised a change request to test this and my colleague decided to test it on his own system first but despite his best efforts he couldn't produce a shutdown or warning message. After some debugging and searching through the application source code we discovered that the there was indeed a function calling the stored procedure and it was popualting the datareader, sadly the function was coded to return nothing, so here we had a piece of functionality which I was assured did do as it said on the tin, ( system shutdown ), but in reality was either never implemented or was disabled , we didn't examine every change to check but at least 2006 seemed the last time it might have worked if it every did.
Agreed there is not going to be any measurable change in application performance but there has to be some satisfaction in the outcome. The dmvs are a very powerful addition to SQL Server and can be used in many different ways to diagnose "things", yes they came in with SQL 2005 but the application in question only made the leap from 2000 to 2008 this year so this type of analysis was unavailable to me.