Background
I am a fledgling DBA who was hired in the middle of a migration from SQL Server 2005 x64 to 2008 R2 x64. We also migrated from a local server to a colo virtual server, and moved some of our databases over to a second virtual server, hoping to ease some of the load from the main SQL Server which hosts a Sage MAS 500 system.
Unfortunately, despite the virtual server having more resources than the old 2005 server, we have been experiencing performance problems, and I have been tasked with fixing these. I haven't made much progress, but today I noticed something strange:
Question
I've been checking dependencies for various tables and views, and noticed the time it takes to get results (especially when viewing objects which depend on the view or table in question) from 2008 R2 is much longer than it takes on the old 2005 server. Also, in some cases, different results are returned even though there has been no change in the tables/views since migrating. At the time I checked on the 2008 R2 server, there was virtually no activity so resources shouldn't have been a problem.
It seems to me this is symptomatic of our problems. Does anything come to mind why there would be such a drastic difference in View Dependency performance, or can anybody point me in a direction to investigate?
Best Answer
The 2008r2 "View Dependencies" functionality was completely rewritten from the 2005 version. This is because the 2005 version didn't work.
So it's not surprising that the results may be different (i.e., they should be correct now). It is a little unexpected that the 2008r2 version might be slower at it, but not entirely surprising, as it is after all, using a completely different approach.
So, my guess would be that this phenomenon has little or nothing to do with your other problems.