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

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.


Comments (7)

  1. kelmy@capstonetechnology.com says:

    Did I miss something here? How about a clue where the original schema file can be found?

  2. Barclay Hill says:

    The location of master.dbschemas is coverred in the MSDN Article linked above. It is found on your box after installing GDR here: "%programfiles%Microsoft Visual Studio 9.0VSTSDBExtensionsSqlServer

    <SQL Version>DBSchemas"

  3. Santosht says:

    I don’t think we should customize the standard schema files.

    Here are the reasons:

    1) Its error prone. What if you make a mistake when you are tailoring the schema?

    2) What happens if you start using features that you don’t use today? Go and alter the schema file? In that case the code that compiles on the SQL server will not compile in VSTS, your project. Not cool.

    I would rather live with the extra few seconds and wait for the db pro team to fix the project load time.

    Not a best practice at all in my opinion.

  4. markanderson says:

    If you think about it, this is similar to includes… so yes you need to make sure to trim down and only utilize the components of the schema that you are working with.  The analysis of the schema file does indeed take time so it would be nice to make sure that we only include what is necessary.  If you include the whole thing that would be like including all items within the .net namespace in every class… kinda silly.

    Going one step further, it’s important to keep track of what is being referenced from a security perspective.  Keeping an eye on what is being used can go a long way to ensuring that the data is secure since you know what type of operations can be performed.  If you know what operations are allowed you can structure your application appropriately.

  5. Barclay Hill says:

    Exactly Mark! Good comments. Referencing dbschemas in database projects is an equivalent metaphor to referencing assemblies in the C#/VB.

  6. Santosht,

    I couldn't disagree more.

    1) If you make a mistake you'll know about it instantly because datadude will tell you its an invalid file. CTRL-Z saves the day.

    2) Rather than deleting stuff from the .dbschema file I have just commented it out. If you find yourself using an object that has been commented out open up the file, hit CTRL-F, find the object in question and uncomment it. Easy.

    Sure it would be nice if projects load quicker but even if the project loading could be "fixed" (your word) there is still a theoretical limit to the speed at which it can do something (the laws of physics see to that) so why not give it a helping hand by making it do less work?

    Your attitude puzzles me quite frankly. Still, each to their own.