RedGate SQL Data Compare – Will More Indexes Speed Up?

indexmigrationperformance

We want to use RedGate's SQL Data Compare to migrate user data from one DB to a DB on a completely different provider. This will be a time-sensitive operation as we do not want our site offline for too long and there will be a lot of data that needs to be moved. We do not want any I/O on the table during this time.

Has anyone using RedGate found that adding additional indexes on some of our comparison criteria columns (i.e. DateCreated, DateModified) will affect the time to perform this? Our initial testing is at about 25-30 min for comparison of the source -> target DBs at the moment.

Best Answer

The question suggests that the tool is being used to migrate data and not schema, which implies that you are referring to SQL Data Compare and not SQL Compare.

In which case, yes, indexes on the comparison key columns will speed on SQL Data Compare.

I've consulted the SQL Data Compare project manager, who provides the following insight: "Usually performance problems with SDC have more to do with the network connection speed to the server and/or the disk speed on the location of the temp files, neither of which are affected by having an index on the comparison key"

SQL Data Compare performance tips are part of the SQL Data Compare documentation.

Related Solutions

Sql-server – Setting up a Redundant Sql Server 2008 Configuration While Retaining High Performance and Minimal Downtime

I would go with mirroring with a witness, do you want to do the work of a manual fail-over at 3AM when "socks hit the fan"? Check out Paul Randal's MCM prep videos, they might help you out in your decision.

Sql-server – Compare Indexes/Constraints between two SQL Server databases

DECLARE @Sourcedb sysname 
DECLARE @Destdb sysname 
DECLARE @SQL varchar(max) 

SELECT @Sourcedb = '<<Database1>>' 
SELECT @Destdb = '<<Database2>>' 

SELECT @SQL = ' SELECT ISNULL(SoSource.name,SoDestination.name) ''Object Name'' 
                     , CASE  
                       WHEN SoSource.object_id IS NULL      THEN SoDestination.type_desc +  '' missing in the source -- '  
                                                                                         + @Sourcedb + ''' COLLATE database_default 
                       WHEN SoDestination.object_id IS NULL THEN SoSource.type_desc      +  '' missing in the Destination -- ' + @Destdb  
                                                                                         + ''' COLLATE database_default 
                       ELSE SoDestination.type_desc + '' available in both Source and Destination'' COLLATE database_default 
                       END ''Status'' 
                 FROM (SELECT * FROM ' + @Sourcedb + '.SYS.objects  
                        WHERE Type_desc not in (''INTERNAL_TABLE'',''SYSTEM_TABLE'',''SERVICE_QUEUE'')) SoSource  
      FULL OUTER JOIN (SELECT * FROM ' + @Destdb + '.SYS.objects  
                        WHERE Type_desc not in (''INTERNAL_TABLE'',''SYSTEM_TABLE'',''SERVICE_QUEUE'')) SoDestination 
                   ON SoSource.name = SoDestination.name COLLATE database_default 
                  AND SoSource.type = SoDestination.type COLLATE database_default 
                  ORDER BY isnull(SoSource.type,SoDestination.type)' 

EXEC (@Sql)

Best Answer

Related Solutions

Sql-server – Setting up a Redundant Sql Server 2008 Configuration While Retaining High Performance and Minimal Downtime

Sql-server – Compare Indexes/Constraints between two SQL Server databases

Related Question