Jon Sayce wrote an article in 2008 on how to improve SMO performance using the SetDefaultInitFields method on the Server object to pull back more than one property at a time; Diana Moldovan explored it a little further here. Unfortunately this setting can trigger a bug that cost me a few hours today.
The easiest way to replicate it is by enabling all the properties for all objects at once using the method that takes a simple boolean to enable it through the entire SMO structure. Lets have a quick peek at how some objects and properties would normally look.
Import-Module SQLPS -DisableNameChecking
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server ".\SQL2014"
$smo.Databases["master"].FileGroups | Select *
$smo.Databases["master"].FileGroups[0].Files | Select *
Parent : [master] FileGroupType : RowsFileGroup ID : 1 IsDefault : True IsFileStream : False ReadOnly : False Size : 503872 Files : {master} Name : PRIMARY Urn : Server[@Name='VM-WIN81\SQL2014']/Database[@Name='master']/FileGroup[@Name='PRIMARY'] Properties : {Name=ID/Type=System.Int32/Writable=False/Value=1, Name=IsDefault/Type=System.Boolean/Writable=True/Value=True, Name=ReadOnly/Type=System.Boolean/Writable=True/Value=False, Name=Size/Type=System.Double/Writable=False/Value=503872...} UserData : State : Existing IsDesignMode : False Parent : [PRIMARY] AvailableSpace : 52160 BytesReadFromDisk : 4120576 BytesWrittenToDisk : 0 FileName : C:\Database\MSSQL12.SQL2014\MSSQL\DATA\master.mdf Growth : 10 GrowthType : Percent ID : 1 IsOffline : False IsPrimaryFile : True IsReadOnly : False IsReadOnlyMedia : False IsSparse : False MaxSize : -1 NumberOfDiskReads : 64 NumberOfDiskWrites : 0 Size : 503872 UsedSpace : 451712 VolumeFreeSpace : 55902428 Name : master Urn : Server[@Name='VM-WIN81\SQL2014']/Database[@Name='master']/FileGroup[@Name='PRIMARY']/File[@Name='master'] Properties : {Name=AvailableSpace/Type=System.Double/Writable=False/Value=52160, Name=FileName/Type=System.String/Writable=True/Value=C:\Database\MSSQL12.SQL2014\MSSQL\DATA\master.mdf, Name=Growth/Type=System.Double/Writable=True/Value=10, Name=GrowthType/Type=Microsoft.SqlServer.Management.Smo.FileGrowthType/Writable=True/Value=Percent...} UserData : State : Existing IsDesignMode : False
And now we'll do the same but enable that flag instead.
# Bring back all of the properties as efficiently as possible
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server ".\SQL2014"
$smo.SetDefaultInitFields($true)
$smo.Databases["master"].FileGroups | Select *
$smo.Databases["master"].FileGroups[0].Files | Select *
Parent : [master]
FileGroupType : RowsFileGroup
ID : 1
IsDefault : True
IsFileStream : False
ReadOnly : False
Size : 503872
Files :
Name : PRIMARY
Urn : Server[@Name='VM-WIN81\SQL2014']/Database[@Name='master']/FileGroup[@Name='PRIMARY']
Properties : {Name=ID/Type=System.Int32/Writable=False/Value=1, Name=IsDefault/Type=System.Boolean/Writable=True/Value=True, Name=ReadOnly/Type=System.Boolean/Writable=True/Value=False,
Name=Size/Type=System.Double/Writable=False/Value=503872...}
UserData :
State : Existing
IsDesignMode : False
The following exception occurred while trying to enumerate the collection: "A column named 'DatabaseName2' already belongs to this DataTable.".
At line:6 char:1
+ $smo.Databases["master"].FileGroups[0].Files | Select *
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException
+ FullyQualifiedErrorId : ExceptionInGetEnumerator
It sure was fast because it threw an obscure exception. Now if you Google this error you'll come across a couple references going back 11 years but not much insight into what caused it.
- SMO FileGroup.Files produces DuplicateNameException (from 2015)
- SQL Server: Tuning your SMO Application for great performance – PART 2 (in the comments section from 2005!)
Let's investigate further; by rebuilding the object, finding what data type the property in question is, and then seeing what default properties get set.
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server ".\SQL2014"
$type = $smo.Databases["master"].FileGroups[0].Files[0].GetType()
Write-Host "Default Fields"
$smo.GetDefaultInitFields($type)
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server ".\SQL2014"
$smo.SetDefaultInitFields($true)
Write-Host "All Fields"
$smo.GetDefaultInitFields($type)
Default Fields All Fields AvailableSpace FileName Growth GrowthType ID IsPrimaryFile MaxSize Size UsedSpace BytesReadFromDisk BytesWrittenToDisk IsOffline IsReadOnly IsReadOnlyMedia IsSparse NumberOfDiskReads NumberOfDiskWrites PolicyHealthState VolumeFreeSpace
It turns out has no default properties set, and the exception seems to occur after you try to set some. Interestingly though the way that boolean flag works is to set default properties on each SMO type; which means the solution is to turn it on for everything and then only disable it for this specific type; retaining any of the performance improvements throughout the rest of SMO.
# Bring back all of the properties as efficiently as possible
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server ".\SQL2014"
$smo.SetDefaultInitFields($true)
$smo.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.DataFile], $false)
$smo.Databases["master"].FileGroups | Select *
$smo.Databases["master"].FileGroups[0].Files | Select *
I've enumerated the entire SMO structure over hundreds of servers and this is the only object that appears to require this workaround, and so if you're using the global flag like I do them you should use them in conjunction.
I've tested this on up to SQL Server 2016 CTP 3.2 and whatever the underlying bug is it still exists in the current version.