SQL Compare extremely slow registering target database
JamesMB
Posts: 4 New member
in SQL Compare
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
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
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.
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
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
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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
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
Mike