SQL Compare extremely slow registering target database

JamesMBJamesMB Posts: 4 New member
Hi,

I've been using SQL Compare for years with no noticeable performance issues but have recently moved to a new company and am trying to get their databases set up to use the SQL Toolbelt. There is one datawarehouse database which is causing me issues that I've never seen before. The comparison seems to take an unacceptably long time and almost all the time appears to be on the 'Registering target database step' when 'Reading indexes'. It's very quick up to this point and almost instant once it's past that point, but this seems to take about 10 minutes. The source database I'm using is an empty local instance and the target database is a backup of a production database on a remote server. 

Any help with tracking down, and ideally fixing, the cause of this would be gratefully received. I've tried ignoring indexes but that has made no difference at all and a colleague has suggested that it may be related to partitioning but I'm afraid that I'm at an impasse.

Thanks

James
Tagged:

Answers

  • Hi,

    Could I ask for a little more information please? How many tables & indexes are we talking about for this warehouse? How many partitions on those indexes? I'm not sure this has any bearing, but I'm trying to get a sense of the scale of the problem.
  • JamesMBJamesMB Posts: 4 New member
    Hi Grant,

    I've only just seen the database for the first time yesterday, so I'm not really that familiar with it myself, I just know that I've never seen SQL compare behave in this way before. I've been told that there's an awful partitioning scheme with thousands of partitions and the colleague that told me this seemed to think that was most likely the issue. 

    There are just over 400 tables and I know that there are also a number of indexed views. Selecting from sys.indexes gives me a count of just under 1100 indexes.

    Hope that helps

    James
  • JerryMAJerryMA Posts: 2 New member
    edited April 23, 2019 3:44PM
    Hi Grant,

    I agree 100% with James that the performance when comparing has degraded. I've been using SQL Compare consistently over the past 2.5 years on a weekly, sometimes daily, basis and. In my case it just appears to hang indefinitely on some comparisons. Please note that the underlying databases being compared are the same I've been comparing over past 2.5 years and schema changes only slightly over time.

    Also note that in past whenever I've seen it hang it was due to blocking due to open transaction or pending DDL change. However in these cases I see no blocking. In fact, after running sp_who2 the process is just waiting and there is zero activity on the server at the time.

    The largest of these databases is only ~400 tables, ~400 indexes, ~200 views, ~125 procs, 1 CLR function, 0 triggers, 1 partition function, ~15 synonyms. Data size is ~600GB.

    These are SQL Server 2012 SP4 boxes.

    Thanks in advance for looking into this.
    Jerry

  • Thanks for the feedback Jerry. That's not a very big database and you shouldn't be seeing poor performance on it. I'll bring this up with the devs.
  • Eddie DEddie D Posts: 1,699 Rose Gold 5
    edited April 24, 2019 3:44PM
    Hi James

    A ticket in our call logging system has been created for you.  I will send a private message to you with the ticket details.

    Would you please increase the minimum logging level to verbose and then repeat the comparison.  This action will generate a verbose log file which hopefully will provide to us information where in the comparison is performing badly. 

    This help document explains how to increase the logging level and how to locate the log file generated.  Please update the support ticket with a copy of the log file for us to review.

    Many thanks
    Eddie 
    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • JerryMAJerryMA Posts: 2 New member
    Eddie,

    Thanks for quickly getting back on this issue. So I went to turn on verbose logging and realized the option was not there since I was still running version 11.5. I overlooked fact that after upgrading the Tool Belt both instances remained side-by-side.

    I have since ran the same database compares using the 13.7 version and I'm no longer experiencing the indefinite hanging. Now I am seeing the expected hanging only when database activity is blocking the Redgate process.

    I will continue testing and if I encounter issues will update this ticket.

    Thanks again for your help and I hope you can assist James successfully as well.

    Jerry
  • jescoinjescoin Posts: 1 New member
    edited July 23, 2019 10:00AM
    Hello, something similar happens to me. I have two databases and one of them is very slow (in previous versions it didn't happen to me). I attached log (the slow database is SCP-T-Des in DESSQL16 Server)
  • MichaelMiMichaelMi Posts: 1 New member
    I also have just recently within the last two weeks started having a similar issue.  I have attached my verbose log file in the hopes it helps identify the issue.  Thank you.

    Mike
Sign In or Register to comment.