locked
Database Professional GDR 2 Too Slow / Suppress Warning to Blame?

    Question

  • The purpose of this post is to provide the steps necessary to show that the “Suppress Warnings” functionality doesn’t always work.  In addition, this post will show that the Suppress Warning bug can result in a failure to cache the .dbmdl file.  Failure to cache the file results in absurdly slow loads and builds. 

     

    Prerequisites / Assumptions

    ===================

     

    1.         Install the Northwind database.  It will be used to recreate the issue. 

     

    2.         I’m running the following software locally: 

    a.         Windows XP Pro SP2

    b.         VS 2008 SP1 Database Pro GDR 2

    c.         Filemon

    d.         SQL Server 2008 (10.0.1600)

     

    3.         Given the fragile nature of the .dbmdl file - as is documented at http://blogs.msdn.com/bahill/archive/2009/02/24/deserialization-has-failed-for-project.aspx, http://www.teamfuze.net/thread.jspa?threadID=9794, and http://support.microsoft.com/kb/970563/ - the following precautions should be taken to prevent unwanted interference with the .dbmdl file during the steps to reproduct the error: 

     

    a.         Create a folder that will contain the database pro project (c:\Northwind1).  Ensure it is NOT indexed for Fast Searches. 

    b.         During the reproduction steps, do not save any files unless instructed to do so in the following steps. 

    c.         When it is necessary to open a Windows Explorer window, close it ASAP. 

    d.         Do not run Perform, Filemon, antivirus software, desktop search software (ie Copernic), or any other application that might read the .dbmdl file.  The only exception is Filemon as is described in the Reproduction Steps. 

     

     

    Reproduction Steps

    ==============

    1.         Ensure C:\Northwind1 is empty.

     

    2.         Launch VS. 

     

    3.         File => New => Project => Database Projects => SQL Server 2008 (or 2000/2005 depending on where you installed Northwind) => SQL Server 2008 Wizard

     

                Name = Northwind1

                Location = C:\

                Solution = Create New Solution

                Create directory for Solution = unchecked

                Add to Source Control = unchecked

     

                Click OK

               

    4.         Welcome Screen

     

                Click Next

     

    5.         Project Properties

     

    Click Next

     

    6.         Set Database Options

     

                Click Next

     

    7.         Import Database Schema

     

                Import Existing Schema = checked

               

                Set the Connection string to wherever you installed the Northwind database.  Supply appropriate credentials. 

     

                Click Next

     

    8.         Configure Build and Deploy

     

                Click Finish.

     

    9.         NOTE that you should NOT build the project until step #18. 

     

    10.       Add the following to the bottom of the CustOrderHist stored procedure.

     

    --Purpose of this code is to generate a build warning. 

                            select * From ##GenerateWarning

     

                Close the tab to save the proc change.

     

    11.       Add the following to the top of the CustOrdersDetail stored procedure.

     

    --The purpose of this code is to generate a compile error.  

    asdaslkdjfasdkjfalksdfalksdjkajsd

     

                Close the tab to save the proc change.

     

    12.       Solution Explorer => right click the Northwind1 database project (not the solution) => Properties => Build => Suppress Warnings => 4151.

     

                Close the tab to save the setting. 

     

    13.       Exit VS.  If asked to save anything, choose “Yes”.

     

    14.       It is a Microsoft best practice that each developer has their own version of the .dbmdl file.  It is also a Microsoft best practice that the .dbmdl file should not be checked into TFS. 

     

    The purpose of this step is to make the c:\northwind1 folder appear as if a newly-hired employee just retrieved the project from TFS source control. 

     

                delete the C:\Northwind1\Northwind1.dbmdl file.  

     

                If you opened an Explorer window to delete the file, close it. 

     

    15.       Launch VS.

     

    16.       File => Recent Projects => c:\northwind1\northwind1.sln.

     

    17.       a.         Verify that the C:\Northwind1\Northwind1.dbmdl has been recreated.

     

    18.       a.         Solution Explorer => right click the Northwind1 database project => Build. 

     

                            Although it isn’t obvious here – because Northwind is such a small database – this build causes the whole project to be re-analyzed (as was done in steps #16 and #17).  With a large project, the load could take an hour and then the build could take another hour. 

     

    19.       Switch to the Error tab.  This is the first bug.  The warning appeared even though it should have been suppressed via step #12. 

     

    20.       Exit VS. 

     

    21.       Note that the c:\northwind1\northwind1.dbmdl file that was created in Step #16 was automatically deleted. 

     

    a.         The purpose of the .dbmdl file is to cache data to reduce load and build times. 

     

    b.         If the file is deleted each time the project is unloaded (step #20) and each time the project is built (step #18), the file is NEVER used to provide caching. 

     

    c.         If the caching file never provides caching, Database Pro should not be used for Enterprise projects.  For the purposes of this post, I’m defining an Enterprise project as one having hundreds to thousands to hundreds-of-thousands of SQL artifacts. 

     

    i.          The creation of a dbmdl file is very, very slow.  I’ve seen database projects take hours to load and hours to build.  Our developers cannot be sitting on their hands for this amount of time.  Or – even worse – what if we implemented Database Pro and then needed an emergency build in the middle of our batch cycle? 

     

    ii.          If the warning suppression doesn’t work (step #19) and the project generates a lot of warnings (my project with only 350 procs generates 13K+ warnings) the use of DB Pro creates a catch-22 situation;  my project loads/builds so slowly because of the warnings and I can’t solve the warnings because my project loads/builds so slowly.  As a result, some shops will find DB Pro use impractical. 

     

    22.       Return to step #15 and repeat thru step 22 as many times as you desire.  A vicious circle occurs. 

     

     

    Work-Around

    ==========

    Add the following steps:

     

    17b.     Solution Explorer => right click the Northwind1 database project (not the solution) => Properties => Build => Suppress Warnings => Make it blank.

     

    18b.     Solution Explorer => right click the Northwind1 database project (not the solution) => Properties => Build => Suppress Warnings => 4151. 

     

    18c.     Solution Explorer => right click the Northwind1 database project => Build. 

     

    These steps fix the Suppression issue and prevent the .dbmdl from being deleted/recreated so much. 

     

               

    Questions

    =======

     

    1.         Is this a known issue?  If so, where is it documented? 

     

    2.         Are there other conditions that can result in this issue?

     

    3.         Are there other work-arounds? 

     

    4.         Does VS 2010 Beta 2 solve these issues? 

     

    5.         When will a fix be available?

     

     

    Friday, January 08, 2010 3:56 AM

Answers

  • Wow. Great bug report! It was obviously a lot of work coming up with the repro' steps.

    1.         Is this a known issue?  If so, where is it documented? 


    Yes. The issue about not handling warning suppression is a known bug in the GDR release. I don't remember any bugs about not persisting the .dbmdl file when you unload the project. There are corner cases where we simply cannot save the .dbmdl file (disk space, etc) but that's not what's happening here. We don't have a list of al known bugs published anywhere.
     

    2.         Are there other conditions that can result in this issue?

    I don't know.

     

    3.         Are there other work-arounds? 


    The work around for both issues is to click the Reanalyze Project button in Solution Explorer
     

    4.         Does VS 2010 Beta 2 solve these issues? 


    I didn't try the Beta 2 build. I tried the latest bits (soon to be released as a Release Candidate). Yes. Both issues are fixed.

    5.         When will a fix be available?

    Visual Studio 2010.

    It's also worth mentioning that the performance of Data Dude is significantly better in VS 2010.


    Duke Kamstra - Program Manager - VSTS Database Edition (Data Dude, DBPro)
    Friday, January 08, 2010 4:53 PM

All replies

  • Wow. Great bug report! It was obviously a lot of work coming up with the repro' steps.

    1.         Is this a known issue?  If so, where is it documented? 


    Yes. The issue about not handling warning suppression is a known bug in the GDR release. I don't remember any bugs about not persisting the .dbmdl file when you unload the project. There are corner cases where we simply cannot save the .dbmdl file (disk space, etc) but that's not what's happening here. We don't have a list of al known bugs published anywhere.
     

    2.         Are there other conditions that can result in this issue?

    I don't know.

     

    3.         Are there other work-arounds? 


    The work around for both issues is to click the Reanalyze Project button in Solution Explorer
     

    4.         Does VS 2010 Beta 2 solve these issues? 


    I didn't try the Beta 2 build. I tried the latest bits (soon to be released as a Release Candidate). Yes. Both issues are fixed.

    5.         When will a fix be available?

    Visual Studio 2010.

    It's also worth mentioning that the performance of Data Dude is significantly better in VS 2010.


    Duke Kamstra - Program Manager - VSTS Database Edition (Data Dude, DBPro)
    Friday, January 08, 2010 4:53 PM
  • Hi Dave,

    Does Duke answer your questions? Please let us know if you still need helps on this issue. Thanks.


    Hongye Sun [MSFT]
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg @ microsoft.com

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    See what's happening in MSDN forum? Follow us at Twitter.
    Tuesday, January 12, 2010 7:50 AM