Analyzing database schema and build the SQL DB project is taking more time approximately 1 hour

Question

Analyzing database schema and build the SQL DB project is taking more time approximately 1 hour. We found few alternatives to suppress this issue from the internet but there are consequences if make those changes, please check the following issue and suggest us, can we follow the below suggestions are not.

1. Analyzing database schema - If we disable the Auto create statistics and Auto update statistics in DB project setting, but if we disable there are consequences for query performance against your database.
2. Build issue - If we disable Validate Casing on identifies in DB project setting, but there is a consequences of not validating the type checks.

Target platform we are using is SQL Server 2016 Visual studio : Microsoft Visual Studio Professional 2019.

Answer 1

Hi @19616165,

https://support.microsoft.com/en-us/topic/kb3177312-sql-server-2016-build-versions-d6cd8e5f-4aa3-20ac-f38f-8faef950840f

In case you miss some important fixes, it is recommended that you install the latest SP(SP3) with the latest GDR.

It seems that we are seeing posts from the same side of the discussion. To give other members ideas, I'll put the link here.

https://stackoverflow.com/questions/9734019/analyzing-database-schema-takes-hours

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

I recommend you first update stats on system tables before the more heavy-handed suggestions in your question. This can improve performance of thee catalog view queries executed during schema comparison and publish actions. Below are scripts. one for SQL Server 2016 and earlier and another for later versions.

--system table stats update for SQL 2016 and earlier (uses XML PATH)
DECLARE @SQL nvarchar(MAX) =
(
     SELECT 
       N'UPDATE STATISTICS ' 
     + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) 
     + N'.' 
     + QUOTENAME(OBJECT_NAME(i.object_id)) 
     + N';'
FROM sys.indexes AS I
JOIN sys.partitions AS p ON
     p.object_id = i.object_id
AND p.index_id = i.index_id
WHERE
     OBJECTPROPERTYEX(i.object_id, 'IsSystemTable') = 1
     AND i.index_id > 0
     AND p.rows > 0
FOR XML PATH(''), TYPE).value('.','nvarchar(MAX)'
);
EXEC sp_executesql @SQL;
GO


--system table stats update for SQL 2017 and Azure SQL Database (uses STRING_AGG)
DECLARE @SQL nvarchar(MAX) =
(
     SELECT
         STRING_AGG(
               N'UPDATE STATISTICS ' 
             + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id))
             + N'.'
             + QUOTENAME(OBJECT_NAME(i.object_id))
         ,';')
FROM sys.indexes AS I
JOIN sys.partitions AS p ON
     p.object_id = i.object_id
     AND p.index_id = i.index_id
WHERE 
     OBJECTPROPERTYEX(i.object_id, 'IsSystemTable') = 1
     AND i.index_id > 0
     AND p.rows > 0
);
EXEC sp_executesql @SQL;
GO