We've been using Visual Studio 2017's SQL Server Schema Comparison for all our (SQL Server 2016) migrations and deployments.
However, recently, it has become very slow, taking hours to process. If we uncheck the "Tables" object, it goes quickly. But when tables are checked, it is stuck on "Initializing comparison..." for ages.
I've not been able to find anything online that has helped us. Any ideas?
What seemed to work for us is that if you excluded tables (in Schema Compare Options --> Object types --> Application-scoped -- > Tables), it runs quickly.
After its runs initially, you can compare again with tables selected, and its fine.
With the exception of this, the Visual Studio database Schema Compare is an awesome tool.
You can use MSSQL Server Management Studio's own comparison or use one of these tools:
https://www.agile-code.com/blog/choose-your-sql-server-schema-comparison-tool/
I had very similar problem and I was able to find a solution.
Query starting with SELECT * FROM (SELECT SCHEMA_NAME([o].[schema_id]) AS [SchemaName], from [sys].[spatial_indexes] and other tables had suboptimal plan and was running for hours, causing VS to timeout and retry.
Plan guide included below solved the problem for me.
Because VS sends all queries in one batch during 1st attempt to get results plan guide will not kick in. After query times out, VS will retry this query in its own batch and plan guide will be applied. To speed up the process even more and don't wait for time out you can kill VS session when it is stuck on this query and force retry.
EXEC sp_create_plan_guide
@name = N'VS Schema Comp Spatial Idxs',
@stmt = N'SELECT * FROM (
SELECT
SCHEMA_NAME([o].[schema_id]) AS [SchemaName],
[si].[object_id] AS [ColumnSourceId],
[o].[name] AS [ColumnSourceName],
[o].[type] AS [ColumnSourceType],
[ic].[column_id] AS [ColumnId],
[c].[name] AS [ColumnName],
[si].[index_id] AS [IndexId],
[si].[name] AS [IndexName],
[ds].[type] AS [DataspaceType],
[ds].[data_space_id] AS [DataspaceId],
[ds].[name] AS [DataspaceName],
[si].[fill_factor] AS [FillFactor],
[si].[is_padded] AS [IsPadded],
[si].[is_disabled] AS [IsDisabled],
[si].[allow_page_locks] AS [DoAllowPageLocks],
[si].[allow_row_locks] AS [DoAllowRowLocks],
[sit].[cells_per_object] AS [CellsPerObject],
[sit].[bounding_box_xmin] AS [XMin],
[sit].[bounding_box_xmax] AS [XMax],
[sit].[bounding_box_ymin] AS [YMin],
[sit].[bounding_box_ymax] AS [YMax],
[sit].[level_1_grid] AS [Level1Grid],
[sit].[level_2_grid] AS [Level2Grid],
[sit].[level_3_grid] AS [Level3Grid],
[sit].[level_4_grid] AS [Level4Grid],
[sit].[tessellation_scheme] AS [TessellationScheme],
[s].[no_recompute] AS [NoRecomputeStatistics],
[p].[data_compression] AS [DataCompressionId],
CONVERT(bit, CASE WHEN [ti].[data_space_id] = [ds].[data_space_id] THEN 1 ELSE 0 END)
AS [EqualsParentDataSpace]
FROM
[sys].[spatial_indexes] AS [si] WITH (NOLOCK)
INNER JOIN [sys].[objects] AS [o] WITH (NOLOCK) ON [si].[object_id] = [o].[object_id]
INNER JOIN [sys].[spatial_index_tessellations] [sit] WITH (NOLOCK) ON [si].[object_id] = [sit].[object_id] AND [si].[index_id] = [sit].[index_id]
INNER JOIN [sys].[data_spaces] AS [ds] WITH (NOLOCK) ON [ds].[data_space_id] = [si].[data_space_id]
INNER JOIN [sys].[index_columns] AS [ic] WITH (NOLOCK) ON [si].[object_id] = [ic].[object_id] AND [si].[index_id] = [ic].[index_id]
INNER JOIN [sys].[columns] AS [c] WITH (NOLOCK) ON [si].[object_id] = [c].[object_id] AND [ic].[column_id] = [c].[column_id]
INNER JOIN [sys].[objects] AS [o2] WITH (NOLOCK) ON [o2].[parent_object_id] = [si].[object_id]
INNER JOIN [sys].[stats] AS [s] WITH (NOLOCK) ON [o2].[object_id] = [s].[object_id] AND [s].[name] = [si].[name]
INNER JOIN [sys].[partitions] AS [p] WITH (NOLOCK) ON [p].[object_id] = [o2].[object_id] AND [p].[partition_number] = 1
LEFT JOIN [sys].[indexes] AS [ti] WITH (NOLOCK) ON [o].[object_id] = [ti].[object_id]
LEFT JOIN [sys].[tables] AS [t] WITH (NOLOCK) ON [t].[object_id] = [si].[object_id]
WHERE [si].[is_hypothetical] = 0
AND [ti].[index_id] < 2
AND OBJECTPROPERTY([o].[object_id], N''IsSystemTable'') = 0
AND ([t].[is_filetable] = 0 OR [t].[is_filetable] IS NULL)
AND ([o].[is_ms_shipped] = 0 AND NOT EXISTS (SELECT *
FROM [sys].[extended_properties]
WHERE [major_id] = [o].[object_id]
AND [minor_id] = 0
AND [class] = 1
AND [name] = N''microsoft_database_tools_support''
))
) AS [_results];
',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (FORCE ORDER)';
I had the same problem and found a way around it.
Using a jump box with 64 bit virtual server with 16GB memory, CPU @2.4 GHz 2 processors with 3 cores each, and installing SQL Server 2022 Developer edition for the source database and installing Visual Studio 2022 on the same virtual server and moving the project on the same server and then running Schema compare in the same server has resolved the problem.
Even with over 1900 tables, 1600 procedure, 1600 view... it completes within 5 minutes the first time, 3 minutes second time and under 2 minutes there after.
I asked a colleague to move his project on the same jump box and run schema compare and he had the same successful completion rates.
I would think SQL Server Developer edition is what made the difference because I tried all the above before hand, except having the Schema Compare source database on a SQL Server Enterprise edition and it took 45 minutes to over 1 hour then. At that time I made sure my jump box was on the same data centre as my DB server and then monitored CPU, Memory, Disk and Network resources and did not find any bottle necks.
So I conclude that is SQL Server Developer edition on the same computer where the Visual Studio project is running that has made the difference.
© 2022 - 2025 — McMap. All rights reserved.