Insanely slow 'Refresh' functionality

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? 
Tagged:

Best Answer

  • Greg_SmulkoGreg_Smulko Posts: 41 Silver 1
    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 | Technical Architect for SQL Monitor | Redgate Software

Answers

  • 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.
    Greg Smulko | Technical Architect for SQL Monitor | Redgate Software
  • RomanPekarRomanPekar Posts: 19 Bronze 1
    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_SmulkoGreg_Smulko Posts: 41 Silver 1
    edited October 8, 2018 9:55AM
    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


    Greg Smulko | Technical Architect for SQL Monitor | Redgate Software
  • RomanPekarRomanPekar Posts: 19 Bronze 1
    edited October 8, 2018 11:31AM
    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>
  • samssb82samssb82 Posts: 12 Bronze 2
    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.
  • RomanPekarRomanPekar Posts: 19 Bronze 1
    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!
  • RomanPekarRomanPekar Posts: 19 Bronze 1
    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.
  • 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.
    Greg Smulko | Technical Architect for SQL Monitor | Redgate Software
  • And how do we create a support ticket?
  • RomanPekarRomanPekar Posts: 19 Bronze 1
    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.

  • duggergduggerg Posts: 5 New member
    edited November 20, 2018 9:34PM
    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>
  • Have you put the setting outside the comment block?
  • duggergduggerg Posts: 5 New member
    Almost all of them are outside the comment blocks in the code snippet. Only the data related comparisons are commented.
Sign In or Register to comment.