4

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?

Improve this question
2
  • 1
    Are you running statistics and index maintenance on your databases?
    – Mike Fal
    Feb 26, 2014 at 15:46
  • Mike, we have a nightly optimization job that rebuilds indexes, reorganizes the indexes, and then updates the statistics (not sure if that is overkill or not)
    – Marco
    Feb 28, 2014 at 16:43

1 Answer 1

Reset to default
5

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.

Improve this answer
1
  • 2
    Thanks, that makes sense. In hindsight, upgrading SQL Server to 2008 R2, switching to a virtual server and moving to a datacenter all in one go probably wasn't the best idea, but unfortunately I wasn't in on the planning - I just get to deal with the fallout. Ah well, I'm going to treat it as a learning experience. I've already learned more about wait states in the last two days than I had ever hoped to.
    – Marco
    Feb 28, 2014 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.