SMO issues with SQL 2012 SP2

After rolling out SQL 2012 SP2 (CU1) across all our environments, we noticed all our TeamCity builds were going a lot slower, or never completing. I didn't make the connection at first, but I eventually tracked it down to a SMO call in our install script that was taking a long time to return (actually I think it either deadlocks or never returns on our busy build server).

Firing up profiler I was able to confirm that the SMO call in question (querying a database's DatabaseSnapshotBaseName property) was resulting in multiple SQL queries hitting the server, one of which in particular was fairly massive. We were doing this in an inner loop, so the performance was fairly rubbish - we had a classic ORM-lazy-load issue.

Clearly we should have looked at SetDefaultInitFields before, but the performance was always good enough we didn't have to bother. Which makes me suspect strongly that either the defaults have changed with SP2 (CU1), or the 'get all properties for the database' query has changed to include additional data (probably something cloud related), which reduces the performance substantially in the 'get everything' case.

What's a bit nasty here is that you also have this query executed if you reference the smo JobServer object, since (through ILSpy) one of the first things that class's constructor does is access the StringComparer property for the MSDB database object:

this.m_comparer = parentsrv.Databases["msdb"].StringComparer;

This object depends on a couple of server properties, but I think 'Collation' is the key one here - if that's not loaded then the the newer 'all properties' query kicks in, which is again much slower than it was before.

Moral: be sure to add 'Collation' to your SetDefaultInitFields() list for Databases.