GDR R2 Performance on large schema

Question

We have a large legacy schema that we want to make more manageable through the use of the GDR R2.  We have, roughly:

800 tables
4000 sprocs
655 foreign keys

Not surprisingly, we are having some performance issues.  We have not yet referenced master.dbschema, although there are warnings for references to it, because I figure that will make performance even worse.

A rebuild takes 3 or 4 minutes.  Sometimes, right-click on the DB project takes 7 seconds to get a context menu to appear.

We have a DB Server project which defines 2 logins.  The rest is contained in a single DB project that references the server project.

Is there any tweaks that can be done to improve performance?

Answer 1

Hi,

We are experiencing the same issue with our database (~900 tables, ~4'000 procedures, but DRI done by triggers).

Before the GDR edition, we were not able to work with the project since dependencies resolution was not done in the background => we had to wait till it's done (about 30 minutes to 1 hour) before being able to use the project !

VSTSDB use a high amount or memory, so giving it as much RAM as you can will improve performance.

Besides, we could have subdivided our project into subprojects but we did not want to add more references between projects as we had already a lot of them, some of them being circular.

Still, building the whole project takes us about 15 minutes on a dual core desktop with 4 Go of RAM.

Answer 2

Thanks for your reply.  Nice to know we are not alone.  I am running a core 2 duo @ 2.66GHz with 4GB RAM too on Vista Business x64.

I am going to have to decide soon if we can live with this or not.

Answer 3

Can you informe me about the result of your investigation, that is whether or not you decide to keep the solution and why.

Thanks.

Answer 4

Here are a few recommendations:
1. Run an x64 OS - It gives us more RAM.
2. Break the database up into seperate projects and use project references. Each project gets its own model so model resolution is quicker for each project. UI responsiveness in Solution Explorer is quicker also.
3. You can minimize the performance effects referencing master.dbschema by following Barclay's blog: http://blogs.msdn.com/bahill/archive/2009/03/23/right-sizing-the-master-dbschema-file-for-better-design-time-performance.aspx
4. Use Visual Studio 2010. We improved performance a LOT! Beta 2 is available for download: http://www.microsoft.com/visualstudio/en-us/products/2010/default.mspx?CR_CC=100253207&WT.mc_id=SEARCH&WT.srch=1&CR_SCC=100253207

---

Duke Kamstra - Program Manager - VSTS Database Edition (Data Dude, DBPro)

Answer 5

I think the deployment times are what's going to kill you. Say you had a few schema changes to make and you wanted to run them into your local database to test before checking them in. If you follow "best practices" you need to deploy the project to your local database (rather than running them in directly, by hand). That, by far, will take more time than opening and building the project.

Other things that burned us included:
1. Performance of server explorer.
2. Adding columns, not nulls (with defaults) to an existing table doesn't work. Basically you have to either forget about mainatining any kind of column order to your tables and add new columns to the end of the table or write out-of-bad scripts that perform the data migration for you (like SSMS can do)
3. Lack of native support for nocheck constraints (must be added in post-deployment script).
4. Lack of support for a SQL 2000 database and server project on a SQL 2008 server (the server project is treated as a 2008 project)

That's just off the top of my head.

I wish you luck.

Answer 6

Duke, thanks for your response.

What scares me about the multiple project idea is the task of actually breaking apart this huge legacy schema.  Do you have any tips for proceeding on that front?

Ken, you are right about the build performance for sure.  Because of it, I was thinking that devs would have to follow a cycle something like this:

- manually make changes to local dev database during development
- when when development is finished and ready for check-in, then do a schema compare and merge manual changes back into the DB project
- once changes are merged into DB project, build and deploy the DB to local (hopefully this would only be done once a day maybe?)
- test changes locally again to ensure things still work after build and deploy of DB
- check in the changes

The build/deploy performance will be an issue for iterative development, but I am trying to weigh the downsides of that with the upside of having a DB project and the advantages that come with it.

Answer 7

We're also struggling with the development and build performance of the database edition.  Of Duke's 4 suggestions, 1 and 4 are not possible for us, leaving options 2 & 3.

For option 2, is there an easy way to determine what objects are referenced in the master schema from a given database project?  This would make it easier to cut the master.dbschema file down to size.

For option 3, I'm with TFarrell, some guidance on breaking our big database project into separate projects would be very helpful.