Insanely slow 'Refresh' functionality

RomanPekar

Recently we've finally moved from ReadyRoll to SQL Change Automation. Suddenly each 'Refresh' takes ages (~20-30 min) instead of seconds (~5-10sec) which makes the tool essentially unusable. There're no queries running in DB during this refresh and nothing particular in the logs.

It's pretty much the only change we've made so it's high chance that SQL Change Automation is the tool to blame. I hope it's not supposed to work like this (it definitely wasn't the case before update). What can we do in order to find the cause and fix this issue? 

Greg_Smulko

We were able to identify the root cause of this issue and we're working on a permanent fix.

For now, a workaround is to add the following line into a PropertyGroup in the .sqlproj:

<SyncOptionIgnoreConstraintNotForReplicationAttribute>False</SyncOptionIgnoreConstraintNotForReplicationAttribute>

Sorry for the inconvenience caused.

For the future reference, regarding creating support tickets, please start from the https://productsupport.red-gate.com/hc/en-us page , where you can submit a request.

Greg_Smulko

Hi Roman,

I'm really sorry that you're encountering such a degradation of performance.
Do you know which version of ReadyRoll you were using before?

Can you please change the logging setting in the logging.config files by setting it to the below value:

<level value="DEBUG" /> 

These files are at these locations:
C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\SqlChangeAutomation\logging.config
C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\Extensions\001rr241.rgs\logging.config (001rr241.rgs will be different in your case).

Please note that the folder locations might be different depending on the version of VS that you are using.

The log files should help to pinpoint the exact operation that takes most of the time. 
After you replicate the issue please change it from `DEBUG` to `FATAL`, to make sure that logging itself is not the culprit.

RomanPekar

Hi Greg,

Thanks for your tip.
The version of RR we were using before update is 1.14.

I've set level to DEBUG, and there's more information in the log now. But it still not clear what's the reason for the issue is, I just see a big gap (~10 minutes) between 2 lines in the log file.
Do you think there're something else we can do?

I've tried to check what happens with Process Monitor but cannot see anything specific (there're quite some activity from SQL LightHouse service and a lot of access to Temp\user\Red Gate\*.tmp files though)

Greg_Smulko

Hi Roman, could you paste these 2 lines from the log file between which a gap occurs?

It seems that you've found the logs yourself, for future reference they're stored in 

%localappdata%\Red Gate\Logs\SQL Change Automation

RomanPekar

It looks like this:

<div>080892|2018-10-08 13:08:12.370|DEBUG|RedGate.Versioning.Core.VersioningProject.DatabaseProject|83|Changed file: C:\Projects\Roman.Pekar\TestProject\Schema-Model\data\Tables\TEST.sql
<span style="background-color: transparent; color: inherit; font-size: inherit; font-family: roboto, "helvetica neue", Arial, sans-serif;">080892|2018-10-08 13:17:14.864|DEBUG|RedGate.Versioning.Client.VsShell.VisualStudio.ShellIntegration.VisualStudioProjectFileUpdater|70|The following files have been added:
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: roboto, "helvetica neue", Arial, sans-serif;">080892|2018-10-08 13:17:14.953|DEBUG|RedGate.Versioning.Client.VsShell.VisualStudio.ShellIntegration.VisualStudioProjectFileUpdater|70|Number of assets to add to project: 0</span></div>

samssb82

I have seen this behavior, but in our scenario it is when there is a change to a large stored procedure or view.  I believe it is attempting to identify the differences.

RomanPekar

samssb82 said:

I have seen this behavior, but in our scenario it is when there is a change to a large stored procedure or view.  I believe it is attempting to identify the differences.

Hm I think it can be interesting to check if 'refresh' works better if there's no changes at all, thanks!

RomanPekar

Greg_Smulko said:

Hi Roman, could you paste these 2 lines from the log file between which a gap occurs?

Greg, any new insights of the problem? Please let me know if we can expect some changes/advises or if we have to start looking into some workarounds.

Greg_Smulko

Hi Roman, I'm just releasing a new version of the product with extended logging, the version number is 3.0.18283.
Could you please install this version and provide the logs again?
Sorry for the inconvenience caused.
Actually, if you could file a support ticket, we could provide you with better assistance.

Oleksii_Shabrov

And how do we create a support ticket?

RomanPekar

Greg_Smulko said:

We were able to identify the root cause of this issue and we're working on a permanent fix.

For now, a workaround is to add the following line into a PropertyGroup in the .sqlproj:

<SyncOptionIgnoreConstraintNotForReplicationAttribute>False</SyncOptionIgnoreConstraintNotForReplicationAttribute>

Sorry for the inconvenience caused.

For the future reference, regarding creating support tickets, please start from the https://productsupport.red-gate.com/hc/en-us page , where you can submit a request.

Hi Greg,

