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.

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.