升级SMO至171版本后，MS SQL Server大型数据库脚本生成变慢

我们运维的数据库包含大量架构与表，使用SMO（Microsoft.SqlServer.SqlManagementObjects）为单个架构生成对象创建脚本。例如某数据库有1200个架构，每个架构包含75个对象：

• 使用SMO 150.18208.0版本时，本地SQL Server生成单个架构脚本耗时约12秒；
• 升级至最新171.30.0版本后，耗时增至2-3分钟。

以下是创建Scripter的代码：

var scripter = new Scripter(sourceDb.SmoServer)
{
    Options =
    {
        DriAll = true,
        Indexes = true,
        ClusteredIndexes = true,
        NonClusteredIndexes = true,
        DriIndexes = true,
        DriClustered = true,
        DriNonClustered = true,
        FullTextIndexes = false,
        Triggers = true,
        Default = true,
        ScriptDataCompression = false,
        AllowSystemObjects = false,
        AgentJobId = false,
        ScriptXmlCompression = false
    }
};

生成脚本的代码（allSchemaObjects包含单个架构的全部75个对象）：

scripts.AddRange(scripter.EnumScript(allSchemaObjects));

跟踪发现，每个对象都会执行一条耗时约1.7秒的查询语句：

exec sp_executesql N'SELECT
i.name AS [Name],
CAST(ISNULL(si.bounding_box_xmax,0) AS float(53)) AS [BoundingBoxXMax],
CAST(ISNULL(si.bounding_box_xmin,0) AS float(53)) AS [BoundingBoxXMin],
CAST(ISNULL(si.bounding_box_ymax,0) AS float(53)) AS [BoundingBoxYMax],
CAST(ISNULL(si.bounding_box_ymin,0) AS float(53)) AS [BoundingBoxYMin],
CAST(case when (i.type=7) then hi.bucket_count else 0 end AS int) AS [BucketCount],
CAST(ISNULL(si.cells_per_object,0) AS int) AS [CellsPerObject],
CAST(i.compression_delay AS int) AS [CompressionDelay],
~i.allow_page_locks AS [DisallowPageLocks],
~i.allow_row_locks AS [DisallowRowLocks],

        CASE WHEN ((SELECT tbli.is_memory_optimized FROM sys.tables tbli WHERE tbli.object_id = i.object_id)=1 or
        (SELECT tti.is_memory_optimized FROM sys.table_types tti WHERE tti.type_table_object_id = i.object_id)=1)
        THEN ISNULL((SELECT ds.name FROM sys.data_spaces AS ds WHERE ds.type=''FX''), N'''')
        ELSE CASE WHEN ''FG''=dsi.type THEN dsi.name ELSE N'''' END
        END
       AS [FileGroup],
CASE WHEN ''FD''=dstbl.type THEN dstbl.name ELSE N'''' END AS [FileStreamFileGroup],
CASE WHEN ''PS''=dstbl.type THEN dstbl.name ELSE N'''' END AS [FileStreamPartitionScheme],
i.fill_factor AS [FillFactor],
ISNULL(i.filter_definition, N'''') AS [FilterDefinition],
i.ignore_dup_key AS [IgnoreDuplicateKeys],

        ISNULL(indexedpaths.name, N'''')
       AS [IndexedXmlPathName],
i.is_primary_key + 2*i.is_unique_constraint AS [IndexKeyType],
CAST(
          CASE i.type WHEN 1 THEN 0 WHEN 4 THEN 4
                      WHEN 3 THEN CASE xi.xml_index_type WHEN 0 THEN 2 WHEN 1 THEN 3 WHEN 2 THEN 7 WHEN 3 THEN 8 END
                      WHEN 4 THEN 4 WHEN 6 THEN 5 WHEN 7 THEN 6 WHEN 5 THEN 9 ELSE 1 END
        AS tinyint) AS [IndexType],
CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered],
i.is_disabled AS [IsDisabled],
CAST(CASE WHEN filetableobj.object_id IS NULL THEN 0 ELSE 1 END AS bit) AS [IsFileTableDefined],
CAST(ISNULL(k.is_system_named, 0) AS bit) AS [IsSystemNamed],
CAST(OBJECTPROPERTY(i.object_id,N''IsMSShipped'') AS bit) AS [IsSystemObject],
i.is_unique AS [IsUnique],
CAST(ISNULL(si.level_1_grid,0) AS smallint) AS [Level1Grid],
CAST(ISNULL(si.level_2_grid,0) AS smallint) AS [Level2Grid],
CAST(ISNULL(si.level_3_grid,0) AS smallint) AS [Level3Grid],
CAST(ISNULL(si.level_4_grid,0) AS smallint) AS [Level4Grid],
ISNULL(s.no_recompute,0) AS [NoAutomaticRecomputation],
CAST(ISNULL(INDEXPROPERTY(i.object_id, i.name, N''IsPadIndex''), 0) AS bit) AS [PadIndex],
ISNULL(xi2.name, N'''') AS [ParentXmlIndex],
CASE WHEN ''PS''=dsi.type THEN dsi.name ELSE N'''' END AS [PartitionScheme],
case UPPER(ISNULL(xi.secondary_type,'''')) when ''P'' then 1 when ''V'' then 2 when ''R'' then 3 else 0 end AS [SecondaryXmlIndexType],
CAST(ISNULL(spi.spatial_index_type,0) AS tinyint) AS [SpatialIndexType],
CAST(ISNULL(INDEXPROPERTY(i.object_id, i.name, N''IsOptimizedForSequentialKey''), 0) AS bit) AS [IsOptimizedForSequentialKey],
CAST(
        case when ((SELECT  MAX(case when xml_compression = 1 then 1 else 0 end) FROM sys.partitions  WHERE object_id = (CASE WHEN i.type = 4 THEN allobj.object_id ELSE i.object_id END) AND index_id = (CASE WHEN i.type = 4 THEN 1 ELSE i.index_id END)) > 0) then 1 else 0 end
       AS bit) AS [HasXmlCompressedPartitions]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
LEFT OUTER JOIN sys.spatial_index_tessellations as si ON i.object_id = si.object_id and i.index_id = si.index_id
LEFT OUTER JOIN sys.hash_indexes AS hi ON i.object_id = hi.object_id AND i.index_id = hi.index_id
LEFT OUTER JOIN sys.data_spaces AS dsi ON dsi.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.tables AS t ON t.object_id = i.object_id
LEFT OUTER JOIN sys.data_spaces AS dstbl ON dstbl.data_space_id = t.Filestream_data_space_id and (i.index_id < 2 or (i.type = 7 and i.index_id < 3))
LEFT OUTER JOIN sys.xml_indexes AS xi ON xi.object_id = i.object_id AND xi.index_id = i.index_id
LEFT OUTER JOIN sys.selective_xml_index_paths AS indexedpaths ON xi.object_id = indexedpaths.object_id AND xi.using_xml_index_id = indexedpaths.index_id AND xi.path_id = indexedpaths.path_id
LEFT OUTER JOIN sys.filetable_system_defined_objects AS filetableobj ON i.object_id = filetableobj.object_id
LEFT OUTER JOIN sys.key_constraints AS k ON k.parent_object_id = i.object_id AND k.unique_index_id = i.index_id
LEFT OUTER JOIN sys.stats AS s ON s.stats_id = i.index_id AND s.object_id = i.object_id
LEFT OUTER JOIN sys.xml_indexes AS xi2 ON xi2.object_id = xi.object_id AND xi2.index_id = xi.using_xml_index_id
LEFT OUTER JOIN sys.spatial_indexes AS spi ON i.object_id = spi.object_id and i.index_id = spi.index_id
LEFT OUTER JOIN sys.all_objects AS allobj ON 
        allobj.name = ''extended_index_'' + cast(i.object_id AS varchar) + ''_'' + cast(i.index_id AS varchar) AND allobj.type=''IT''
      
WHERE
(tbl.name=@_msparam_2 and SCHEMA_NAME(tbl.schema_id)=@_msparam_3)
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'DomainItem_temp',@_msparam_3=N's19'

请问如何在使用最新SMO版本的前提下，恢复接近旧版本的性能？

1. 精简冗余的脚本选项 #

当前启用的选项存在大量冗余：Indexes = true已经包含ClusteredIndexes和NonClusteredIndexes，DriAll也覆盖了DriIndexes、DriClustered、DriNonClustered。冗余选项会触发SMO执行额外的元数据查询，精简后可减少不必要的系统表访问：

var scripter = new Scripter(sourceDb.SmoServer)
{
    Options =
    {
        DriAll = true,
        Indexes = true,
        Triggers = true,
        Default = true,
        ScriptDataCompression = false,
        AllowSystemObjects = false,
        AgentJobId = false,
        ScriptXmlCompression = false
        // 移除所有重复的索引相关选项
    }
};

2. 预加载元数据后批量生成脚本 #

新版本SMO可能对逐个对象的元数据查询优化不足，可先预加载架构下所有对象的完整元数据，再一次性生成脚本：

// 预加载表和索引的默认字段，减少多次查询
sourceDb.SmoServer.SetDefaultInitFields(typeof(Table), true);
sourceDb.SmoServer.SetDefaultInitFields(typeof(Index), true);

// 确保所有对象已加载完整元数据
foreach (var obj in allSchemaObjects)
{
    obj.Load();
}

// 一次性生成整个架构的脚本
scripts.AddRange(scripter.EnumScript(allSchemaObjects));

3. 针对慢查询创建系统表索引 #

跟踪到的慢查询涉及多个系统表的关联，可创建以下索引加速查询（需在master数据库执行，且需高权限）：

-- 优化sys.tables按schema和名称的查询
CREATE NONCLUSTERED INDEX IX_tables_schema_name ON sys.tables(schema_id, name) INCLUDE(object_id);

-- 优化sys.indexes按object_id的查询
CREATE NONCLUSTERED INDEX IX_indexes_object_id ON sys.indexes(object_id) INCLUDE(index_id, type, name, is_hypothetical, fill_factor);

4. 禁用不需要的特性脚本 #

新版本SMO默认收集空间索引、XML索引、内存优化表等特性的元数据，若你的业务不需要这些脚本，可显式禁用：

var scripter = new Scripter(sourceDb.SmoServer)
{
    Options =
    {
        // 保留必要选项
        DriAll = true,
        Indexes = true,
        Triggers = true,
        Default = true,
        ScriptDataCompression = false,
        AllowSystemObjects = false,
        AgentJobId = false,
        ScriptXmlCompression = false,
        // 禁用不需要的特性
        SpatialIndexes = false,
        XmlIndexes = false,
        MemoryOptimizedObjects = false,
        FileTables = false
    }
};

内容的提问来源于Stack Exchange，提问作者Sturle Dahl