Thanks, that was it! Seems to work fine now.
I was going to create this request but I haven't seen anything meaningful in the logs of the new version so I was going to conduct proper test before I send it to you.

duggerg

This has not helped us by setting it to true or false. I am also seeing it create Security folders and files each time I try a new baseline even though I have set every option that deals with users, permissions, roles, etc to be ignored. What am I missing?

<div><PropertyGroup></div><div>&nbsp; &nbsp; <!-- These properties might be set by the Developer --></div><div>&nbsp; &nbsp; <SyncOptionIncludeDependentObjects>False</SyncOptionIncludeDependentObjects></div><div>&nbsp; &nbsp; <SyncOptionIncludeExistenceChecks>False</SyncOptionIncludeExistenceChecks></div><div>&nbsp; &nbsp; <SyncOptionUseDropCreateInsteadOfAlter>False</SyncOptionUseDropCreateInsteadOfAlter></div><div>&nbsp; &nbsp; <SyncOptionDisableDdlTriggers>False</SyncOptionDisableDdlTriggers></div><div>&nbsp; &nbsp; <SyncOptionCaseSensitiveObjectDefinition>False</SyncOptionCaseSensitiveObjectDefinition></div><div>&nbsp; &nbsp; <SyncOptionIgnoreExtendedProperties>False</SyncOptionIgnoreExtendedProperties></div><div>&nbsp; &nbsp; <SyncOptionIgnoreCommentsInBodies>False</SyncOptionIgnoreCommentsInBodies></div><div>&nbsp; &nbsp; <SyncOptionIgnoreAnsiOptions>False</SyncOptionIgnoreAnsiOptions></div><div>&nbsp; &nbsp; <SyncOptionIgnoreCollations>True</SyncOptionIgnoreCollations></div><div>&nbsp; &nbsp; <SyncOptionIgnoreColumnsOrder>True</SyncOptionIgnoreColumnsOrder></div><div>&nbsp; &nbsp; <SyncOptionIgnoreWhiteSpace>True</SyncOptionIgnoreWhiteSpace></div><div>&nbsp; &nbsp; <SyncOptionIgnoreSynonymDatabaseAndServerName>True</SyncOptionIgnoreSynonymDatabaseAndServerName></div><div>&nbsp; &nbsp; <SyncOptionIgnoretSQLt>True</SyncOptionIgnoretSQLt></div><div>&nbsp; &nbsp; <SyncOptionIgnoreDmlTriggers>False</SyncOptionIgnoreDmlTriggers></div><div>&nbsp; &nbsp; <SyncOptionIgnoreInsteadOfTriggers>False</SyncOptionIgnoreInsteadOfTriggers></div><div>&nbsp; &nbsp; <SyncOptionIgnoreBindings>False</SyncOptionIgnoreBindings></div><div>&nbsp; &nbsp; <SyncOptionIgnoreQueueEventNotifications>False</SyncOptionIgnoreQueueEventNotifications></div><div>&nbsp; &nbsp; <SyncOptionIgnoreSquareBrackets>False</SyncOptionIgnoreSquareBrackets></div><div>&nbsp; &nbsp; <SyncOptionIgnoreWithElementOrder>True</SyncOptionIgnoreWithElementOrder></div><div>&nbsp; &nbsp; <SyncOptionIgnoreWithEncryption>False</SyncOptionIgnoreWithEncryption></div><div>&nbsp; &nbsp; <!-- These properties might be set by the DBA --></div><div>&nbsp; &nbsp; <SyncOptionIgnoreDataspaces>True</SyncOptionIgnoreDataspaces></div><div>&nbsp; &nbsp; <SyncOptionIgnoreConstraintNotForReplicationAttribute>True</SyncOptionIgnoreConstraintNotForReplicationAttribute></div><div>&nbsp; &nbsp; <SyncOptionIgnoreFillFactor>False</SyncOptionIgnoreFillFactor></div><div>&nbsp; &nbsp; <SyncOptionIgnoreIndexLockAttributes>False</SyncOptionIgnoreIndexLockAttributes></div><div>&nbsp; &nbsp; <SyncOptionIgnoreStatistics>True</SyncOptionIgnoreStatistics></div><div>&nbsp; &nbsp; <SyncOptionIgnoreDataCompression>True</SyncOptionIgnoreDataCompression></div><div>&nbsp; &nbsp; <SyncOptionIgnoreFullTextIndexing>False</SyncOptionIgnoreFullTextIndexing></div><div>&nbsp; &nbsp; <SyncOptionIgnoreReplicationTriggers>True</SyncOptionIgnoreReplicationTriggers></div><div>&nbsp; &nbsp; <SyncOptionIgnoreSchemaObjectAuthorization>False</SyncOptionIgnoreSchemaObjectAuthorization></div><div>&nbsp; &nbsp; <SyncOptionIgnoreStatisticsNorecompute>False</SyncOptionIgnoreStatisticsNorecompute></div><div>&nbsp; &nbsp; <SyncOptionConsiderNextFilegroupInPartitionSchemes>False</SyncOptionConsiderNextFilegroupInPartitionSchemes></div><div>&nbsp; &nbsp; <!-- Shared Developer/DBA options --></div><div>&nbsp; &nbsp; <SyncOptionIgnoreIndexes>False</SyncOptionIgnoreIndexes></div><div>&nbsp; &nbsp; <SyncOptionIgnorePermissions>True</SyncOptionIgnorePermissions></div><div>&nbsp; &nbsp; <SyncOptionIgnoreConstraintWithNoCheckAttribute>False</SyncOptionIgnoreConstraintWithNoCheckAttribute></div><div>&nbsp; &nbsp; <SyncOptionIgnoreNocheckAndWithNocheckAttributes>False</SyncOptionIgnoreNocheckAndWithNocheckAttributes></div><div>&nbsp; &nbsp; <SyncOptionIgnoreIdentitySeedAndIncrement>True</SyncOptionIgnoreIdentitySeedAndIncrement></div><div>&nbsp; &nbsp; <SyncOptionIgnoreUserProperties>True</SyncOptionIgnoreUserProperties></div><div>&nbsp; &nbsp; <SyncOptionIgnoreUsersPermissionsAndRoleMemberships>True</SyncOptionIgnoreUsersPermissionsAndRoleMemberships></div><div>&nbsp; &nbsp; <SyncOptionAddWithEncryption>False</SyncOptionAddWithEncryption></div><div>&nbsp; &nbsp; <SyncOptionDecryptEncryptedObjects>False</SyncOptionDecryptEncryptedObjects></div><div>&nbsp; &nbsp; <SyncOptionUseCompatibilityLevel>False</SyncOptionUseCompatibilityLevel></div><div>&nbsp; &nbsp; <!-- Data comparison/sync options --></div><div>&nbsp; &nbsp; <!--</div><div>&nbsp; &nbsp; <SyncDataOptionDropKeysIndexesAndConstraints>False</SyncDataOptionDropKeysIndexesAndConstraints></div><div>&nbsp; &nbsp; <SyncDataOptionDisableForeignKeys>False</SyncDataOptionDisableForeignKeys></div><div>&nbsp; &nbsp; <SyncDataOptionIncludeTimestampColumns>False</SyncDataOptionIncludeTimestampColumns></div><div>&nbsp; &nbsp; <SyncDataOptionCaseSensitiveObjectDefinition>True</SyncDataOptionCaseSensitiveObjectDefinition></div><div>&nbsp; &nbsp; <SyncDataOptionForceBinaryCollation>True</SyncDataOptionForceBinaryCollation></div><div>&nbsp; &nbsp; <SyncDataOptionTrimTrailingSpaces>False</SyncDataOptionTrimTrailingSpaces></div><div>&nbsp; &nbsp; <SyncDataOptionDisableDmlTriggers>False</SyncDataOptionDisableDmlTriggers></div><div>&nbsp; &nbsp; <SyncDataOptionDisableDdlTriggers>True</SyncDataOptionDisableDdlTriggers></div><div>&nbsp; &nbsp; <SyncDataOptionDontIncludeCommentsInScript>False</SyncDataOptionDontIncludeCommentsInScript></div><div>&nbsp; &nbsp; <SyncDataOptionReseedIdentityColumns>False</SyncDataOptionReseedIdentityColumns></div><div>&nbsp; &nbsp; <SyncDataOptionSkipIntegrityChecksForForeignKeys>False</SyncDataOptionSkipIntegrityChecksForForeignKeys></div><div>&nbsp; &nbsp; <SyncDataOptionTransportClrDataTypesAsBinary>False</SyncDataOptionTransportClrDataTypesAsBinary></div><div>&nbsp; &nbsp; <SyncDataOptionCompressTemporaryFiles>False</SyncDataOptionCompressTemporaryFiles></div><div>&nbsp; &nbsp; <SyncDataOptionTreatEmptyStringAsNull>False</SyncDataOptionTreatEmptyStringAsNull></div><div>&nbsp; &nbsp; <SyncDataOptionUseChecksumComparison>False</SyncDataOptionUseChecksumComparison></div><div>&nbsp; &nbsp; <SyncDataOptionUseMaxPrecisionForFloatComparison>False</SyncDataOptionUseMaxPrecisionForFloatComparison></div><div>&nbsp; &nbsp; --></div><div>&nbsp; </PropertyGroup></div>

Oleksii_Shabrov

Have you put the setting outside the comment block?

duggerg

Almost all of them are outside the comment blocks in the code snippet. Only the data related comparisons are commented.