Share via

Right sizing the master.dbschema file for better design time performance

  • Article

In the March edition of MSDN we provided an overview of Server Projects and how to reference the master.dbschema file to resolve references to system objects.  If you have a project that references the master.dbschema file, you may have noticed it takes a while to establish this reference. You may have also noticed the project may not load or deploy as quickly as it did before referencing the master.dbschema file.  This is because when referencing the master.dbschema file you are adding all the system objects found in a typical SQL Server instance to your database model in Visual Studio. The number of system objects defined in the master.dbschema file greatly outnumbers the user created objects for many database projects.  If you have multiple projects referencing the master.dbschema file this can be compounded as each projects reference to the master.dbschema has its own copy of the database model in memory. 

Master dbschema files do not contain objects you will deploy.  Their primary purpose is to resolve references to system objects and system catalogs. The dbschema file is actually a serialized version of the master database schema model.  It is an xml file that can be customized if you like.  Suppose you have a project that contains just a few stored procedures that reference an extended stored procedure.  You could reference one of the static master.dbschema files provided with the project, but that is a fair amount of overhead for just one extended stored procedure. You could also just suppress the warning to ignore the unresolved reference to the extended stored procedure, but this might not be ideal as you plan to work with the extended stored  procedure in many of your stored procedures and want it to be resolved.  Alternatively, you can customize your master.dbschema file to have only the system objects that are approved for referencing by the database development team. 

To customize the master dbschema file you simply remove the elements you do not need or reference in your project. Let's assume that our project only references the extended stored procedures that deal with extended properties. These include fn_listextendedproperty, sp_addextendedproperty, sp_dropextendedproperty and sp_updateextendedproperty.  You want to customize the master.dbschema file to include only these objects.

To accomplish this you:

  1. Make a copy of the master.dbschema. Be sure to copy the right version to match the version of SQL Server you are targeting in your project.
  2. Open the master.dbschema file with Visual Studio. Rename it to master.dbschema.txt before you edit it so VS will not parse the whole xml. This will speed up the edit process greatly.
  3. Copy the elements of each of the objects you want to keep on to the clipboard or another file.
  4. Remove all the elements within the model element.
  5. Add the selected elements back to your master.dbschema within the model element.
  6. Save the file. Name the file something other than master.dbschema so your team can easily recognize that it is the customized file and not the original.
  7. Add the customized file to SCC so the rest of the team can reference the same file. A solution folder is a good way to go about this.
  8. Remove the reference from your project(s) to the original master.dbschema.
  9. Add a new reference to your project for your customized master.dbschema.

Establishing the reference to your customized master.dbschema now only takes seconds. Loading your project should also be a little quicker now that the full model of the master.dbschema is not loaded each time for each referencing database project. In this example we have taken the master.dbschema file down from 8 MBs to 20k. This is roughly 2000 database objects down to the 4 we selected for our example.

Customized master.dbschema file in VS Editor 

The customized master.dbschema from the example is attached.

onlyextprops.master.dbschema

Comments

  • Anonymous
    March 24, 2009
    PingBack from http://magazines.linkablez.info/2009/03/22/right-sizing-the-masterdbschema-file-for-better-design-time-performance/
  • Anonymous
    March 26, 2009
    Did I miss something here? How about a clue where the original schema file can be found?
  • Anonymous
    March 26, 2009
    The comment has been removed
  • Anonymous
    May 28, 2009
    The comment has been removed
  • Anonymous
    July 15, 2009
    The comment has been removed
  • Anonymous
    July 15, 2009
    Exactly Mark! Good comments. Referencing dbschemas in database projects is an equivalent metaphor to referencing assemblies in the C#/VB.
  • Anonymous
    September 15, 2010
    The comment has been removed