none
Project Model Validation Takes ~10 minutes RRS feed

  • Question

  • Hi All,

    We have a DB Solution that contains several SSDT DB Projects. One in particular has DB References to several of the others, and itself contains a large number of objects (~1k tables, many of which have at least four associated SPs).

    Whenever I attempt to publish this DB, it takes at least 10 minutes to validate the project model - even before I get the Publish Dialog on screen.

    As you can imagine, this makes the project essentially unusable - particuarly if the publish fails and you need to restart.

    Is there any way to bring this time down to an acceptable level?

    Thanks

    Fergal


    Friday, May 3, 2013 10:20 AM

Answers

  • The time required to resolve the model is a function of the number of objects, but more importantly the result of the number of warnings and errors in the model.

    If you have unresolved 3 and 4-part references, resolution can take a long time.

    Especially when you have a circular reference between two or more databases, it will take a very long time as it will try to re-evaluate which is impossible due to the circular reference as it needs to fully resolve one database before it moves to the next.

    Also make sure to turn off Extended TSQL validation when you are not using a pure SQL Azure or SQL Server 2012 contained database model, for all other scenarios it needs to be turned off.


    -GertD @ www.sqlproj.com


    Wednesday, May 29, 2013 5:46 AM
    Moderator

All replies

  • Anyone? I tried deleting the .dbmdl model file in the vague hope that that might have some sort of impact. No dice though.

    As it stands, the amount of time it takes to build/analyse the DB project for a publish just makes it virtually unuseable!

    Thursday, May 23, 2013 7:58 AM
  • Just curious, did you update to the latest SSDT and DACFx?  Before I updated, it literally wouldn't resolve mine at all.. just hung.  After I updated (I believe May 2013 for DACFx and Dec 2012 for SSDT) it resolves in just a couple of minutes now..

    David

    Friday, May 24, 2013 12:22 AM
  • Hi DBARider,

    I had the latest SSDT, but not DacFx. I've updated just now, and attempting to call a Publish on the DB in question. It's been validating the project model for the last 10 minutes so far, so it doesn't appear to have made much difference.

    Friday, May 24, 2013 8:08 AM
  • Interesting fact that may or may not have an impact:

    I have an msbuild script that deploys my projects. It works fine for smaller DBs on my machine, but when I run it against another developer machine, it throws an error complaining about:

    MSB4019: The imported project "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found.

    On my machine, this exists (hence no error). But both he and I have SSDT directories on the V11.0 path. It almost looks like these projects are pointing at the VS2010 files. Do I need to do someting to change it?

    Friday, May 24, 2013 3:02 PM
  • I ran into this issue as well.  Turned out the reference existed in the dbproj file.  Look for the line:

      <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets" />

    What I had to do was to ensure my build process was not utilizing the old dbproj file but rather using the migrated sqlproj file.

    As far as you publish taking long.. what do you see in the output table for Build?  Is it still resolving??


    David

    Friday, May 24, 2013 6:54 PM
  • Thanks. I managed to edit the proj file - but I believe these are new projects - so only contain the sqlproj file. I'll check again on Monday though.

    The output hangs on "Verifying project model" (or similar, I'm home now so don't have the exact phrase in front of me - you know the bit I mean though).

    That said, we did find (apparently) that taking out the warnings on case seemed to cut the build time down by what im told is a significant amount (again, can't know myself until I'm back in the office). But we have a heap more warnings in the project. So perhaps fixing all those will substantially reduce out build times.

    Friday, May 24, 2013 7:44 PM
  • The time required to resolve the model is a function of the number of objects, but more importantly the result of the number of warnings and errors in the model.

    If you have unresolved 3 and 4-part references, resolution can take a long time.

    Especially when you have a circular reference between two or more databases, it will take a very long time as it will try to re-evaluate which is impossible due to the circular reference as it needs to fully resolve one database before it moves to the next.

    Also make sure to turn off Extended TSQL validation when you are not using a pure SQL Azure or SQL Server 2012 contained database model, for all other scenarios it needs to be turned off.


    -GertD @ www.sqlproj.com


    Wednesday, May 29, 2013 5:46 AM
    Moderator
  • I m still facing this issue though it is mentioned as resolved in this connect bug:

    https://connect.microsoft.com/SQLServer/feedback/details/788553/ssdt-project-validating-the-project-model-takes-10-minutes-for-large-dbs

    I m having 2000~ objects in my db project; version: SQL Server Data Tools   11.1.40706.0

    I face the same poor performance with MSBuld.exe commandline too.

    Is there a way to reopen the connect bug?


    Mahesh


    Sunday, November 2, 2014 2:57 PM
  • I've also gotten an increase in performance when setting the TEMP/TMP variables to a RAM drive. Watch performance monitor access to the hard disk.
    Thursday, November 6, 2014 3:17 PM
  • I got this resolved by add /p:CmdLineInMemoryStorage=TRUE. Thanks to stackoverflow user Mark Neves!

    Thursday, May 24, 2018 7:15 PM